Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query Taking long time

Posted on 2013-11-04
7
Medium Priority
?
507 Views
Last Modified: 2013-11-06
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
Comment
Question by:rbhargaw
  • 3
  • 3
7 Comments
 

Author Comment

by:rbhargaw
ID: 39622854
If I run  on os.TxeventId = rent.TxEventId , then the query runs faster.

Any reason why?
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39623025
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39625616
You'll probably need this index:

TxOrganStatus ( TxeventId, TxEntityTypeCode ) INCLUDE ( OrganStatus )
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:rbhargaw
ID: 39625652
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39625752
>> 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
 

Author Closing Comment

by:rbhargaw
ID: 39628374
Satisfied with the explanation.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39628468
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question