Sybase Optimization Question

[Guest_imported]

While a result set should not be different regardless of the WHERE clause ordering, the question of proper ordering came up in optimization. Assume a FROM clause as follows where data is retrieved from all tables:
FROM (tables)A, B, C, D
Should the ordering of the WHERE clause follow logically (which is easier to read). In otherwords:
Where A.1 = B.1
and A.2 = B.2
and C.2 = B.2
and C.3 = B.3
and D.4 = C.4

or does skipping around matter for optimization purposes.
For example:
Where D.4 = C.4
and C.2 = B.2
and A.1 = B.1
and C.3 = B.3
and A.2 = B.2

Thanks in advance

Nicatt

 

[Tim1]

Hi Nicatt,

Strictly speaking, the order in which you place things in the where clause shouldn't affect the performance, however, Sybase is a wierd and wonderful thing....

Depending on the version that you are running, you may notice that it does some odd things to the performance of your query, mainly because of the index selection. In older versions of Sybase (especially v.10 and early v.11) it may change the index picked depending on the order of the where clause, even down to which side of the "=" sign you have a table.

Basically, have a play to get the best performance, but don't be surprised if things change occasionally.

Tim.

 

[qyllr]

hello,

i've wondered about your question also...a way to possibly test it...is with dbcc traceon 302, 310... and compare the results with the different outputs...for the syntax... consult with the sybase manual...

cheers,
q.