SQL - Insane?

[NSMan]

Einstien's definition of insane was "Doing the same thing twice, and expecting different results"

declare @indextyp char(5)
declare @IL char(5)

set @indextyp='D'
set @IL='A'

	select
	names.key1 as name1,
	case when @indextyp='D' then inst.a19 else inst.a18 end as name2,
          inst.b15 as recep#, 
	  inst.a1 as insttype, 
          inst.a17 as instdate,
          inst.key2 as recdate,
          inst.a4 as book,
          inst.a5 as booknumber,
          inst.d1,41 as legal,
          inst.id as id
          from object names, object inst 
      where	
[COLOR=#ff0000]((@IL = 'L' and inst.typ=25) or (@IL='I' and inst.typ=3) or (@IL='A' and inst.typ in (3,25)))
[/color]
[COLOR=green]inst.typ in (3,25)[/color]

	   and names.typ=10
	   and names.link1=inst.id
	   and (names.a5='Grantor' or names.a5='Issued Against')

If I run this with the red code, my query takes 2:45, if I run it with the green code, it takes 1:53. Shouldn't the equivilate to the same thing?

 

[Catadmin]

No.

If I'm reading this correctly, the red code is evaluating 2 conditions three times over. Which means for every set of conditions in parens, the code is going through the entire table looking for that condition.

The green code is evaluating 1 condition once. So it is reading the table only once looking for only one condition.

Computers take things much more literally than humans can. Whereas we can look at a printed spreadsheet for 4 things at the same time (when we're not tired @=), the computer literally has to look for one thing at a time. Sometimes it's fast. In this case, it's just slow enough that you're noticing the difference. Probably due to the size of your table.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[NSMan]

I'm sorry, I ment the green code was taking longer. The red code taking longer would make sense to me, but I swear, I ran that select statement three times, and the green code always took just about that much longer.

 

[NSMan]

And here is something else that just doesn't add up, (to me)

This takes 10 seconds...

drop table nstemp

select 
names.key1 as name1, 
case when 'D'='D' then left(inst.a19,38) else left(inst.a18,38) end as name2, 
inst.b15 as recep#, 
inst.a1 as insttype, 
inst.a17 as instdate, 
inst.key2 as recdate, 
inst.a4 as book, 
inst.a5 as booknumber, 
inst.d1 as legal, 
inst.id as id into [nstemp]
from object names, object inst where 
(('A' = 'L' and inst.typ=25) or ('A'='L' and inst.typ=3) or 
('A'='A' and inst.typ in (3,25))) 
and names.typ=10 
and names.link1=inst.id 
and (('D'='D' and (names.a5='Grantor' or names.a5='Issued Against')) or ('I'='I' and (names.a5='Grantee' or names.a5='Lienholder'))) 
and names.key1>'!' 
and inst.key2 between '74148' and '74513' 
 order by names.key1, 
 inst.id

select * from nstemp

This takes 12...

select 
names.key1 as name1, 
case when 'D'='D' then left(inst.a19,38) else left(inst.a18,38) end as name2, 
inst.b15 as recep#, 
inst.a1 as insttype, 
inst.a17 as instdate, 
inst.key2 as recdate, 
inst.a4 as book, 
inst.a5 as booknumber, 
inst.d1 as legal, 
inst.id as id
from object names, object inst where 
(('A' = 'L' and inst.typ=25) or ('A'='L' and inst.typ=3) or 
('A'='A' and inst.typ in (3,25))) 
and names.typ=10 
and names.link1=inst.id 
and (('D'='D' and (names.a5='Grantor' or names.a5='Issued Against')) or ('I'='I' and (names.a5='Grantee' or names.a5='Lienholder'))) 
and names.key1>'!' 
and inst.key2 between '74148' and '74513' 
 order by names.key1, 
 inst.id

GAHHHHH!!!! We have created an insane monster that derives it's pleasure from proveing its insanity!!!!

 

[Catadmin]

I looked up what you were doing with the inst.typ in (3,25) part of your code and realized that it is actually having to read the table twice just on that one part of code. At this point, I'm W.A.G.ing it, but I'm wondering if the table scan it did on the first two conditionals in red were kept in memory and made it faster for SQL to process the last part of the code because it already knew where all the "3"s and "25"s were. It just needed to check on the "A" part of that last condition.

The other possibility is that the first part of your conditionals in the red code @IL is already cutting your returned records down to a more managable size, THEN looking for the 3 or 25 in the smaller set. Whereas the green code has to look through the entire table because it has no other conditionals cutting down on the returned records.

Regardless, I would check both bits of code in QA with a Display Estimated Execution Plan to see where the green code is hanging up over the red code.

In any case, if you're going to do just the green code, I'd break it up into (inst.typ = 3 or inst.typ =25).

BTW, BOL says that using the IN (3,25) will save you typing, but it does not say that it saves you execution time.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!