no bookmark lookup noticed when i think i need to see it

SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
t1.col3=t2.col3 returns 20 rows...
It is doing a nested loop join with t1 being the outer table and t2 being
the inner table. it is doing a non clustered index seek on t2. The
nonclustered index it is using is on (col2,col3). What I dont understand is
why on my execution plan I dont see a bookmark lookup to fetch the
corresponding row since i need t2.col1
When i run
SELECT t2.* FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
t1.col3=t2.col3
I see a bookmark lookup with the same indexes being used as the previous
query for the 2 tables...Perhaps you have a clustered index on t2(col1)? Remember that the nc indexes uses the clustering key
as row locator.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OTXLtMWiDHA.1456@.TK2MSFTNGP11.phx.gbl...
> SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> t1.col3=t2.col3 returns 20 rows...
> It is doing a nested loop join with t1 being the outer table and t2 being
> the inner table. it is doing a non clustered index seek on t2. The
> nonclustered index it is using is on (col2,col3). What I dont understand is
> why on my execution plan I dont see a bookmark lookup to fetch the
> corresponding row since i need t2.col1
> When i run
> SELECT t2.* FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> t1.col3=t2.col3
> I see a bookmark lookup with the same indexes being used as the previous
> query for the 2 tables...
>sql

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