Tuning up this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Tuning up this query

Author

Topic

babloo
Starting Member

35 Posts

Posted - 2013-04-12 : 15:46:58

Hi I am currently using this Query and takes forever to show me the results.

SELECT DISTINCT table1.bill_no,
table1.mrn,
table1.last_name,
table1.first_name,
table1.discharged,
table2.dservice
FROM table1
LEFT JOIN table2 ON table1.visit_id=table2._fk_visit
LEFT JOIN table3 ON table1.visit_id=table3.VISIT_ID

WHERE (table1.discharged BETWEEN '10/01/2012' AND '03/01/2013')
ANd (table2.finalized <> 'Y')
AND (table2.dservice IN ('MPE', 'ERS', 'EMR'))
AND (table2.vpt_type <> 'IP')
AND (table3.visit_id IN
(SELECT distinct table3.visit_id
FROM table3
WHERE a_date >= '10/01/2012'
GROUP BY table3.visit_id
HAVING SUM(CASE WHEN doctype IN ('DOC1', 'DOC2') THEN 1 ELSE 0 END) = 0) )
AND (table3.doctype IN ('DOC3', 'DOC4'))
ORDER BY DISCHARGED

Is there a better way to achive the result by changing my Query.

I want to make sure that DOC1 and DOC2 are missing and DOC3 and DOC4 are not missing.

Thanks Babloo

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-12 : 16:43:23

Couple of comments before we think about optimizing:

1. You are using LEFT joins in your query, but then you also have columns from the right tables in the WHERE clause. That effectively turns the joins into INNER JOINs. Is that okay as per your business logic?

2. I am assuming that one visit_id can have multiple DOC's. Assuming that is true, is your goal to get visit_id's only if there are no doctypes of DOC1 and DOC2 or, are you trying to get records where there are any DOC3's or DOC4's?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-13 : 02:12:31

Also you're doing an IN check with table3 and you're not having any fields from table3 in select. If that being case, there's no need of in check, Instead remove left join with table3 and replace it with inner join with subquery that you've used for the IN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

babloo
Starting Member

35 Posts

Posted - 2013-04-15 : 08:56:14

Thanks James and Visakh.

1. I was using LEFT join since table1 is the visit table, a patient may have 1 visit but may also have more than 1 document so I was using LEFT Join but I can use INNER JOIN that makes more sense. Thank you.

2. My goal is to get visits for those patients who do not have DOC1 & DOC2 but must have DOC3 & DOC4, if they dont' meet this criteria I don't want to see those patients.

Viaskh, I am using IN check to make sure if the patient has DOC3 & DOC4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-15 : 12:27:47

quote:
Originally posted by babloo

Thanks James and Visakh.

1. I was using LEFT join since table1 is the visit table, a patient may have 1 visit but may also have more than 1 document so I was using LEFT Join but I can use INNER JOIN that makes more sense. Thank you.

2. My goal is to get visits for those patients who do not have DOC1 & DOC2 but must have DOC3 & DOC4, if they dont' meet this criteria I don't want to see those patients.

Viaskh, I am using IN check to make sure if the patient has DOC3 &
DOC4

i understood that

my only point was in that case you could just take IN condition out and replace the entire subquery to the place where you just join to table3

ie like


SELECT DISTINCT	 table1.bill_no,
table1.mrn,
table1.last_name,
table1.first_name,
table1.discharged,
table2.dservice
FROM table1 
LEFT JOIN table2 ON table1.visit_id=table2._fk_visit 
INNER JOIN (
SELECT distinct table3.visit_id
FROM table3
WHERE a_date >= '20120110'
GROUP BY table3.visit_id
HAVING SUM(CASE WHEN doctype IN ('DOC1', 'DOC2') THEN 1 ELSE 0 END) = 0
AND table3.doctype IN ('DOC3', 'DOC4')
)t
 ON table1.visit_id=t.VISIT_ID
WHERE (table1.discharged >= '20120110' AND '20130103')
ANd	(table2.finalized <> 'Y') 
AND (table2.dservice IN ('MPE', 'ERS', 'EMR')) 
AND (table2.vpt_type <> 'IP') 
AND (table3.visit_id IN 
ORDER BY DISCHARGED

Also for the datepart logic use like what i've shown

reason is here

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

babloo
Starting Member

35 Posts

Posted - 2013-04-15 : 15:20:07

Thanks Viaskh, when I try your query I get this error message.

Msg 4145, Level 15, State 1, Line 18
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

babloo
Starting Member

35 Posts

Posted - 2013-04-15 : 15:26:55

Fixed it, that was because of the dates.

babloo
Starting Member

35 Posts

Posted - 2013-04-15 : 15:30:40

Thanks Viaskh, this helps alot from 2 minutes to 25 seconds and no SQL Deadlocks either. Really helpful query. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-16 : 02:08:18

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources