Query Taking long time
If I run the query , the data comes back fast but removing the comment, it takes forever
takes forever when the commented lines dealing with TxOrganStatus are included?
Can anyone let me know how to optimize this?
select
pl.patientid
,txe.Txeventid
from ( select distinct patientid
from trntb_LabResult
where LabResultId between 43699849
and 43781505
) pl
join trntb_TxEvent txe
on txe.PatientId = pl.patientid
join trntb_RecipientTxEntity rent
on rent.TxEventId = txe.TxEventId
--join trntb_TxOrganStatus os
-- on os.TxeventId = txe.TxEventId
-- and os.TxEntityTypeCode = rent.TxEntityTypeCode
where rent.TxDate is not null
and rent.TxEntityTypeCode = 5
-- and os.OrganStatus = 19071 -- Active
Trntb_TxOrganStatus has the following Index:
CREATE NONCLUSTERED INDEX EventEntityCode
ON dbo.trntb_TxOrganStatus(Tx
takes forever when the commented lines dealing with TxOrganStatus are included?
Can anyone let me know how to optimize this?
select
pl.patientid
,txe.Txeventid
from ( select distinct patientid
from trntb_LabResult
where LabResultId between 43699849
and 43781505
) pl
join trntb_TxEvent txe
on txe.PatientId = pl.patientid
join trntb_RecipientTxEntity rent
on rent.TxEventId = txe.TxEventId
--join trntb_TxOrganStatus os
-- on os.TxeventId = txe.TxEventId
-- and os.TxEntityTypeCode = rent.TxEntityTypeCode
where rent.TxDate is not null
and rent.TxEntityTypeCode = 5
-- and os.OrganStatus = 19071 -- Active
Trntb_TxOrganStatus has the following Index:
CREATE NONCLUSTERED INDEX EventEntityCode
ON dbo.trntb_TxOrganStatus(Tx
Run the query with the "Include Actual Execution Plan" option. Do you have any table scans or key lookups? Is there a suggested index change?
You'll probably need this index:
TxOrganStatus ( TxeventId, TxEntityTypeCode ) INCLUDE ( OrganStatus )
TxOrganStatus ( TxeventId, TxEntityTypeCode ) INCLUDE ( OrganStatus )
ASKER
The Slow.txt is the execuation for query which takes forever
Fast txt is for query running faster
Slow.txt
Fast-Rent.txeventid.txt
Fast txt is for query running faster
Slow.txt
Fast-Rent.txeventid.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Satisfied with the explanation.
CORRECTION, I left out "NOT", which makes it much more clear:
"
Likely there's an index that includes that column but NOT one or both of the other columns referenced in the query. When you ...
"
"
Likely there's an index that includes that column but NOT one or both of the other columns referenced in the query. When you ...
"
ASKER
Any reason why?