Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Better way to do this than multiple EXISTS?

Author  Topic 

GenNS
Starting Member

11 Posts

Posted - 2010-12-07 : 13:23:29
Hi,

I have this query, which returns the correct results, but is pretty slow. If I comment out either one of the AND EXISTS lines, it runs in under a second. With both of them enabled, however, it takes over 9 seconds. Blech.

I'm just not knowledgeable enough about SQL to know a better way to do this. Any suggestions?

Thanks in advance. Here's the current query:

...
@Category int,
@City int
AS

SELECT [yadda yadda]
FROM Stores R
WHERE bitActive<>0 AND wholesaleOnly = 0 AND cityID=@City
AND EXISTS (SELECT 1 FROM ItemsProds WHERE bitHide=0 AND storeID = R.lngID)
AND EXISTS (SELECT 1 FROM StoreCats WHERE catID=@Category AND storeID=R.lngID)
ORDER BY storeTypeID DESC, storeName


GenNS

TimS
Posting Yak Master

198 Posts

Posted - 2010-12-07 : 16:22:49
Possible sultion; I have not done SQL for about 4 years; but, I think this will work.
Tim S.

SELECT [yadda yadda]
FROM Stores R
WHERE bitActive<>0 AND wholesaleOnly = 0 AND cityID=@City
AND EXISTS (
SELECT 1
FROM Stores R
LEFT JOIN ItemsProds ip ON ip.bitHide=0 AND ip.storeID = R.lngID
LEFT JOIN StoreCats sc on sc.catID=@Category AND sc.storeID=R.lngID
WHERE ip.storeID IS NOT NULL and sc.storeID IS NOT NULL
)
ORDER BY storeTypeID DESC, storeName
Go to Top of Page

GenNS
Starting Member

11 Posts

Posted - 2010-12-07 : 17:11:18
Thanks TimS, but that doesn't return the correct results.

The original query will only list stores that are tied to the @Category ID, and that have at least one visible product.

My original query's first "AND EXISTS" clause looks for the product, and the second one checks to make sure there's a match in the StoreCats table.
Go to Top of Page

TimSman
Posting Yak Master

127 Posts

Posted - 2010-12-07 : 17:20:39
Try this.

...
@Category int,
@City int
AS

SELECT
[yadda yadda]
FROM
Stores AS R
INNER JOIN ItemsProds AS ip
ON R.lngID = ip.storeID
AND ip.bitHide = 0
INNER JOIN StoreCats AS sc
ON R.lngID = sc.storeID
AND sc.catID = @Category
WHERE
(R.bitActive <> 0)
AND (R.wholesaleOnly = 0)
AND (R.cityID=@City)
ORDER BY
R.storeTypeID DESC
, R.storeName


Go to Top of Page

GenNS
Starting Member

11 Posts

Posted - 2010-12-07 : 18:13:00
Thanks TimSman

That is approximately a zillion times faster, although I'm not sure why, since people always say to use WHERE EXISTS rather than INNER JOIN when the results don't include any data from the joined tables.

Again, thanks a bunch.

GenNS
Go to Top of Page
   

- Advertisement -