Having problems with queries? Are they running slow and you don't know why?
Chances are they are either not using indexes or you don't have the correct indexes created for the query.
In both of these cases the command
SET SHOWPLAN ON
is your greatest ally!
I also like to run
SET NOEXEC ON
so that the server doesn’t execute the query. Usefull when you want to benchmark UPDATE or DELETE commands and not accidentally change any data!
(Remember to run
SET NOEXEC ON last because if you run it first the
SET SHOWPLAN ON statement will, of course, not be run!)
As an example:
Code:
1> SET SHOWPLAN ON
2> SET NOEXEC ON
3> GO
1> SELECT *
2> FROM post,
3> users
4> WHERE post.userid = users.userid
5> GO
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE
users
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
post
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
As you can see from the output we have a table scan on BOTH tables! YIKES! This will cause some problems as your tables start to fill up with information.
To fix this problem, create an index on users.userid like this:
Code:
1> CREATE INDEX userid ON users( userid )
2> GO
1> SELECT *
2> FROM post,
3> users
4> WHERE post.userid = users.userid
5> GO
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE
post
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
users
Nested iteration.
Index : userid
Forward scan.
Positioning by key.
Keys are:
userid ASC
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
As you can see, the users table is now using the index your created. The reason why post is a table scan is because you are selecting all rows so an index won’t help you at all. A more complex WHERE clause which uses more columns from post would require an index to avoid the table scan.
To turn off NOEXEC And SHOWPLAN simply reverse the first command:
Code:
1> SET NOEXEC OFF
2> SET SHOWPLAN OFF
3> GO
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SET OPTION OFF.
QUERY PLAN FOR STATEMENT 2 (at line 2).
STEP 1
The type of query is SET OPTION OFF.
1>
dBforums
>
Useful Sybase ASE Topics
06-02-01, 21:42
Useful Sybase ASE Topics
)
ASE-ASE Replication database using Sybase Central
Linear Mode
