August 19, 2015 at 2:40 pm
Can anyone look and comment how can I make the following query more efficient especially the subqueries
SELECT DISTINCT
C.OldDealerID
,C.NameLong
,C.NameShort
,C.MailingState
,CC.NameFirst
,CC.NameLast
,CC.Phone
,CC.FaxPhone
,MR.Name
,CC.Address
,CC.City
,CC.Zip
,C.MailingStreet
,C.MailingCity
,CC.EMailAddress
---,(SELECT NameShort FROM Channels C WHERE C.ChannelID = C.AssignedBranch) AS Facilitator
--,(select NameShort from Channels C where C.channelID = C.assignedbranch) as Facilitator
,C.DateLastReview
,HC.HoldingCoDescription
---Funded, Approved,Conditioned, Declined
,(Select Count(C2.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join Contracts2 c2 on a.AppID = c2.AppID Where C.ChannelID = c.ChannelID and c2.DateContractFunded > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and c2.DateContractFunded < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) ) As FundedLastYear
,(Select Count(ad.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join AppDetails ad on a.AppID = ad.AppID Where C.ChannelID = c.ChannelID and StatusAppDetail = 'A' and DateDetailDisposed > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and DateDetailDisposed < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)))) As ApprovedLastYear
,(Select Count(ad.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join AppDetails ad on a.AppID = ad.AppID Where C.ChannelID = c.ChannelID and StatusAppDetail ='I' and DateDetailDisposed > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and DateDetailDisposed < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)))) As ConditionedLastYear
,(Select Count(ad.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join AppDetails ad on a.AppID = ad.AppID Where C.ChannelID = c.ChannelID and StatusAppDetail = 'D' and DateDetailDisposed > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and DateDetailDisposed < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)))) As DeclinedLastYear
,(Select Count(C2.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join Contracts2 c2 on a.AppID = c2.AppID Where C.ChannelID = c.ChannelID and c2.DateContractFunded > (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) ) As FundedThisYear
,(Select Count(ad.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join AppDetails ad on a.AppID = ad.AppID Where C.ChannelID = c.ChannelID and StatusAppDetail = 'A' and DateDetailDisposed > (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)))) As ApprovedThisYear
,(Select Count(ad.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join AppDetails ad on a.AppID = ad.AppID Where C.ChannelID = c.ChannelID and StatusAppDetail ='I' and DateDetailDisposed > (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)))) As ConditionedThisYear
,(Select Count(ad.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join AppDetails ad on a.AppID = ad.AppID Where C.ChannelID = c.ChannelID and StatusAppDetail = 'D' and DateDetailDisposed > (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)))) As DeclinedThisYear
From
Channels AS C
,ChannelProductPlan AS CPP
,ChannelContacts AS CC
,tblLuMktReps AS MR
,tblLuHoldingCo AS HC
Where ActiveFlag = 'Y'
and C.ChannelID = CPP.ChannelID
and C.ChannelID = CC.ChannelID
and Relationship = 1
and C.MarketRepID = MR.MarketRepID
and C.OldDealerID <> 1234
and Channeltype = 1
and C.HoldingCoID = HC.HoldingCoID
or ActiveFlag = 'Y'
and C.ChannelID = CPP.ChannelID
and C.ChannelID = CC.ChannelID
and Relationship = 1
and C.MarketRepID = MR.MarketRepID
and C.OldDealerID <> 1234
and Channeltype = 6
and C.HoldingCoID = HC.HoldingCoID
Order By MR.Name, C.MailingState, C.NameLong
August 19, 2015 at 4:08 pm
At 1st glance, it looks like something along these lines should work...
DECLARE
@StartLastYear DATE,
@StartThisYear DATE
SELECT
@StartLastYear = DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP) -1, 1, 1),
@StartThisYear = DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1)
SELECT DISTINCT
C.OldDealerID,
C.NameLong,
C.NameShort,
C.MailingState,
CC.NameFirst,
CC.NameLast,
CC.Phone,
CC.FaxPhone,
MR.Name,
CC.Address,
CC.City,
CC.Zip,
C.MailingStreet,
C.MailingCity,
CC.EMailAddress,
C.DateLastReview,
HC.HoldingCoDescription,
---Funded, Approved,Conditioned, Declined
facd.FundedLastYear,
facd.ApprovedLastYear,
facd.ConditionedLastYear,
facd.DeclinedLastYear
--- and so on
FROM
Channels AS C
JOIN ChannelProductPlan AS CPP
ON C.ChannelID = CPP.ChannelID
JOIN ChannelContacts AS CC
ON C.ChannelID = CC.ChannelID
JOIN tblLuMktReps AS MR
ON C.MarketRepID = MR.MarketRepID
JOIN tblLuHoldingCo AS HC
ON C.HoldingCoID = HC.HoldingCoID
OUTER APPLY (
SELECT
COUNT(*) AS FundedLastYear,
COUNT(CASE WHEN a.DateDetailDisposed < @StartThisYear AND a.StatusAppDetail = 'A' THEN ad.AppID END) AS ApprovedLastYear,
COUNT(CASE WHEN a.DateDetailDisposed < @StartThisYear AND a.StatusAppDetail = 'I' THEN ad.AppID END) AS ConditionedLastYear,
COUNT(CASE WHEN a.DateDetailDisposed < @StartThisYear AND a.StatusAppDetail = 'D' THEN ad.AppID END) AS DeclinedLastYear,
--... just follow this logic for the rest...
FROM
Applications a
LEFT JOIN AppDetails ad
ON a.AppID = ad.AppID
LEFT JOIN Contracts2 c2
ON a.AppID = c2.AppID
WHERE
C.ChannelID = a.SourceID
AND a.DateDetailDisposed >= @StartLastYear
) facd
WHERE
ActiveFlag = 'Y'
AND Relationship = 1
AND C.OldDealerID <> 1234
AND Channeltype = 1
OR ActiveFlag = 'Y'
AND C.ChannelID = CPP.ChannelID
AND C.ChannelID = CC.ChannelID
AND Relationship = 1
AND C.MarketRepID = MR.MarketRepID
AND C.OldDealerID <> 1234
AND Channeltype = 6
AND C.HoldingCoID = HC.HoldingCoID
ORDER BY
MR.Name,
C.MailingState,
C.NameLong
August 20, 2015 at 2:39 am
I like what Jason did. As soon as you start seeing correlated sub-queries like that, usually the best approach is either a JOIN or an APPLY.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 20, 2015 at 8:08 am
I apologize guys..I posted in the wrong section. I have R2 and it is giving me an error
'DATEFROMPARTS' is not a recognized built-in function name'
any other techniques to write the sub queries better in R2?
All the help highly appreciated.
August 20, 2015 at 8:14 am
mufadalhaiderster (8/20/2015)
I apologize guys..I posted in the wrong section. I have R2 and it is giving me an error'DATEFROMPARTS' is not a recognized built-in function name'
any other techniques to write the sub queries better in R2?
All the help highly appreciated.
Testing the subqueries doesn't require DATEFROMPARTS, just hardcode the dates until someone comes up with an alternative.
From BOL: "DATEFROMPARTS Applies To: SQL Server 2014, SQL Server 2016 Preview"
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 20, 2015 at 8:18 am
mufadalhaiderster (8/20/2015)
I apologize guys..I posted in the wrong section. I have R2 and it is giving me an error'DATEFROMPARTS' is not a recognized built-in function name'
any other techniques to write the sub queries better in R2?
All the help highly appreciated.
Solved:
DECLARE
@StartLastYear DATE,
@StartThisYear DATE
SELECT
@StartLastYear = DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE())-1,0),
@StartThisYear = DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0)
SELECT @StartLastYear, @StartThisYear
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 20, 2015 at 9:38 am
Thanks for all effort guys,
I am still getting an error message on the FROM, outer apply clause(Incorrect syntax near the keyword from)
OUTER APPLY (
SELECT
COUNT(*) AS FundedLastYear,
COUNT(CASE WHEN a.DateDetailDisposed < @StartThisYear AND a.StatusAppDetail = 'A' THEN ad.AppID END) AS ApprovedLastYear,
COUNT(CASE WHEN a.DateDetailDisposed < @StartThisYear AND a.StatusAppDetail = 'I' THEN ad.AppID END) AS ConditionedLastYear,
COUNT(CASE WHEN a.DateDetailDisposed < @StartThisYear AND a.StatusAppDetail = 'D' THEN ad.AppID END) AS DeclinedLastYear,
--... just follow this logic for the rest...
FROM
Applications a
LEFT JOIN AppDetails ad
ON a.AppID = ad.AppID
LEFT JOIN Contracts2 c2
ON a.AppID = c2.AppID
WHERE
C.ChannelID = a.SourceID
AND a.DateDetailDisposed >= @StartLastYear
) facd
August 20, 2015 at 9:43 am
mufadalhaiderster (8/20/2015)
Thanks for all effort guys,I am still getting an error message on the FROM, outer apply clause(Incorrect syntax near the keyword from)
OUTER APPLY (
SELECT
COUNT(*) AS FundedLastYear,
COUNT(CASE WHEN a.DateDetailDisposed < @StartThisYear AND a.StatusAppDetail = 'A' THEN ad.AppID END) AS ApprovedLastYear,
COUNT(CASE WHEN a.DateDetailDisposed < @StartThisYear AND a.StatusAppDetail = 'I' THEN ad.AppID END) AS ConditionedLastYear,
COUNT(CASE WHEN a.DateDetailDisposed < @StartThisYear AND a.StatusAppDetail = 'D' THEN ad.AppID END) AS DeclinedLastYear,
--... just follow this logic for the rest...
FROM
Applications a
LEFT JOIN AppDetails ad
ON a.AppID = ad.AppID
LEFT JOIN Contracts2 c2
ON a.AppID = c2.AppID
WHERE
C.ChannelID = a.SourceID
AND a.DateDetailDisposed >= @StartLastYear
) facd
It's the trailing comma. Remove it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 20, 2015 at 9:44 am
If that's the exact query you're trying to run, then you have a comma after the last expression you're selecting, so you'd want to remove that.
Cheers!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply