November 13, 2009 at 8:32 am
I have Query that throws BOOK MARK LOOK UP .. I know that INDEXES are not that much good on the Specfic object.
But i don't want to change any Indexes .. Is there anyother way to remove this BOOKMARK Look up ..
Please Kindly let me know
Thanks
November 13, 2009 at 8:42 am
Can you post the table def and query.
In the meantime its worth checking this out:
November 13, 2009 at 9:14 am
Here is the script file , table def , and indexes .. I modifed the actual table names,col names .. pls go through this .. Please let me know ..
Bunch of Thanks :::
/* Script File*/
Declare @Var1 datetime,
@Var2 char(1)
SET @Var1= ''
SET @Var2 = ''
Begin
IF Exists (
SELECT 1 FROM tab1 s
LEFT OUTER JOIN tab2 p ON s.col1 = p.col1
WHERE p.col5 = @Var1 and (p.Col6 <> 'F' or p.Col6 IS NULL)
and Exists(
Select 1 from tab3 i
Where i.col1 = s.col1
AND i.col2 < @Var1
GROUP BY i.Col3
--Having Round(Sum(convert(int,i.col4)), 2) > 0 ) ) -- from 2000
Having Round(Sum(i.col4), 2) > 0 ) ) -- from 2005
begin
RAISERROR ('Not in Scope', 16, 1) WITH SETERROR
RETURN
End
END
/* table defination */
CREATE TABLE [dbo].tab3(
col4 [char](14),
[col5] [char](5),
[col6] [char](3),
[col7] [char](3) ,
[col8] [char](3) ,
col1 [char](14) ,
[col9] [varchar](16) ,
col2 [datetime] NOT NULL,
col3 [float] NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [GD_tab3_rowguid] DEFAULT (newid()),
CONSTRAINT [PK_tab3] PRIMARY KEY NONCLUSTERED
(
[col4] ASC
) ON [PRIMARY]
) ON [PRIMARY]
/* Indexes */
index_22-nonclustered, unique located on PRIMARYrowguid
IX_tab3- nonclustered located on PRIMARYcol5, col6, col7, col4
IX_tab3_4-nonclustered located on PRIMARYcol1
IX_tab3_5-nonclustered located on PRIMARYcol5, col6, col7, col4, col2
IX_tab3_6-nonclustered located on PRIMARYcol5, col6, col2
PK_tab3_7-nonclustered, unique, primary key located on PRIMARY col4
Index used "IX_tab3_4-nonclustered located on PRIMARYcol1" --in Query
Please note that COL3 in table -tab3 does n't have any index .. I am not supposed to change the Indexes or create the new indexes .. I want to know is there anything we can do programmatically to eliminate this book mark look UP ..
Thanks
November 13, 2009 at 9:35 am
Im confused with the numbering of the indexes. You have a Primary key on the table PK_tab3, then state another one PK_tab3_7? Is this what you meant
November 13, 2009 at 9:57 am
The only way to eliminate a bookmark lookup without having the requested columns in an index, would be to force the query to use the clustered index instead of a non-clustered index.
In this case, SQL Server has determined that using a non-clustered index plus a bookmark lookup would be more efficient than that. So, you could do it, but it would almost certainly be less efficient and slower.
The right way to eliminate a bookmark lookup is to set up the right non-clustered indexes that cover the columns you're querying.
There is no programmatic means of eliminating a bookmark lookup other than those two methods. Either cover the query with an index, or force the query to not use anything but the clustered index.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 13, 2009 at 10:13 am
PK_tab3_7-nonclustered, unique, primary key located on PRIMARY col4
is typing mistake -
PK_tab3-nonclustered, unique, primary key located on PRIMARY col4 is correct one ...
November 13, 2009 at 10:14 am
Hi GSquared ,
can you please tell How can i use Forcing index ...
some example please
thanks
November 13, 2009 at 10:23 am
John Paul-702936 (11/13/2009)
can you please tell How can i use Forcing index ...
Look up table hints in SQL's Books Online.
Sure you want to do that? Are you 100% certain that you know better than the query optimiser what's the best way to run the query?
If you do, test both the original and the hinted to make sure that the hinted query really is quicker.
Why don't you want to change the indexes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2009 at 10:34 am
I wonder why you focus on not having that bookmark lookup - there might be other ways to help performance, too...
Example:
What is the reason to do a sum aggregation over a column with CHAR(14) definition?
Maybe changing that column to INT would avoid implicit conversion, helping performance as well.
November 13, 2009 at 10:38 am
why do you have two indexes with the same leading columns?
IX_tab3- nonclustered located on PRIMARY col5, col6, col7, col4
IX_tab3_5-nonclustered located on PRIMARY col5, col6, col7, col4, col2
Surely the first one is redundant?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply