how to tune this query
-
The following query when executed will involve in a bookmark look up ,how to avoid bookmark look up for this query
SELECT
T1.CONFLICT_ID,
CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
T1.LAS
T1.CREATED,
T1.MODIFICAT
T1.ROWD,
T1.STD_NIT,
T2.CURCYD,
T1.PRI_D,
T2.SUBTYPE
T1.PRI_METH
T1.PRODID,
T1.CONTRACTPRICE,
T1.XGROUPPRICE,
T1.XLIST_PRICE,
T1.SVCPERMT,
T1.SVC_PERCT,
T1.VOL_DISID,
T3.DISCNT_CD
FROM
dbo.S_PRILSTITEM T1
INNER JOIN dbo.SPRILST T2 ON T1.PRI_D = T2.ROWD
LEFT OUTER JOIN dbo.SVOLDISCNT T3 ON T1.VOL_DISID = T3.ROWD
WHERE
(T1.PRI_D = '5-4-330')
Thanks in advance
-
A bookmark lookup means that SQL Server must read from the data pages to handle the query. The only way to avoid a bookmark lookup is to use a covering index. This is great in theory, but in the real world, you don't want a covering index for every query that you are running so you use them when needed to improve performance.
You mentioned that this query produces a bookmark lookup. Is this query performing poorly or did you just notice that it was performing a bookmark lookup? If it performing OK, leave it alone. If it is a dog, look at adding a coverinng index.
-
Thanks for your suggestion. The query is performing ok.
-
I am new to sql server. Can you please how to create a covering index? on whic columns?
-
Here is a quote from the BOL:
Covered queries can improve performance. Covered queries are queries where all the columns specified in the query are contained within the same index. For example, a query retrieving columns a and b from a table that has a composite index created on columns a, b, and c is considered covered. Creating indexes that cover a query can improve performance because all the data for the query is contained within the index itself; only the index pages, not the data pages, of the table must be referenced to retrieve the data, thereby reducing overall I/O. Although adding columns to an index to cover queries can improve performance, maintaining the extra columns in the index incurs update and storage costs.
-End of quote
Basically a covered index is another term for a composite index.
-SQLBill
-
SQLBill (12/17/2007)
Here is a quote from the BOL:Covered queries can improve performance. Covered queries are queries where all the columns specified in the query are contained within the same index. For example, a query retrieving columns a and b from a table that has a composite index created on columns a, b, and c is considered covered. Creating indexes that cover a query can improve performance because all the data for the query is contained within the index itself; only the index pages, not the data pages, of the table must be referenced to retrieve the data, thereby reducing overall I/O. Although adding columns to an index to cover queries can improve performance, maintaining the extra columns in the index incurs update and storage costs.
-End of quote
Basically a covered index is another term for a composite index.
-SQLBill
one that includes (or covers) all of the columns from that table being used in the query.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Just curious, what are you trying to achieve with these two statements?
CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
From what I see you're converting a field to retrieve just the date and then concatenating it with the time from the same field. If the field already has the date and time, why the need for the converts?
--------------------
Colt 45 - the original point and click interface
-
This query is given to me by the developer, so i dont know why he is using this convert statemens.
i created a covering index for table T1,but still it uses bookmark lookup to retrieve data from table T1
why is it so?
-
p1naga (12/18/2007)
This query is given to me by the developer, so i dont know why he is using this convert statemens.i created a covering index for table T1,but still it uses bookmark lookup to retrieve data from table T1
why is it so?
Try removing the convert statements and running the query. I think that they are probably causing the bookmark lookup.
It looks like the developer is trying to do formatting using T-SQL and this is the wrong place for formatting.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Also - you will want to make sure you use the appropriate column ordering for it. The first ones mentioned should be the ones used in the joins and the WHERE statement (since you don't have order by or group by), then followed by the others. If you don't order them correctly - the query analyzer won't see any reason to use the index.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Are any of the items in the FROM clause actually views instead of tables?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p1naga (12/17/2007)
Thanks for your suggestion. The query is performing ok.If its performing OK, then leave the index as it was. A massive, great covering index (especially in SQL 2000) may gain you a small amount of time, but at a cost of more disk usage and more overhead for updates/inserts
Unless the bookmark lookup is a bottleneck in the query, leave it alone. Especially if you're retrievign a small number of rows. There are worse ways to run a query
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I agree with Gail here. That is why I asked the question initially. The simple fact is that 'if it's not broke, don't fix it'! You could end up indexing your entire database if you think you need to prevent every bookmark lookup that you come accross.
-
Heh... on the other side of the coin, how do you know it's not broke unless you try to fix it. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You don't. But one's time is, unfortunately limited, and a more productive use of one's time may be to fix the things that are known to be broke, before investigating the things that just might be broke. 😉
Besides, there are enough ways to check. If the query's not running excessively long, or doing large number of IOs 1, and if the bookmark lookup isn't at least 85% of the total query's cost, leave the poor thing alone :hehe:
(1): Relative to other queries in the system and based on user perception, among other things.
Seriously, though. To justify a covering index of around 15 columns, on SQL 2000, the query would have to be a really badly performing one. In my opinion, that is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply