Bookmark lookup takes time in SQL server 2000

Hi All
I have an application with SQL Server 2000 SP4 as my RDBMS.
There is a table with around 1200000 records with all the necessary
indexes defined when i execute the query through query analyzer the no
of rows returned are around 300000, execution plan shows a Bookmark
Lookup. But the bookmark lookup taks lot of time?
Also if i remove the particular index it shows me a Table Scan in
Execution Plan.
Can anyone help me explain why Bookmark lookup takes the same time as
Table Scan?
Thanks & Regards
Vishal.I am not sure if I can but perhaps a covering index would be helpful here.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159864815.076365.227450@.k70g2000cwa.googlegroups.com...
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>|||Vishal wrote:
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
A bookmark lookup occurs when the index that was used to satisfy the
query doesn't contain all of the columns that were requested from a
specific table. Below is a snippet from an earlier response that I
posted to a similar question:
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Vishal,
to add another point to the mix, you might just want to check if it's
possible to use a covering index.
I mention this as you are referring to queries against one single table and
there is often a huge time saving if you can cover the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Can u tell me more about covering Indexes'
As in :
What do they mean'
How to define them?
Rgds
Vishal.
Paul Ibison wrote:
> Vishal,
> to add another point to the mix, you might just want to check if it's
> possible to use a covering index.
> I mention this as you are referring to queries against one single table and
> there is often a huge time saving if you can cover the query.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Tracy Thanks for ur info on Bookmark Lookup.
But does anyone have any Idea Why Bookmark Lookup is taking up same
time as a Table Scan does?
The table on which the query executes has about 80 columns defined.
Could this be the reason when I Execute a Select * on such a Table it
takes same time as a Table Scan?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> > Hi All
> > I have an application with SQL Server 2000 SP4 as my RDBMS.
> > There is a table with around 1200000 records with all the necessary
> > indexes defined when i execute the query through query analyzer the no
> > of rows returned are around 300000, execution plan shows a Bookmark
> > Lookup. But the bookmark lookup taks lot of time?
> > Also if i remove the particular index it shows me a Table Scan in
> > Execution Plan.
> > Can anyone help me explain why Bookmark lookup takes the same time as
> > Table Scan?
> > Thanks & Regards
> > Vishal.
> >
> A bookmark lookup occurs when the index that was used to satisfy the
> query doesn't contain all of the columns that were requested from a
> specific table. Below is a snippet from an earlier response that I
> posted to a similar question:
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the
> table, using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to
> get the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action,
> depending on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Vishal,
here's an article that sums it up nicely:
http://www.informit.com/articles/article.asp?p=27015&seqNum=6&rl=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Vishal,
avoiding the * (all columns) and just getting the ones you need might make
it possible to use a covering index (as in other part of this thread).
But to answer your question with a question, why would you assume that
bookmark lookups are always faster than a tablescan? IE there's a logical
read ("set statistics io on" to see) cutoff point after which carrying out
the bookmark lookup will prove more expensive than a tablescan, eg in a
tablescan you'll read each page once while using a bookmark lookup you might
read each page 100 times.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .