Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • Last Modified:

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(TxEventId,TxEntityTypeCode)
0
rbhargaw
Asked:
rbhargaw
  • 3
  • 3
1 Solution
 
rbhargawFounderAuthor Commented:
If I run  on os.TxeventId = rent.TxEventId , then the query runs faster.

Any reason why?
0
 
Brian CroweDatabase AdministratorCommented:
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?
0
 
Scott PletcherSenior DBACommented:
You'll probably need this index:

TxOrganStatus ( TxeventId, TxEntityTypeCode ) INCLUDE ( OrganStatus )
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
rbhargawFounderAuthor Commented:
The Slow.txt is the execuation for query which takes forever
Fast txt is for query running faster
Slow.txt
Fast-Rent.txeventid.txt
0
 
Scott PletcherSenior DBACommented:
>> If I run  on os.TxeventId = rent.TxEventId , then the query runs faster.
Any reason why? <<

Likely there's an index that includes that column but one or both of the other columns referenced in the query.  When you use a column that's not in the index, SQL must then go back to the main table, either via a full table scan or from an index lookup.

Change the existing index, if there is one, to include the other two columns.
0
 
rbhargawFounderAuthor Commented:
Satisfied with the explanation.
0
 
Scott PletcherSenior DBACommented:
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 ...
"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now