x

Bookmark Lookup

Hi

I'm trying to optimise a stored procedure in SQL 2000 and in the Query Execution Plan the highest Query Cost is on a Bookmark Lookup, however all the information I have looked at says to create an index to resolve this but the script is two tables joined so am a little stuck as to how to resolve.

**** Edit ******

Here is the information from the query execution plan, as you can see it mentions one table but in the output it mentions the joined table too.

![alt text][1]

All ideas gratefully received.

Many thanks

[1]: /upfiles/bookmark_lookup.jpg
more ▼

asked Aug 26, 2011 at 02:27 AM in Default

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 60 62 66

Table and index scripts along with the query please :o)
Aug 26, 2011 at 02:29 AM WilliamD
Sorry, having trouble getting something that will make sense.
Aug 26, 2011 at 03:22 AM Mrs_Fatherjack
That part of the execution plan just tells us you have the bookmark lookup, maybe you can supply us with the table & index creation scripts along with the actual t-sql query of the sproc.
Aug 26, 2011 at 03:43 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
To create a covering index in SQL SErver 2000, you have to add ALL columns from the output to the key. The only ones you don't have to add, are the column (or columns) that makes up the clustered index on that table (they'll be included automatically anyway). From the index above, you're missing the sProductName column and the uGUID.
more ▼

answered Aug 26, 2011 at 05:11 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

Thanks, I'm not sure how do this though as the sProductName and the uGUID are in a different table.
Aug 26, 2011 at 05:35 AM Mrs_Fatherjack
Are they the clustered key? Then you don't need to include them. But, you should have seen a different plan if they are.
Aug 26, 2011 at 05:39 AM Grant Fritchey ♦♦
Also, the tool tips are nice, but get down to the properties to really see good info.
Aug 26, 2011 at 05:40 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Even though it is 2 tables joined, the bookmark lookup should just be against one object.
more ▼

answered Aug 26, 2011 at 02:32 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

Have added the query execution plan information regarding the bookmark lookup for your thoughts. Many thanks
Aug 26, 2011 at 03:23 AM Mrs_Fatherjack
Put a covering index on SLCL for iFormID, iCheckedByID, iProdID and see what effect that has
Aug 26, 2011 at 03:38 AM Kev Riley ♦♦

It's not made any difference. This is what I did:

USE [LIVE_SPHINX]
GO

/****** Object:  Index [IX_SPHINX_linkCustomerLink_4]    Script Date: 08/26/2011 11:33:56 ******/ 
CREATE NONCLUSTERED INDEX [IX_SPHINX_linkCustomerLink_4] ON [dbo].[SPHINX_linkCustomerLink] 
(
    [iProdID] ASC,
    [iFormID] ASC,
    [iCheckedByID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
Aug 26, 2011 at 03:41 AM Mrs_Fatherjack
and the execution plan was the same with the same properties on the bookmark lookup?
Aug 26, 2011 at 03:58 AM Kev Riley ♦♦
Yes, it was identical.
Aug 26, 2011 at 04:01 AM Mrs_Fatherjack
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x476
x42
x8
x1

asked: Aug 26, 2011 at 02:27 AM

Seen: 1383 times

Last Updated: Aug 26, 2011 at 03:22 AM