Ugly query and key lookup

1

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

2

I presume this is the arguments to sp_ExceuteSQL?

Please confirm that the datatype of PatientOrder.PatientVisit_ID is actually BigInt?

Why not using a JOIN? (ALthough I don't know if that would make any difference ... but it might)

Presumably PatientOrder.PatientVisit_ID is indexed with Order_ID as an include column (or Clustered Index on PatientVisit_ID)

TherapyAdmin.Order_ID also indexed. Can't see having INCLUDE columns on that would help, given the number of columns that are included in the SELECT, but having ScheduledAdminNumber in the index may help with the ORDER BY.

Unless the schema is dependent on the currently connected user then prefix the table names with schema (normally "dbo.") which will slightly improve the parse time.

Remove the index hint until all avenues for getting the Query Optimiser to choose the best plan have been exhausted. Its very rare to be able to beat the Query Optimiser with an index hint.

make sure the indexes are recently rebuilt and the statistics. Use FULLSCAN on the rebuild of the statistics if that isn't the default - just in case that helps the query planner. At least you'll know what your "best possible baseline" is then.

Put the PatientOrder.PatientVisit_ID in the TherapyAdmin table ???