So Im trying to optimize this Sybase SQL query to perform faster. I did a show plan and looked for all "Table Scans" in the Plan. One by one I eliminated them all by providing index hints in the SQL itself ((index xyz_idx1).
This eliminated all the Table Scans but the perfoemance is the same. Now all of them are forward scans.
So whats the difference between a forwad scan and a table scan ? All I knew was that Table Scans were evil. I dont really know how to interpret the elements of a SQL plan. Is there any online resource or book available that can explaion the various elements of a SQL plan ?
And finally, if a forward scan is bad performace-wise, then how do I really fix it?... im already forcing it to use the available indexes.
A table scan is just what it sounds like. The query has to scan through every record in the table to find ones matching your criteria. An index keeps track of the records that match certain criteria. There are guidelines for creating indexes that you can find in your Sybase docs. Basically, it will tell you what types of indexes are useless because some types of data don't lend themselves very well to being indexed (dates and strings). For best results you want to index columns that have a few unique values.
A forward scan indicates that the query is using an index. It is slightly better than a reverse scan. A forward scan means the scan is in the order specified at the time the index was created.
I've had success using the SQL Cookbook from O'Reilly when optimizing SQL.
subject: Optimizing Queries by Looking @ SQL Plans