Newbie: Timeout On IN statement

I have a query which returns the Top 100 selling products for each of the 14
categories that we sell. It takes 10 minutes to run, however. Does anyone
have a suggestion on speeding this up.
SELECT ProductID, CategoryID, Sales, Qty
FROM Sales s1
WHERE productid in
(SELECT TOP 100 productid
FROM Sales s2
WHERE (s1.productID = s2.productid)
ORDER BY s2.Sales DESC)
I have tried to sort the View "Sales" that it queries by the column Sales to
speed it up, to no effect. If I run the nested select on its own without th
e
WHERE clause, it runs in 2 seconds.Sorry, Wrong WHERE
SELECT ProductID, CategoryID, Sales, Qty
FROM Sales s1
WHERE productid in
(SELECT TOP 100 productid
FROM Sales s2
WHERE (s1.categoryID = s2.categoryid)
ORDER BY s2.Sales DESC)|||SELECT ProductID, CategoryID, Sales, Qty
FROM Sales s1
WHERE EXISTS
(SELECT *
FROM Sales s2
WHERE s1.productID = s2.productid)
dean
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:2B25C9C7-A42D-46F7-B0FF-A3E4B06EDD31@.microsoft.com...
>I have a query which returns the Top 100 selling products for each of the
>14
> categories that we sell. It takes 10 minutes to run, however. Does
> anyone
> have a suggestion on speeding this up.
> SELECT ProductID, CategoryID, Sales, Qty
> FROM Sales s1
> WHERE productid in
> (SELECT TOP 100 productid
> FROM Sales s2
> WHERE (s1.productID = s2.productid)
> ORDER BY s2.Sales DESC)
> I have tried to sort the View "Sales" that it queries by the column Sales
> to
> speed it up, to no effect. If I run the nested select on its own without
> the
> WHERE clause, it runs in 2 seconds.
>
>|||I think your query is written incorrectly. Don't you really mean:
SELECT ProductID, CategoryID, Sales, Qty
FROM Sales s1
WHERE productid in
(SELECT TOP 100 productid
FROM Sales s2
WHERE (s1.categoryID =
s2.categoryID) --Changed product to category here
ORDER BY s2.Sales DESC)
Aside from that, can you show the view definition? Is there a way to query
less tables than the view touches?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:2B25C9C7-A42D-46F7-B0FF-A3E4B06EDD31@.microsoft.com...
>I have a query which returns the Top 100 selling products for each of the
>14
> categories that we sell. It takes 10 minutes to run, however. Does
> anyone
> have a suggestion on speeding this up.
> SELECT ProductID, CategoryID, Sales, Qty
> FROM Sales s1
> WHERE productid in
> (SELECT TOP 100 productid
> FROM Sales s2
> WHERE (s1.productID = s2.productid)
> ORDER BY s2.Sales DESC)
> I have tried to sort the View "Sales" that it queries by the column Sales
> to
> speed it up, to no effect. If I run the nested select on its own without
> the
> WHERE clause, it runs in 2 seconds.
>
>|||Dean,
Won't that query simply return every row in the Sales table?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Dean" <dvitner@.nospam.gmail.com> wrote in message
news:O6lrF419FHA.1148@.tk2msftngp13.phx.gbl...
> SELECT ProductID, CategoryID, Sales, Qty
> FROM Sales s1
> WHERE EXISTS
> (SELECT *
> FROM Sales s2
> WHERE s1.productID = s2.productid)
> dean
> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
> news:2B25C9C7-A42D-46F7-B0FF-A3E4B06EDD31@.microsoft.com...
>|||an index on (CategoryID, Sales DESC, productid) might help|||SEE POST BELOW -- Chuck
"Adam Machanic" wrote:

> Dean,
> Won't that query simply return every row in the Sales table?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>

> "Dean" <dvitner@.nospam.gmail.com> wrote in message
> news:O6lrF419FHA.1148@.tk2msftngp13.phx.gbl...
>
Adam, Dean,
Dean's query did just that, but, I altered it to include the TOP 100 * ...
ORDER BY "Sales" and it returned the results in 9 seconds. (after changing
the where to reflect my mistake in the original post. Thanks to both of you
,
what a relief !
Does any one have an explanation of why EXISTS worked so much better than IN
?
Chuck Ghastin|||> Does any one have an explanation of why EXISTS worked so much better than
IN?
when you post both plans, you'll have a better chance of getting a
useful explanation|||"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:7FD2E304-00AF-452B-823A-94C1BB34BAA0@.microsoft.com...
> Dean's query did just that, but, I altered it to include the TOP 100 * ...
> ORDER BY "Sales" and it returned the results in 9 seconds. (after
> changing
> the where to reflect my mistake in the original post. Thanks to both of
> you,
> what a relief !
Can you show the query you ended up with? I'm not certain that what
you're describing is logically equivalent to your original query.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||missread it, sorry..
dean
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OnukR$19FHA.3608@.TK2MSFTNGP09.phx.gbl...
> Dean,
> Won't that query simply return every row in the Sales table?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Dean" <dvitner@.nospam.gmail.com> wrote in message
> news:O6lrF419FHA.1148@.tk2msftngp13.phx.gbl...
>