Solved

Query Rewrite

Posted on 2013-01-04
5
257 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
Comment Utility
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:ScottPletcher
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Help in SQL Full Text Search 3 28
Word Template Mail merge with vb.net 4 39
Sql query 34 17
Delete Trigger in SQL Server2008R2 5 0
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

19 Experts available now in Live!

Get 1:1 Help Now