expensive key lookup

Question

expensive key lookup

tcronin 95651

SSCertifiable

Points: 6735
More actions
April 23, 2017 at 2:13 pm

#321078

have the below code from 3rd party app trying to get rid of expensive key lookup. Output from therapyadmin table is causing it, tried index with include caused update issues too many columns, anyone have idea for better approach in the select? Covering index worked but too much overhead with the writes
Thanks
N'select TherapyAdmin_ID, RowVersionNumber, Order_ID, @PatientVisit_ID PatientVisit_ID, AdminNumber, AdminComments ,AdminStartDateTime, AdminEndDateTime, ScheduledAdminNumber, SchedAdminStartDateTime, SchedAdminEndDateTime ,AdministeringStaff_ID, CompletionStatus, NotAdministeredCode, OnHold, AdminRouteCode, AdminSiteCode, AdminDeviceCode, AdminMethodCode ,AdministeredAmount, AdministeredUnits, AdminRecordMethod, Retrieved, PrevAdminStartDateTime, PrevAdminEndDateTime, PreparationTime ,FollowUpTime, LastPatientScanSeconds, RetrieveTime, AdminReportingSource, AdministeredLocation_id, UserDefinedFields, ConsentGiver, ConsentGivenDateTime ,WitnessStaff_ID, WitnessTime from TherapyAdmin with ( index(PatientOrder_TherapyAdmin_FK1_idx)) where Order_ID in (select Order_ID from PatientOrder where PatientOrder.PatientVisit_ID=@PatientVisit_ID) order by Order_ID,ScheduledAdminNumber',N'@PatientVisit_ID bigint',@PatientVisit_ID=379964759

Suggested Topics

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

You've been here long enough to know how to do this.
Here is a formatted version, for all those who struggle to optimise embedded SQL:

DECLARE @PatientVisit_ID BIGINT = 379964759;

SELECT TherapyAdmin_ID, RowVersionNumber, Order_ID, PatientVisit_ID = @PatientVisit_ID, AdminNumber, AdminComments, AdminStartDateTime, AdminEndDateTime, ScheduledAdminNumber, SchedAdminStartDateTime, SchedAdminEndDateTime, AdministeringStaff_ID, CompletionStatus, NotAdministeredCode, OnHold, AdminRouteCode, AdminSiteCode, AdminDeviceCode, AdminMethodCode, AdministeredAmount, AdministeredUnits, AdminRecordMethod, Retrieved, PrevAdminStartDateTime, PrevAdminEndDateTime, PreparationTime, FollowUpTime, LastPatientScanSeconds, RetrieveTime, AdminReportingSource, AdministeredLocation_id, UserDefinedFields, ConsentGiver, ConsentGivenDateTime, WitnessStaff_ID, WitnessTime FROM TherapyAdmin WITH (INDEX(PatientOrder_TherapyAdmin_FK1_idx)) WHERE Order_ID IN ( SELECT Order_ID FROM PatientOrder WHERE PatientOrder.PatientVisit_ID = @PatientVisit_ID ) ORDER BY Order_ID, ScheduledAdminNumber;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Your options are:
- Create a covering index
- Live with the key lookup
- Change the query to not select columns it doesn't need, and then add a covering index

How expensive is expensive? Key lookups show high percentages in the plan, but unless you're looking up many thousands of rows it may not actually be a problem.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

tcronin 95651 - Sunday, April 23, 2017 2:13 PM
have the below code from 3rd party app trying to get rid of expensive key lookup. Output from therapyadmin table is causing it, tried index with include caused update issues too many columns, anyone have idea for better approach in the select? Covering index worked but too much overhead with the writes
Thanks
Snip

Can you change the code? If not, how are you implementing the new index?
Can you post an actual execution plan as a .sql attachment? Cheers.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 4

tried covering too much overhead on updates if I can find better sql may be able to get vendor to adopt attaching plan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Estimated executions: ~19000, actual executions: 0. In that plan they key lookup is not worth removing as it's not doing anything.
Why you've got such bad estimates for row counts is the real question. Is that query likely to return ~19k rows? Is the actual row count of 0 realistic, or did you produce that plan with a non-standard parameter value.

Keep in mind that your options are limited. If you need all those columns to be returned, then you need to either accept the key lookup or create a covering index. If you don't need all the columns, then reduce the select to the ones you do need, and then create a covering index.
There's no magic here.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 6

that's kind of conclusion I have come to, another side issue is getting vendor to understand they really only need 1 years of data accessible by the app now up to 3 years worth

expensive key lookup

Suggested Topics