April 12, 2011 at 3:32 am
Hi,
There is a view mytest. This view is created from 2 tables table1 union all table2.
When tried to see the perofrmance of the query using estimation execution plan getting the bookmarks.
Can anyone tell me how to remove the bookmarks so that the perfomrnace increase
Regards,
Naveen
April 12, 2011 at 6:38 am
A key lookup or a RID lookup, operation is caused when the nonclustered index that the optimizer uses doesn't have all the columns to satisify the query. The common method around this is to use the INCLUDE command to store extra columns at the leaf level of the index. But, you have to be careful. You don't want to add all columns from the table to the INCLUDE. Another option is to write the query such that it uses the clustered index to gather data instead of a non-clustered index. That avoids the lookup operation.
Without seeing the structure, the query and the execution plan, that's about all I can tell you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 13, 2011 at 12:28 am
There is a view defined as below
CREATE VIEW TotalStats_FloorByFloor_v
AS
SELECT
EquipmentNumber_FK,
RemoteMonitoringType_FK,
StatisticsStartTime,
StatisticsEndTime,
starts,
door_operations,
relevellings,
door_reopenings_by_lightcurtain_cuts,
nudgings,
door_reopenings_by_safety_edge,
door_reopenings_by_unknown_reason,
bad_stops,
FloorNbr
FROM
(
SELECT
EquipmentNumber_FK,
RemoteMonitoringType_FK,
MIN(StatisticsStartTime) AS StatisticsStartTime,
MAX(StatisticsEndTime) AS StatisticsEndTime,
SUM(CONVERT(BIGINT, TotalStarts)) AS Starts,
SUM(CONVERT(BIGINT, DoorOperations)) AS door_operations,
SUM(CONVERT(BIGINT, Knx3NbrRelevellings)) AS ReLevellings,
NULL AS door_reopenings_by_lightcurtain_cuts,
NULL AS Nudgings,
NULL AS door_reopenings_by_safety_edge,
NULL AS door_reopenings_by_unknown_reason,
NULL AS bad_stops,
NULL AS FloorNbr
FROM
ElevatorTotalStatistics
WHERE
RemoteMonitoringType_FK = 'KNX3'
GROUP BY
EquipmentNumber_FK,
RemoteMonitoringType_FK,
CONVERT(CHAR(10), StatisticsEndTime, 121)
UNION ALL
SELECT
EquipmentNumber_FK,
RemoteMonitoringType_FK,
MIN(StatisticsStartTime) AS StatisticsStartTime,
MAX(StatisticsEndTime) AS StatisticsEndTime,
SUM(CONVERT(BIGINT, ISNULL(NbrStartsUp, 0) + ISNULL(NbrStartsDown, 0))) AS Starts,
SUM(CONVERT(BIGINT, NbrDoorOperations)) AS door_operations,
SUM(CONVERT(BIGINT, NbrRelevels)) AS ReLevellings,
SUM(CONVERT(BIGINT, NbrDoorReopByLightCurtainCuts)) AS door_reopenings_by_lightcurtain_cuts,
SUM(CONVERT(BIGINT, NbrNudging)) AS Nudgings,
SUM(CONVERT(BIGINT, NbrDoorReopBySafetyEdge)) AS door_reopenings_by_safety_edge,
SUM(CONVERT(BIGINT, NbrDoorReopByUnknownReason)) AS door_reopenings_by_unknown_reason,
SUM(ISNULL(ElevatorFloorStatistics.NbrBadStops, 0) +
ISNULL(ElevatorFloorStatistics.NbrBadStopsUp, 0) +
ISNULL(ElevatorFloorStatistics.NbrBadStopsDown, 0) +
ISNULL(ElevatorFloorStatistics.NbrBadStop3InchesUp, 0) +
ISNULL(ElevatorFloorStatistics.NbrBadStop3InchesDown, 0)) AS bad_stops,
Floor AS FloorNbr
FROM
ElevatorFloorStatistics
WHERE
RemoteMonitoringType_FK = 'KNXM'
GROUP BY
EquipmentNumber_FK,
RemoteMonitoringType_FK,
Floor,
CONVERT(CHAR(10), StatisticsEndTime, 121)
) TotalStartsTable
The structrues of the tables Elevatorfloorstatstics and elevatortotalstatistics is
StatisticsStartTimedatetimeno8 no(n/a)(n/a)NULL
EquipmentNumber_FKnumericno918 0 no(n/a)(n/a)NULL
Floorsmallintno25 0 no(n/a)(n/a)NULL
RemoteMonitoringType_FKvarcharno10 yesnonoSQL_Latin1_General_CP1_CI_AS
AnalyzerType_FKvarcharno10 yesnonoSQL_Latin1_General_CP1_CI_AS
EquipmentClass_FKvarcharno10 yesnonoSQL_Latin1_General_CP1_CI_AS
StatisticsEndTimedatetimeno8 no(n/a)(n/a)NULL
NbrDoorOperationsintno410 0 no(n/a)(n/a)NULL
NbrDoorReopByLightCurtainCutsintno410 0 yes(n/a)(n/a)NULL
NbrDoorReopBySafetyEdgeintno410 0 yes(n/a)(n/a)NULL
NbrDoorReopByOpenButtonintno410 0 yes(n/a)(n/a)NULL
NbrDoorReopByUnknownReasonintno410 0 yes(n/a)(n/a)NULL
NbrNudgingintno410 0 yes(n/a)(n/a)NULL
NbrLightCurtainCutsintno410 0 yes(n/a)(n/a)NULL
NbrPhotocellCutsintno410 0 yes(n/a)(n/a)NULL
NbrRelevelsintno410 0 yes(n/a)(n/a)NULL
NbrBadStopsintno410 0 yes(n/a)(n/a)NULL
NbrBadStopsUpintno410 0 yes(n/a)(n/a)NULL
NbrBadStopsDownintno410 0 yes(n/a)(n/a)NULL
NbrStartsUpintno410 0 yes(n/a)(n/a)NULL
NbrStartsDownintno410 0 yes(n/a)(n/a)NULL
NbrBadStop3InchesUpintno410 0 yes(n/a)(n/a)NULL
NbrBadStop3InchesDownintno410 0 yes(n/a)(n/a)NULL
NbrStartsUpAndDownTotalintno410 0 yes(n/a)(n/a)NULL
CreateDatedatetimeno8 no(n/a)(n/a)NULL
I_EqNum_Floor_ElevatorFloorStatisticsnonclustered located on PRIMARYEquipmentNumber_FK, Floor
PK_ElevatorFloorStatisticsclustered, unique, primary key located on PRIMARYStatisticsStartTime, EquipmentNumber_FK, Floor
elevatortotalstatisticsis:
StatisticsStartTimedatetimeno8 no(n/a)(n/a)NULL
EquipmentNumber_FKnumericno918 0 no(n/a)(n/a)NULL
StatisticsEndTimedatetimeno8 no(n/a)(n/a)NULL
RemoteMonitoringType_FKvarcharno10 yesnonoSQL_Latin1_General_CP1_CI_AS
AnalyzerType_FKvarcharno10 yesnonoSQL_Latin1_General_CP1_CI_AS
EquipmentClass_FKvarcharno10 yesnonoSQL_Latin1_General_CP1_CI_AS
TotalStartsintno410 0 no(n/a)(n/a)NULL
TotalRunTimeintno410 0 yes(n/a)(n/a)NULL
DoorOperationsintno410 0 yes(n/a)(n/a)NULL
Knx3NbrRelevellingsintno410 0 yes(n/a)(n/a)NULL
CreateDatedatetimeno8 no(n/a)(n/a)NULL
PK_ElevatorTotalStatisticsclustered, unique, primary key located on PRIMARYEquipmentNumber_FK, CreateDate
when i use the query
SELECT
StatisticsStartTime,
StatisticsEndTime,
Starts
FROM
TotalStats_FloorByFloor_v
WHERE
EquipmentNumber_FK = @EquipmentNumber_FK AND
FloorNbr = @FloorNbr
ORDER BY
StatisticsEndTime DESC
I am getting the bookmarks
Can you please tell us on this
April 13, 2011 at 5:08 am
There are a couple of ways you could try to fix this. Probably the easiest is to look at the column order on the primary key of the first table. Instead of the columns StatisticsStartTime, EquipmentNumber_FK, Floor, try changing the order to EquipmentNumber_FK,Floor,StatisticsStartTime. You could then get rid of the non-indexed column entirely.
Assuming that's not possible, you could add the two date columns from the query to the nonclustered index as INCLUDE columns.
However, changing the nonclustered will only help this query. If you add another column to the SELECT list it won't work any more. How many different ways are you going to query the view? Is the most common access path to use EquipmentNUmber_FK and Floor or will other queries use StatisticsStartTime? Also, while a key lookup operation is not desirable, it's not the end of the world. How badly is it hurting your performance?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 13, 2011 at 1:45 pm
This is a question more than an answer, but wouldn't you want, RemoteMonitoringType_FK, to be part of the index used to satisfy this query as it is part of the view definition?
So I'd think that you might want the Clustered index (Primary Key in this case) to be what Grant has suggested and then add an index on RemoteMonitoringType_FK.
Can you get an execution plan and attach it?
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
April 14, 2011 at 2:21 am
Hello,
seeing your query I realize there is some code in excess. Your query is like this,
SELECT * FROM
(
SELECT ...
UNION ALL
SELECT ...
) TotalStartsTable
you can remove the outer query.
Next, your UNION uses two close queries against the same table, maybe you can merge both queries in only one to improve performance.
Regards,
Francesc
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply