How to check your Query performance?

Once you have written a query, you will need to know if its the most optimised query or not. One place you would want to check is the Query plan. Now what is this query plan?
Its the steps the ASE follows to retrive data from the tables.

What does it give you?
1. In case of a query involving multiple tables, it gives you the order in which the
tables are taken up for data retrival.
2. It will give you information if the ASE is using the indexes or not.
3. It will tell you if any temporary tables are created while executing the query.
4. It will also tell you how the aggregate cluses are being processed.

How do you see the query plane in Sybase?Use the following:

1> set showplan on
2> go
1> set noexec on
2> go

'showplan' puts the query plan on and 'noexec' ensures that the query doesn't get executed if you run it. When you run the query, you should see something like:

1> select title from titles where title > "Cooking"
2> go

STEP 1
The type of query is SELECT
FROM TABLE
titles
Nested iteration
Index: titlein

Things to look out for:
1. If there is a something like "Tablescan", it means the ASE is scanning the entire
contents fo the table to retrieve results. Its dangerous if you have huge table. If its
inevitable make sure ASE does it onlyfor samll tables.
2. Temporary tables: If you see ASE is creating too many temporary tables, its bound to
take a lot of time and reflets bad querying technique. Temporary table are inevitable
for ordery by/ aggregate clauses but proper querying might mitigate them if not avoid
them alltogether.