SQL Search Query

[Robert]

Hello-

I am having a difficult time figuring out the differences between the following 2 queries. The first query ( which should return around 8000 records) when run against a Sql7 DB with 70000 records will always timeout (timeout set at 30 secs), but the 2nd query will return the correct amount of records ( about 8000 ) in under 8 secs.

Could someone shed some light on this for me?

Thank you
Robert

******Start Query 1******
Select Lot, Title, UserId, HighBidder, HighBid,
BidTimes, DateIn, EndDate, Price, Category, Options,
Reserve, StartDate From Auctions A Where (
((title LIKE '%STUB%' )) or ((description LIKE '%STUB%' ))
) And (Status In ('A', 'P&#39 Or (Status In ('B', 'C&#39
And EndDate >= '02/25/2001&#39
) And ((A.Reserve > A.Price And A.Reserve Between 0
And 999999.99)
Or (A.Price Between 0 And 999999.99))
Order By Lot Asc




*****End Query 1*****

******Start Query 2******
Select Lot, Title, UserId, HighBidder, HighBid,
BidTimes, DateIn, EndDate, Price, Category, Options,
Reserve, StartDate From
(Select Lot, Title, UserId, HighBidder, HighBid,
BidTimes, DateIn, EndDate, Price, Category, Options,
Reserve, StartDate,Description From Auctions A Where
(A.Status In ('A', 'P&#39

Or (A.Status In ('B', 'C&#39 And
A.EndDate >= '02/25/2001&#39

) And ((A.Reserve > A.Price And A.Reserve Between 0
And 999999.99)
Or (A.Price Between 0 And 999999.99))
) as X
Where (

( (title LIKE '%STUB%' ))


or ( (description LIKE '%STUB%' ))



)
Order By Lot Asc

******End Query 2******

[cal]

Base on the info,

they should be the same, it looks like the data are already in the cache
Made sure you clean out the cache after each query.

Anway, the best is use query optimizer(query analyzer) to compare multiples query in parallel.



------------
Robert at 3/27/01 1:05:01 PM

Hello-

I am having a difficult time figuring out the differences between the following 2 queries. The first query ( which should return around 8000 records) when run against a Sql7 DB with 70000 records will always timeout (timeout set at 30 secs), but the 2nd query will return the correct amount of records ( about 8000 ) in under 8 secs.

Could someone shed some light on this for me?

Thank you
Robert

******Start Query 1******
Select Lot, Title, UserId, HighBidder, HighBid,
BidTimes, DateIn, EndDate, Price, Category, Options,
Reserve, StartDate From Auctions A Where (
((title LIKE '%STUB%' )) or ((description LIKE '%STUB%' ))
) And (Status In ('A', 'P&#39 Or (Status In ('B', 'C&#39
And EndDate >= '02/25/2001&#39
) And ((A.Reserve > A.Price And A.Reserve Between 0
And 999999.99)
Or (A.Price Between 0 And 999999.99))
Order By Lot Asc




*****End Query 1*****

******Start Query 2******
Select Lot, Title, UserId, HighBidder, HighBid,
BidTimes, DateIn, EndDate, Price, Category, Options,
Reserve, StartDate From
(Select Lot, Title, UserId, HighBidder, HighBid,
BidTimes, DateIn, EndDate, Price, Category, Options,
Reserve, StartDate,Description From Auctions A Where
(A.Status In ('A', 'P&#39

Or (A.Status In ('B', 'C&#39 And
A.EndDate >= '02/25/2001&#39

) And ((A.Reserve > A.Price And A.Reserve Between 0
And 999999.99)
Or (A.Price Between 0 And 999999.99))
) as X
Where (

( (title LIKE '%STUB%' ))


or ( (description LIKE '%STUB%' ))



)
Order By Lot Asc

******End Query 2******