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...
>

Newbie: Select Top N - what next?

I've Just started using MS SQL 2000 with MS Access 2000. I can select
the first 10 products like this :-
SELECT top 10 dbo_products.ProductName
FROM dbo_Product
ORDER BY dbo_Product.ProductName DESC;
But when the user wants the next 10, products 11-20 and then maybe
products 21-30. How do i select them?http://www.aspfaq.com/show.asp?id=2120
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MaD Matt" <matt@.REMOVEMEworkforcedirect.com> wrote in message
news:i9soh1hlrreb0012ri13kgf4i4a1rv8irm@.
4ax.com...
> I've Just started using MS SQL 2000 with MS Access 2000. I can select
> the first 10 products like this :-
> SELECT top 10 dbo_products.ProductName
> FROM dbo_Product
> ORDER BY dbo_Product.ProductName DESC;
> But when the user wants the next 10, products 11-20 and then maybe
> products 21-30. How do i select them?
>|||Mad
drop table #w
create table #w
(
col1 int identity,
col2 varchar(2)
)
insert into #w values ('y')--do it within a loop
select * from #w
select col1 from
(select top 10 col1
from
(select top 20 col1 from #w order by col1 ASc)AS F
order by col1 DESC) as t1
order by col1 asc
"MaD Matt" <matt@.REMOVEMEworkforcedirect.com> wrote in message
news:i9soh1hlrreb0012ri13kgf4i4a1rv8irm@.
4ax.com...
> I've Just started using MS SQL 2000 with MS Access 2000. I can select
> the first 10 products like this :-
> SELECT top 10 dbo_products.ProductName
> FROM dbo_Product
> ORDER BY dbo_Product.ProductName DESC;
> But when the user wants the next 10, products 11-20 and then maybe
> products 21-30. How do i select them?
>