Need help from SQL guru

[DT4K]

OK, I've got two versions of a query. Seems to me like both should take exactly the same amount of time to run. In fact, one runs in about 4 seconds and the other takes 6 minutes to complete. Both of them return the same records. About 40 records get returned. The only difference is that one uses >= some date and the other uses between some date and now.

Here is the one that takes 4 seconds:

select distinct t.TestName, t.NumericOrText, t.testgroup
from tblTests t join tblQualityData qd
on t.TestName = qd.TestName
where TestGroup <> 'BatchTags'
and ItemDataAutoID in
(select ItemDataAutoID
from tblItemData
where (EquipmentID like '622010%' or EquipmentID like '%MakeToBatch%')
and StartDateTime between '2005-02-21 10:13:00' and getdate())
order by t.testgroup, t.TestName

Here is the one that takes 6 minutes:

select distinct t.TestName, t.NumericOrText, t.testgroup
from tblTests t join tblQualityData qd
on t.TestName = qd.TestName
where t.TestGroup <> 'BatchTags'
and qd.ItemDataAutoID in
(select ItemDataAutoID
from tblItemData
where (EquipmentID like '622010%' or EquipmentID like '%MakeToBatch%')
and StartDateTime >= '2005-02-21 10:13:00')
order by t.testgroup, t.TestName

Notice that the only difference between the two is in the last line of the subquery. Now the part that really throws me off is that if I run each of those subqueries independently, they both execute instantly and return about 14 ItemDataAutoID's. So from what I can see, if both subqueries return the same thing in the same amount of time and everything except the subquery is identical, then both queries should take the same amount of time. Obviously I'm missing something. The only thing I could think of is that maybe for some reason, the second version is resulting in an unintentional correlated subquery, but I can't figure out why that would be.

Any help would be greatly appreciated.

 

[Armitage]

The first query has two bounds. If there isn't an appropriate index, that means twice the number of comparison. Additionally, your comparing to the results of a function call, which likely rules out some optimizations. Try the first query with a constant value instead of the getdate() call.

 

[DT4K]

The first query is the one that is very fast.
It's the second one that has major issues.
Since the getdate() is in the very fast query, it's not the problem.

 

[Armitage]

Heh - well, there goes that theory 😛

 

[MrChad]

What indexes exist on the table?

 

[DT4K]

tblItemData (ItemDataAutoID decimal(18,0), EquipmentID (nvarchar(15), StartDateTime datetime)
ItemDataAutoID is the primary key and there is an index on EquipmentID. tblItemData has 128,000 records.

tblTests (TestName nvarchar(15), NumericOrText nchar(1), TestGroup nvarchar(20))
No indexes and about 200 records.

tblQualityData (TestName nvarchar(15), ItemDataAutoID decimal(18,0), etc.)
There are indexes on TestName and ItemDataAutoID and the table has close to a million records.

I looked at the execution plans and they are pretty different. Trying to figure out what they really mean is beyond my SQL expertise, but here is how they start:

Slow query:
Clustered Index Seek of tblItemData that executes 780,000 times with a row count of 0.
Index Scan of tblQualityData that executes 461 times with a row count of 780,000.

Fast query:
Clustered Index Scan of tblItemData that executes twice with a rowcount of 14.
Index Seek of tblQualityData that executes 14 times with a row count of 0.

 

[MrChad]

Interesting.

What happens if you use other variations?

e.g.

select distinct t.TestName, t.NumericOrText, t.testgroup
from tblTests t join tblQualityData qd
on t.TestName = qd.TestName
where TestGroup <> 'BatchTags'
and exists
(select ItemDataAutoID
from tblItemData
where tblItemData.ItemDataAutoID = qd.ItemDataAutoID
and (EquipmentID like '622010%' or EquipmentID like '%MakeToBatch%')
and StartDateTime between '2005-02-21 10:13:00' and getdate())
order by t.testgroup, t.TestName

or

select distinct t.TestName, t.NumericOrText, t.testgroup
from tblTests t, tblQualityData qd, tblItemData id
where t.TestName = qd.TestName
and qd.ItemDataAutoID = id.ItemDataAutoID
and t.TestGroup <> 'BatchTags'
and (id.EquipmentID like '622010%' or id.EquipmentID like '%MakeToBatch%')
and id.StartDateTime between '2005-02-21 10:13:00' and getdate())
order by t.testgroup, t.TestName

 

[MrChad]

Any luck?