September 16, 2004 at 10:22 am
A parent table has a set of related child tables. Some or all of the child tables may contain data. There is an indicator in a bit column in the parent table (one for each child table) that denotes whether there are any child record in each child table.
The client side logic dictates that all related child tables must be re-queried each time the parent record changes. The real question is as follows :-
Will SQL server use less overhead (take less time) if the WHERE clause of a query on a known "empty" child table is something like "WHERE 1 = 2" as opposed to the regular WHERE clause that compares a column value to lookup value that will not be found?
In otherwords, when using the regular WHERE clause, SQL server would have to look through the table to see whether any records match the key value. If we know there are none to be found, will a "WHERE 1 = 2" which is inherently FALSE immediately execute and therefore simply return an empty result set without having to rifle through the table?
Any thoughts on this would be much appreciated.
September 16, 2004 at 11:23 am
Depends. The where clause you provided would be much faster, but that would require dynamic sql. If one of the two constants is a variable (which I expect is what you meant and were simply using shorthand) then it will still perform an index SCAN which means it will walk the entire child index. The difference can be seen by looking at the execution plan in Query Analyzer with the following SQL.
declare @test-2 int
set @test-2 = 0
SELECT * FROM syscolumns WHERE @test-2 = 1
SELECT * FROM syscolumns WHERE 0 = 1
If you are doing individual selects on the child tables (vs JOINs) an option would be to query the bits and the use IF statements to either perform the select on the child table or to perform a dummy select if the placeholder was required. If I didn't explain that clearly don't worry because I don't like the option.
A better option, which would support joins, would be to identify a where clause against the first column in an index for the child and use a value that would be first in the index sort, but never actually exist.
SELECT * FROM syscolumns WHERE case when @test-2 = 0 then -1 else 1 end = id
The above will perform an index SEEK and immediately acknoledge that the key can't be found. In your case, rather than a constant (I used 1 above) you would provide the variable containing the actual key value being looked up, of course. If multiple key fields need to be tested you can use a case statement for each of the fields individually.
September 16, 2004 at 1:36 pm
Thanks for this information. I will give some serious consideration to your suggestions.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply