Query Taking long time

Posted on 2013-11-04
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?

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:
    ON dbo.trntb_TxOrganStatus(TxEventId,TxEntityTypeCode)
Question by:rbhargaw
  • 3
  • 3

Author Comment

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

Any reason why?
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?
LVL 69

Expert Comment

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

TxOrganStatus ( TxeventId, TxEntityTypeCode ) INCLUDE ( OrganStatus )
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.


Author Comment

ID: 39625652
The Slow.txt is the execuation for query which takes forever
Fast txt is for query running faster
LVL 69

Accepted Solution

Scott Pletcher earned 500 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.

Author Closing Comment

ID: 39628374
Satisfied with the explanation.
LVL 69

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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now