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 2005 Forums
 Transact-SQL (2005)
 Need some help to optimize this and make it run

Author  Topic 

TheFuzz4
Starting Member

6 Posts
Posted - 2009-07-07 : 12:55:24
Hey Everyone,

This is my first post on here I have this query that is currently running and it takes it anywhere from 45 seconds to a full min to return back just 252 rows. A lot of this has to do with the calculations that are taking place in the query and also in the WHERE clause as well. I am not the original author of this query it was given to me to optimize but I'm running out of ideas here on how to optimize this monster. I have gone through and added in some joins to kill off some of the sub queries but in doing so it actually caused it to run even longer. This query is actually used in a view if it needs to be converted over to a stored procedure to filter it down at the start that is fine but I'd rather keep it as a view so I don't have to go back into the code for the application and change everything out to work as procedure instead of a view. Thank you in advance for your help with this.

SELECT DISTINCT
a.DisbursementID,
a.DisbursementAuthorizationID,
h.ServiceType,
CAST( ( SELECT SUM( AmountApplied ) FROM dtDisbursementDetails WHERE DisbursementID = a.DisbursementID ) AS MONEY ) AS DisbursementAmount,
( SELECT TOP 1 CustomInteger FROM dtEvents WHERE EventTypeID = 23 AND CaseID IN ( SELECT CaseID FROM dtEvents WHERE EventID = e.EventID ) ORDER BY EventDate DESC ) AS AssignPCID,
( SELECT TOP 1 LastName + ', ' + FirstName FROM dtPerson WHERE PersonID = e.PayerPersonID ) AS ClientName,
( SELECT CASE h.ServiceTypeID WHEN 6 THEN
(SELECT TOP 1 ISNULL(LastName + ', ','') + ISNULL(FirstName, '') FROM dtPerson WHERE PersonID = (SELECT TOP 1 BilledBy FROM dtReceivables WHERE ReceivableID = e.ReceivableID))
ELSE (SELECT TOP 1 Organization FROM dtOrganizations WHERE OrganizationID = (SELECT TOP 1 ConsumerOrganizationID FROM dtReceivables WHERE ReceivableID = e.ReceivableID )) END) AS Victim,
CAST( (( SELECT SUM( ISNULL(AmountApplied,0) ) FROM dtPaymentDetails
WHERE PaymentDetailID IN ( SELECT PaymentDetailID FROM dtDisbursementDetails WHERE
DisbursementID = a.DisbursementID )) -
(SELECT ISNULL(SUM(AmountApplied),0) FROM cu_sacs_dtCheckDetails WHERE CheckID IN (SELECT CheckID FROM
cu_sacs_dtCheck WHERE VoidDate IS NULL) AND CheckID IN (SELECT CheckID FROM
cu_sacs_dtCheckDetails WHERE DisbursementDetailID IN (SELECT DisbursementDetailID
FROM dtDisbursementDetails WHERE DisbursementID = a.DisbursementID)))) AS MONEY) AS RemainingAmount,
a.GroupCheck,
h.ServiceTypeID,
( SELECT CASE h.ServiceTypeID WHEN 6 THEN
(SELECT TOP 1 PersonID FROM dtPerson WHERE PersonID = (SELECT TOP 1 BilledBy FROM dtReceivables WHERE ReceivableID = e.ReceivableID))
ELSE (SELECT TOP 1 OrganizationID FROM dtOrganizations WHERE OrganizationID = (SELECT TOP 1 ConsumerOrganizationID FROM dtReceivables WHERE ReceivableID = e.ReceivableID )) END) AS VictimID,
a.GroupCheckByID,
e.ProviderOrganizationID,
(SELECT OrganizationID FROM ulSecUser WHERE PersonID = AuthorizedByID) AS AuthorizingOrganization,
(SELECT ISNULL(LastName + ', ', '') + ISNULL(FirstName,'') FROM dtPerson WHERE PersonID = AuthorizedByID) AS AuthorizedBy
FROM cu_sacs_dtDisbursementAuthorization a
JOIN dtDisbursements b ON a.DisbursementID = b.DisbursementID
JOIN dtDisbursementDetails c ON b.DisbursementID = c.DisbursementID
JOIN dtPaymentDetails d ON c.PaymentDetailID = d.PaymentDetailID
JOIN dtReceivables e ON d.ReceivableID = e.ReceivableID
JOIN dtOrganizationServiceFees f ON e.FeeID = f.OrganizationServiceFeeID
JOIN dtOrganizationServices g ON f.OrganizationServiceID = g.OrganizationServiceID
JOIN ulServiceTypes h ON g.ServiceTypeID = h.ServiceTypeID
WHERE a.Authorized = 1 AND a.AuthorizedDate IS NOT NULL AND a.PrintedDate IS NULL
AND ISNULL(( SELECT SUM( AmountApplied ) FROM dtPaymentDetails WHERE PaymentDetailID IN
( SELECT PaymentDetailID FROM dtDisbursementDetails WHERE DisbursementID = a.DisbursementID )), 0 )
-
ISNULL(( SELECT SUM( AmountApplied ) FROM cu_sacs_dtCheckDetails WHERE CheckID IN
( SELECT CheckID FROM cu_sacs_dtCheck WHERE VoidDate IS NULL ) AND CheckID IN
( SELECT CheckID FROM cu_sacs_dtCheckDetails WHERE DisbursementDetailID IN
( SELECT DisbursementDetailID FROM dtDisbursementDetails WHERE DisbursementID = a.DisbursementID))), 0 )
> 0


Jason
Handel IT

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2009-07-07 : 13:23:22
I'd start by trying to move those correlated subqueries down into the from clause (as either subqueries or just joined in tables). Same with the subqueries in the where clause.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-07-07 : 13:43:38
And make use of the new windowed function to take care of the MAX and SUM within a group.

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

TheFuzz4
Starting Member

6 Posts
Posted - 2009-07-07 : 13:59:21
Could you explain to me the use of the windowed function? This is something new that I have not heard of yet. Thanks.

Jason
Handel IT
Go to Top of Page

TheFuzz4
Starting Member

6 Posts
Posted - 2009-07-07 : 14:07:47
I think I have found a nice explanation of this from this site.
[url]http://articles.techrepublic.com.com/5100-10878_11-6168402.html#[/url] for using the window functions.

Jason
Handel IT
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-07-07 : 14:10:14
This is a start, but I leaning on giving up because of the utter mess. I am about halfway done.
There are no table aliases in front of columns names and so on...
SELECT		a.DisbursementID,
		a.DisbursementAuthorizationID,
		h.ServiceType,
		SUM(c.AmountApplied) OVER (PARTITION BY a.DisbursementID) AS DisbursementAmount,


( SELECT TOP 1 CustomInteger FROM dtEvents WHERE EventTypeID = 23 AND CaseID IN ( SELECT CaseID FROM dtEvents WHERE EventID = e.EventID ) ORDER BY EventDate DESC ) AS AssignPCID,
( SELECT TOP 1 LastName + ', ' + FirstName FROM dtPerson WHERE PersonID = e.PayerPersonID ) AS ClientName,

		CASE h.ServiceTypeID
			WHEN 6 THEN  (SELECT TOP 1 ISNULL(LastName + ', ','') + ISNULL(FirstName, '') FROM dtPerson WHERE PersonID = (SELECT TOP 1 BilledBy FROM dtReceivables WHERE ReceivableID = e.ReceivableID))
			ELSE (SELECT TOP 1 Organization FROM dtOrganizations WHERE OrganizationID = (SELECT TOP 1 ConsumerOrganizationID FROM dtReceivables WHERE ReceivableID = e.ReceivableID )) 
		END AS Victim,



		SUM(COALESCE(d.AmountApplied, 0)) OVER (PARTITION BY d.PaymentDetailID) 
		- SUM(AmountApplied),0) FROM cu_sacs_dtCheckDetails WHERE CheckID IN (SELECT CheckID FROM 
cu_sacs_dtCheck WHERE VoidDate IS NULL) AND CheckID IN (SELECT CheckID FROM 
cu_sacs_dtCheckDetails WHERE DisbursementDetailID IN (SELECT DisbursementDetailID 
FROM dtDisbursementDetails WHERE DisbursementID = a.DisbursementID)))) AS MONEY) AS RemainingAmount,

		a.GroupCheck,
		h.ServiceTypeID,


( SELECT CASE h.ServiceTypeID WHEN 6 THEN 
(SELECT TOP 1 PersonID FROM dtPerson WHERE PersonID = (SELECT TOP 1 BilledBy FROM dtReceivables WHERE ReceivableID = e.ReceivableID))
ELSE (SELECT TOP 1 OrganizationID FROM dtOrganizations WHERE OrganizationID = (SELECT TOP 1 ConsumerOrganizationID FROM dtReceivables WHERE ReceivableID = e.ReceivableID )) END) AS VictimID,

		a.GroupCheckByID,
		e.ProviderOrganizationID,

		xx.OrganizationID AS AuthorizingOrganization,

(SELECT ISNULL(LastName + ', ', '') + ISNULL(FirstName,'') FROM dtPerson WHERE PersonID = AuthorizedByID) AS AuthorizedBy
FROM		cu_sacs_dtDisbursementAuthorization AS a
INNER JOIN	dtDisbursements AS b ON b.DisbursementID = a.DisbursementID
INNER JOIN	dtDisbursementDetails AS c ON c.DisbursementID = b.DisbursementID
INNER JOIN	dtPaymentDetails AS d ON d.PaymentDetailID = c.PaymentDetailID
INNER JOIN	dtReceivables AS e ON e.ReceivableID = d.ReceivableID
INNER JOIN	dtOrganizationServiceFees AS f ON f.OrganizationServiceFeeID = e.FeeID
INNER JOIN	dtOrganizationServices AS g ON g.OrganizationServiceID = f.OrganizationServiceID
INNER JOIN	ulServiceTypes AS h ON h.ServiceTypeID = g.ServiceTypeID
LEFT JOIN	ulSecUser AS xx ON xx.PersonID = {}.AuthorizedByID
WHERE		a.Authorized = 1
		AND a.AuthorizedDate IS NOT NULL
		AND a.PrintedDate IS NULL

AND ISNULL(( SELECT SUM( AmountApplied ) FROM dtPaymentDetails WHERE PaymentDetailID IN 
( SELECT PaymentDetailID FROM dtDisbursementDetails WHERE DisbursementID = a.DisbursementID )), 0 )
- 
ISNULL(( SELECT SUM( AmountApplied ) FROM cu_sacs_dtCheckDetails WHERE CheckID IN 
( SELECT CheckID FROM cu_sacs_dtCheck WHERE VoidDate IS NULL ) AND CheckID IN 
( SELECT CheckID FROM cu_sacs_dtCheckDetails WHERE DisbursementDetailID IN 
( SELECT DisbursementDetailID FROM dtDisbursementDetails WHERE DisbursementID = a.DisbursementID))), 0 )
> 0


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

TheFuzz4
Starting Member

6 Posts
Posted - 2009-07-07 : 14:37:46
Thank you all for all of your help with this. I am now starting to make some progress on this I think.

Jason
Handel IT
Go to Top of Page

TheFuzz4
Starting Member

6 Posts
Posted - 2009-07-07 : 15:21:26
Does anyone have any ideas on how to optimize this section of the where clause? I tried to use the windowing functions in there only to find out that I can't put window functions in a where clause. I also can't put in direct aggregate functions either unless they are contained in a sub query that is not related to the main query directly. Thanks




AND ISNULL(( SELECT SUM( AmountApplied ) FROM dtPaymentDetails WHERE PaymentDetailID IN
( SELECT PaymentDetailID FROM dtDisbursementDetails WHERE DisbursementID = a.DisbursementID )), 0 )
-
ISNULL(( SELECT SUM( AmountApplied ) FROM cu_sacs_dtCheckDetails WHERE CheckID IN
( SELECT CheckID FROM cu_sacs_dtCheck WHERE VoidDate IS NULL ) AND CheckID IN
( SELECT CheckID FROM cu_sacs_dtCheckDetails WHERE DisbursementDetailID IN
( SELECT DisbursementDetailID FROM dtDisbursementDetails WHERE DisbursementID = a.DisbursementID))), 0 )
> 0



Jason
Handel IT
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