CODEWITHSUNDEEP
sqlsql-server
j.strugnell
j.strugnell

Reputation: 407

SQL Server Where Clauses

When running a select query (on SQL SERVER 2000+) with multiple where statements such as

SELECT    * 
FROM      TABLE1
WHERE     TableId = @TableId 
          AND Column1 = @Column1 
          AND Column2 = @Column2 
          AND Column3 = @Column3 etc.

...does the query calculate each and every one of the where clauses even if the first one does not equate? I have a query which checks each of the columns to see if any changes have been made, before updating (i.e. it only updates if there are changes). If the query compares every column then I think I will need to rewrite the query. However, if the query engine first filters to the ID column and then compares each column individually within that filtered set the performance should be fine (I would have thought?).

I hope that makes sense.

Thanks, James.

Upvotes: 0

Views: 186

Answers (4)

TerrorAustralis
TerrorAustralis

Reputation: 2923

I dont know if this will be possible in your case but try this:

On the table/data set you are looking at, create a LastModified datetime default getdate() column. Then create a trigger for Update to that table. Make the trigger do the following.

On update set LastModified = getdate()

This is good database design, and you can index the LastModified column. This is a narrower index to maintain and your query can now be

UPDATE... WHERE LastModifed >= @LastRunDate

For help on triggers, try this Stack overflow thread: Trigger Firing

Upvotes: 0

KM.
KM.

Reputation: 103697

why hit the data twice with something like:

--did data change?
if exists (select ... where pk=@pk and and and...)
begin
    update ... where pk=@pk
end

just do something like:

update ... where pk=@pk and and and...

you can check @@ROWCOUNT if you need to know if it actually changed and was UPDATEd

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300759

Based on statistics and available indexes, the optimiser will estimate which is the best order to compute the query. Most of the time it gets it right, but can be thrown off by out of date statistics.

You should not be concerned with the order of WHERE clauses. Rather, have a look at the actual execution plan created for the query.

Upvotes: 3

Mongus Pong
Mongus Pong

Reputation: 11477

No.

The query engine is pretty well optimised.

Upvotes: 2

Related Questions