SQLTeam.com | Weblogs | Forums

Sql and key lookup issue


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?
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


Without any more details, all I could suggest is to get rid of the index hint, which "forces" SQL to use that index (if possible):

/with ( index(PatientOrder_TherapyAdmin_FK1_idx))/


Don't double-post. I've wound up answering this in a different thread and therefore my response is now in that other thread and discussion becomes fragmented.