Show SQL - Constructing the query (select expert)

[Lochbel]

This is bit of newbie question but i need advice.
Using CR9 on a SQL Server database.

When i build a query using the Select Expert, when i view the select statement or the WHERE clause (under View SQL) it is often in a different sequence to that which i build.

For example i may have a select on field 5, field 8 and field 11, but the SQL will present as fields 8,11 and 5 (say).

This does not happen every time, but could someone explain why this occurs and whether i should be resequencing... My orginal select is built with the quickest response in mind, in so far the first field is an indexed field with the narrowest range.

Thanks and regards




Lochbel

 

[synapsevampire]

As for performance, the select line isn't important, the fields that you join on or perform filtering on is more important with respect to indexes.

As for altering the select statement, you have little control over that, and shouldn't worry about it.

-k

 

[Lochbel]

I am refering to the filtering WHERE clause that is resequenced. I though the sequence of fields in the WHERE clause would have some bearing on performance...namely using an indexed field with less records (of the database) first and then a field that would select more records (if used in isolation) etc...

My point is how to get the right subset of records in the most effective way... does the sequence of index fields in the WHERE clause matter?

If this is how you interpreted my original post, apologies for seeming pedantic.

cheers


Lochbel

 

[vidru]

If you're really concerened about performance, then use a stored procedure. Unless you're dealing with a big chunk of data, I wouldn't be too concerned about the order of the WHERE clause Crystal generates.

-dave

 

[synapsevampire]

My apologies, you're correct, the sequence of a Where clause can be very significant.

Unfortunately it's often times difficult enough just getting the SQL to pass to the database (I have a FAQ on this), and I haven't played with trying to optimize the Where clause as I tend to base reports on Views or SP's (SP's when performance counts).

-k

 

[Lochbel]

Thanks to you both... I have found this resequencing seems more evident when using OLE. ODBC seems to preserve the original sequence a little better.

Might be an artifact of the drivers.

cheers


Lochbel