betsywr
asked on
Order of WHERE statements in Sybase SQL Server
I'm working on tuning some SQL in a Sybase DB and have found a number of queries which don't seem to be utilizing the indexes efficiently on the DB tables. It seems to me that in a join statement, the most logical order for the WHERE statments would be an order which first checks fields that are indexed on each table. For example:
SELECT a.column1,
b.column2
FROM table1 a,
table2 b
WHERE a.indexed_column1 = :hstvariable1
AND a.indexed_column2 = :hstvariable2
AND b.indexed_column1 = a.indexed_column1
AND b.unindexed_column1 = :hstvariable3
(I hope that this is making sense.) In other words, can reordering the WHERE conditions help performance by eliminating some reads on the tables?
SELECT a.column1,
b.column2
FROM table1 a,
table2 b
WHERE a.indexed_column1 = :hstvariable1
AND a.indexed_column2 = :hstvariable2
AND b.indexed_column1 = a.indexed_column1
AND b.unindexed_column1 = :hstvariable3
(I hope that this is making sense.) In other words, can reordering the WHERE conditions help performance by eliminating some reads on the tables?
In a simple query like this, I would think that the query optimiser would already be taking this information into account.
When it comes to designing a RDBMS engine, the SQL parser/optimiser is given a lot of attention. If you think about it, one of the primary functions of the engine is to process SQL.
When it comes to designing a RDBMS engine, the SQL parser/optimiser is given a lot of attention. If you think about it, one of the primary functions of the engine is to process SQL.
Reording can optimise your query.
The best result you get when using sub-queries.
The best result you get when using sub-queries.
ASKER
How can I find out the order that the optimiser is using?
ASKER
How can I find out the order that the optimiser is using?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER