Solved

Query Rewrite

Posted on 2013-01-04
5
258 Views
Last Modified: 2013-01-07
I have a query that is performing poorly and I would like suggestions on how to rewrite it without using the "not in" syntax. The query is:

SELECT
E.[ENCOUNTER_NBR]
,E.[MEMBER_NBR]
,E.[SUB_SOURCE_CD]
FROM [IRADS].[dbo].[IRAD_ENCOUNTER] E
where
E.IRADS_DATA_Source =1
and E.ENCOUNTER_KEY not in
(SELECT distinct EE.ENCOUNTER_KEY
FROM [IRADS].[dbo].[IRAD_ENCOUNTERERROR] EE
where
EE.[ERROR_KEY] in
(select ER.ERROR_KEY
from [IRADS].[dbo].[IRAD_ERROR] ER
where ER.ERROR_CD in ('X01','X02','X03','X04','X05')
and ER.[IRADS_DATA_Source] =1 )
and EE. [IRADS_DATA_Source] =1
and EE.ENCOUNTER_KEY not in
(select EEF.ENCOUNTER_KEY
from [IRADS].[dbo].[IRAD_ENCOUNTERERROR_Filtered] EEF
where EEF.IRADS_DATA_Source =1 ))
0
Comment
Question by:SharonBernal
5 Comments
 
LVL 12

Expert Comment

by:Jared_S
ID: 38745258
This just replaces the "in" with an "exists" . There may be some other way to improve on the joins that can't be found without the data.

SELECT
E.[ENCOUNTER_NBR]
,E.[MEMBER_NBR]
,E.[SUB_SOURCE_CD]

FROM [IRADS].[dbo].[IRAD_ENCOUNTER] E

where
E.IRADS_DATA_Source =1

AND

NOT EXISTS
(SELECT null FROM
[IRADS].[dbo].[IRAD_ENCOUNTERERROR] EE
WHERE EE.ENCOUNTER_KEY = E.ENCOUNTER_KEY
AND EE. [IRADS_DATA_Source] =1

AND EXISTS
(SELECT null FROM [IRADS].[dbo].[IRAD_ERROR] ER
WHERE ER.ERROR_KEY = EE.[ERROR_KEY] AND
ER.ERROR_CD in ('X01','X02','X03','X04','X05')
and ER.[IRADS_DATA_Source] =1 )

AND NOT EXISTS
(SELECT null FROM [IRADS].[dbo].[IRAD_ENCOUNTERERROR_Filtered] EEF
WHERE EEF.ENCOUNTER_KEY = EE.ENCOUNTER_KEY
AND EEF.IRADS_DATA_Source =1))
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38745471
It seems like "IRAD_ENCOUNTERERROR_Filtered" might be a view on "IRAD_ENCOUNTERERROR" -- if so, it's likely a more extensive re-write could help further.
0
 
LVL 12

Expert Comment

by:sachitjain
ID: 38751270
Could you please tell count of records in each of following tables:
[IRADS].[dbo].[IRAD_ENCOUNTER]
[IRADS].[dbo].[IRAD_ENCOUNTERERROR]
[IRADS].[dbo].[IRAD_ERROR]
IRADS].[dbo].[IRAD_ENCOUNTERERROR_Filtered]

And also what are counts of records returned by each of following queries

select ER.ERROR_KEY
from [IRADS].[dbo].[IRAD_ERROR] ER
where ER.ERROR_CD in ('X01','X02','X03','X04','X05')
      and ER.[IRADS_DATA_Source] =1


select EEF.ENCOUNTER_KEY
from [IRADS].[dbo].[IRAD_ENCOUNTERERROR_Filtered] EEF
where EEF.IRADS_DATA_Source =1


SELECT distinct EE.ENCOUNTER_KEY
FROM [IRADS].[dbo].[IRAD_ENCOUNTERERROR] EE
where EE.[ERROR_KEY] in (select ER.ERROR_KEY
                                          from [IRADS].[dbo].[IRAD_ERROR] ER
                                          where ER.ERROR_CD in ('X01','X02','X03','X04','X05')
                                                and ER.[IRADS_DATA_Source] =1 )
      and EE. [IRADS_DATA_Source] =1
      and EE.ENCOUNTER_KEY not in
            (select EEF.ENCOUNTER_KEY
            from [IRADS].[dbo].[IRAD_ENCOUNTERERROR_Filtered] EEF
            where EEF.IRADS_DATA_Source =1 )
0
 

Author Comment

by:SharonBernal
ID: 38751676
Each of these tables millions of records. Would a left outer join be beneficial?
0
 
LVL 12

Accepted Solution

by:
Jared_S earned 500 total points
ID: 38751806
You don't want to do that, an outer join will affect the results of your query.

What kind of indexes do you have on the tables? If your tables don't use proper indexing, that can really make performance suffer.

There is a difference between a bad performing query, and a query that takes a long time to run. You are covering a lot of data, so I'd expect that you have the latter.

Scott had a good point that has been overlooked - is IRAD_ENCOUNTERERROR_Filtered a view? If you open SQL Server Management Studio can you find it as a table?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
powershell and sql server - alerting 7 77
Sql user function 7 32
Rename SQL Instance/SQL Developer Edition 2012 2 12
sql query questions 2 25
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

864 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

25 Experts available now in Live!

Get 1:1 Help Now