Bookmark Lookup

Question

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

All ideas gratefully received.

Many thanks

Answer 1

0

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.

answered Aug 26 '11 at 05:11 AM

Grant Fritchey ♦♦
62.1k ● 12 ● 20 ● 66

Thanks, I'm not sure how do this though as the sProductName and the uGUID are in a different table.

Aug 26 '11 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 '11 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 '11 at 05:40 AM Grant Fritchey ♦♦

(comments are locked)

Answer 2

0

Even though it is 2 tables joined, the bookmark lookup should just be against one object.

answered Aug 26 '11 at 02:32 AM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

Have added the query execution plan information regarding the bookmark lookup for your thoughts. Many thanks

Aug 26 '11 at 03:23 AM Mrs_Fatherjack

Put a covering index on SLCL for iFormID, iCheckedByID, iProdID and see what effect that has

Aug 26 '11 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 '11 at 03:41 AM Mrs_Fatherjack

and the execution plan was the same with the same properties on the bookmark lookup?

Aug 26 '11 at 03:58 AM Kev Riley ♦♦

Yes, it was identical.

Aug 26 '11 at 04:01 AM Mrs_Fatherjack

(comments are locked)

Bookmark Lookup

Follow this question

Related Questions