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:

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:

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:

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>

To check what exactly is executed at the server level when frontend user kicks of a report or any application module, use:

dbcc traceon(11201,11202,11203,11204,11205,11206)

It produces huge output in errorlog. Make sure to turn it off when the job is done.

Thanks a lot..It is very useful to sybase developers/DBA's

what aspects are used in AS/400 from sybase

How to identify which process is slow

Hi,
can anyone help me out in finding a purticular table size in a sybase db. and i'm using sqlanywhere10-interactive sql tool.
sp_spaceused is nomore supported. any alternative or any scripts that i can have to finf the table size?

hello frens..

i've problems to replicate database in 2 machines that already have sybase ASE 12.5.2. I try to configure ASE Replicator using this document
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/rl125ug.
but the following error appear when i run the aserep command.
ASE Replicator requesting Java Virtual Machine exit with message: ASE Replicator
initialization error in component <DDBAdmin>. Component message: Error encounte
red checking environement: S0022: Invalid column name 'network_name'.

ASE Replicator initialization error in component <DDBAdmin>. Component message:
Error encountered checking environement: S0022: Invalid column name 'network_nam
e'.
I didn't find any document on this topic that use GUI Sybase Central to configure replication.Can anyone help me on this topic?TQ:confused:

really very useful

thx alot for your great help

Hi,
l'm wondering if someone can help me, l'm a sqlserver person and just started dabbling into sybase, l decided to start with version 12.5 and purchased jeffery garbus book, it's dosen't explain much on the process of installation and l'm finding it increasing difficult to uninstall sybase ase 12.5 as it's not showing in add/remove programs and the uninstall option in the programs folder is giving me a "unable to launch application".Also, l can't see ase service in the service applet, l've spent the whole day on this and am not too happy, please help, no documentation on uninstalling ase 12.5 on windows 2000 server.