Solved

Query Taking long time

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

Expert Comment

by:ScottPletcher
ID: 39625616
You'll probably need this index:

TxOrganStatus ( TxeventId, TxEntityTypeCode ) INCLUDE ( OrganStatus )
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 69

Accepted Solution

by:
ScottPletcher 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.
0
 

Author Closing Comment

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

Expert Comment

by:ScottPletcher
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

910 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

16 Experts available now in Live!

Get 1:1 Help Now