|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Newbie: Timeout On IN statementI 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. 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) 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? -- Show quoteAdam Machanic Pro SQL Server 2005, available now http://www.apress.com/book/bookDisplay.html?bID=457 -- "Chuck" <Ch***@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. > > > SELECT ProductID, CategoryID, Sales, Qty
FROM Sales s1 WHERE EXISTS (SELECT * FROM Sales s2 WHERE s1.productID = s2.productid) dean Show quote "Chuck" <Ch***@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? -- Show quoteAdam 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" <Ch***@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. >> >> >> > > SEE POST BELOW -- Chuck
Show quote "Adam Machanic" wrote: Dean's query did just that, but, I altered it to include the TOP 100 * ... > 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" <Ch***@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. > >> > >> > >> > > > > > Adam, Dean, 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 auseful explanation "Chuck" <Ch***@discussions.microsoft.com> wrote in message Can you show the query you ended up with? I'm not certain that what 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 ! 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 Show quote "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... >> SELECT ProductID, CategoryID, Sales, Qty >> FROM Sales s1 >> WHERE EXISTS >> (SELECT * >> FROM Sales s2 >> WHERE s1.productID = s2.productid) >> >> dean >> >> "Chuck" <Ch***@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. >>> >>> >>> >> >> > > > >>>Does any one have an explanation of why EXISTS worked so much better than IN? The original attempt with timeout was..."Alexander Kuznetsov" wrote: >when you post both plans, you'll have a better chance of getting a useful explanation > an index on (CategoryID, Sales DESC, productid) might help > 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) Changed to the following with results in 9 seconds... SELECT ProductID, CategoryID, Sales, Qty FROM Sales s1 WHERE EXISTS (SELECT TOP 100 * FROM Sales s2 WHERE (s1.categoryid = s2.categoryid) ORDER BY s2.Sales DESC) Chuck,
Those two queries are not logically equivalent. The old query, essentially, said: "Give me all products where the product is in the top 100 for its category." The new query says: "Give me all products where my category exists." Check your results. -- Show quoteAdam Machanic Pro SQL Server 2005, available now http://www.apress.com/book/bookDisplay.html?bID=457 -- "Chuck" <Ch***@discussions.microsoft.com> wrote in message news:C5E91415-3F01-40D9-8D27-77F69F67EA80@microsoft.com... > >> >>>Does any one have an explanation of why EXISTS worked so much better >> >>>than IN? > > "Alexander Kuznetsov" wrote: >>when you post both plans, you'll have a better chance of getting a > useful explanation >> an index on (CategoryID, Sales DESC, productid) might help >> > > > The original attempt with timeout was... > 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) > > Changed to the following with results in 9 seconds... > SELECT ProductID, CategoryID, Sales, Qty > FROM Sales s1 > WHERE EXISTS > (SELECT TOP 100 * > FROM Sales s2 > WHERE (s1.categoryid = s2.categoryid) > ORDER BY s2.Sales DESC) >  |
|||||||||||||||||||||||
Other interesting topics