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.
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 intASSELECT [yadda yadda]FROM Stores RWHERE bitActive<>0 AND wholesaleOnly = 0 AND cityID=@CityAND 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 RWHERE bitActive<>0 AND wholesaleOnly = 0 AND cityID=@CityAND 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 |
 |
|
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. |
 |
|
TimSman
Posting Yak Master
127 Posts |
Posted - 2010-12-07 : 17:20:39
|
Try this.... @Category int, @City intASSELECT [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 = @CategoryWHERE (R.bitActive <> 0) AND (R.wholesaleOnly = 0) AND (R.cityID=@City)ORDER BY R.storeTypeID DESC , R.storeName |
 |
|
GenNS
Starting Member
11 Posts |
Posted - 2010-12-07 : 18:13:00
|
Thanks TimSmanThat 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 |
 |
|
|
|
|
|
|