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)
 Getting Rid of unions

Author  Topic 

nathan.bekker
Starting Member

11 Posts
Posted - 2010-10-18 : 15:17:09
I am trying to improve the perfomance of my query.
How would I get rid of the Union ALL?
The only Difference in these 2 Queries is the Disposition Column. I am looking for the Newest Record By "Contactdate" for Each Disposition. Please feel free to ask questions or post comments.
Thanks

SELECT B.Disposition,B.ContactDate,B.Status FROM(
     SELECT   Disposition, ContactDate,Status, max(ContactDate) OVER(PARTITION BY patient_id) AS [MaxDate]
          FROM     (SELECT C.Patient_Id,
                           CONVERT(DATETIME,CONVERT (VARCHAR, C.Contact_date, 101) + ' ' + CONVERT (VARCHAR, CONVERT (TIME, C.Contact_time, 108), 108)) AS ContactDate,
                           Disposition,Status
                    FROM   Contact C INNER JOIN Patient P
                    on C.Patient_id = P.Patient_Id
                    WHERE  Disposition like 'Counseling Call _ Week 2'
                    AND P.Patient_Id = @patientId) AS [CALL]
                    
	) AS B
	WHERE ContactDate = MaxDate
	UNION ALL
	SELECT B.Disposition,B.ContactDate,B.Status FROM(
	 SELECT   Disposition , ContactDate,Status, max(ContactDate) OVER(PARTITION BY patient_id) AS [MaxDate]
          FROM     (SELECT C.Patient_Id,
                           CONVERT(DATETIME,CONVERT (VARCHAR, C.Contact_date, 101) + ' ' + CONVERT (VARCHAR, CONVERT (TIME, C.Contact_time, 108), 108)) AS ContactDate,
                           Disposition,Status
                    FROM   Contact C INNER JOIN Patient P
                    on C.Patient_id = P.Patient_Id
                    WHERE  Disposition like 'Counseling Call _ Week 3'
                    AND P.Patient_Id = @patientId) AS [CALL]
                    
	) AS B
	WHERE ContactDate = MaxDate

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-10-18 : 15:18:14
Why are you using LIKE when you don't have a wild card character?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-10-18 : 15:18:59
WHERE (Disposition = 'Counseling Call _ Week 2' OR Disposition = 'Counseling Call _ Week 3')
AND P.Patient_Id = @patientId) AS [CALL]

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

nathan.bekker
Starting Member

11 Posts
Posted - 2010-10-18 : 15:20:38
Yeah I am using the _ as my 1 char Wild. I have a dash in the string but when I try Disposition = 'Counseling Call - Week 2' I dong get any results.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-10-18 : 15:22:26
If Disposition = 'Counseling Call - Week 2' isn't working, then your data contains other characters.

You need to figure out exactly what the string is so that you don't require LIKE.


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

nathan.bekker
Starting Member

11 Posts
Posted - 2010-10-18 : 15:25:29
It sure looks like dash. But is not recognized as one.
Regardless that is not my current problem. Thanks for pointing that out though. I will rewrite my strings and replace the wierd dash with a regular dash.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-10-18 : 15:28:25
I answered your question about how to combine them (put them together with an OR). It is very unlikely it'll fix your performance problem. Let us know though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

nathan.bekker
Starting Member

11 Posts
Posted - 2010-10-18 : 15:43:54
That does not return the result ia need. What your query returns is the newest record (by contactDate) from the Contact Table
for that @patientId. What I am tring to get is All the newest records broken down by each Disposition. Sometimes I would have a CONTACT Disposition 'Counseling Call – Week 2' With a Status of 'Complete' and ContactDate of 10/1/2010 and another CONTACT with the same dispsoition,status but a ContactDate of 10/2/2010 I need to return the newest one (10/2/2010). And like that for Each Disposition I have.
Go to Top of Page

nathan.bekker
Starting Member

11 Posts
Posted - 2010-10-19 : 17:40:03
I understand that Unions are not the best in a Query.
But If I need to return 8-10 Rows of Data for a report and my Query contans 8-10 Union All Statements like I posted earlier, How much does that actually slow down the system? Sometimes the query take 1 second to execute and sometimes 0. Does it even make sense to find a way around it?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-10-19 : 18:31:43
The problem isn't necessarily with the union. I can't say whether or not a rewrite would fix the performance issue as we haven't diagnosed the problem yet.

What does the execution plan show for the query?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

nathan.bekker
Starting Member

11 Posts
Posted - 2010-10-20 : 12:03:22
The Execution Plan Recommended creating a Non-Clustered Index . I did that.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site