• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Optimizing Queries by Looking @ SQL Plans

 
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 148
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
This parrot is no more. It has ceased to be. Now it's a tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic