Correlated subquery problem

[kxramse]

I have this view that is HUGE with 4 select statements that are unioned. It takes forever to run, but what is worse is that there is a program that calls this view while referencing the same view in its own from statement, probably doubling its run time. Anyway, instead of re-inventing the wheel and re-writing things, I'm wondering about getting a correlated subquery to improve things. Here is the original view I'll only give the first of the four select statements since they are relatively the same and they don't appear to be my immediete problem:

SELECT     'True Adj' AS DataType, Reports.[Siteid], Reports.[ReportMonth], Reports.[ReportYear],
Reports.[AmountDue], Reports.[Received], Reports.[AmountReceived], 
                      CAST(Reports.DateTimeReceived AS datetime) AS DateTimeReceived,
Reports.[IncludeDiffNextBill], Reports.[Difference], ReportAdjustments.[IsGeneral],
ReportAdjustments.[IsCredit], 
                      substring(Sites.[Name], 6, len(Sites.[Name]) - 5) AS SitesName,
Sites.[UseDepartments], ReportPlans.[PlanTypeName], ReportAdjMonths.[Months], 
                      ReportCarriers.[Name] AS CarrierName, SiteGroups.[Name] AS SiteGroupsName,
ReportAdjBreaks.[Rate] AS AdjRate, PlansAvailable.[AnniversaryDate], 
                      PlansAvailable.[Status], Companies.[Name] AS CompanyName,
CommissionScales.[Name] AS ScaleName, Constructs.[Name] AS ConstructsName, 
                      GroupInfo.[ReqEffDate], GroupInfo.[TargetType], GroupInfo.[GroupNumber],
GroupInfo.[RenewDate], Brokerages.[Name] AS BrokeragesName, 
                      BasePlans.[Name] AS BasePlansName, Brokers.[AgentName], Brokers.[Client],
Brokers.[AgentNumber], Brokers.[TaxID], Brokers.[AddressOne], 
                      Brokers.[AddressTwo], Brokers.[City], Brokers.[State], Brokers.[Zip],
PlanTypes.[Name] AS PlanTypesName, 
                      PlanTypes.[ShortName] AS PlanTypesShortName, NULL as Rate
FROM         { oj (((((((((((((((([wolftracs].[dbo].[Reports] Reports INNER JOIN
                      [wolftracs].[dbo].[ReportAdjustments] ReportAdjustments ON Reports.[ID] =
ReportAdjustments.[ReportID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[Sites] Sites ON Reports.[SiteID] = Sites.[ID]) INNER JOIN
                      [wolftracs].[dbo].[ReportAdjMonths] ReportAdjMonths ON
ReportAdjustments.[ID] = ReportAdjMonths.[rAdjustmentID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[ReportCarriers] ReportCarriers ON
ReportAdjustments.[rCarrierID] = ReportCarriers.[ID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[SiteGroups] SiteGroups ON Sites.[SiteGroupID] =
SiteGroups.[ID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[ReportPlans] ReportPlans ON ReportAdjustments.[rPlanID] =
ReportPlans.[ID]) INNER JOIN
                      [wolftracs].[dbo].[ReportAdjBreaks] ReportAdjBreaks ON ReportAdjMonths.[ID]
= ReportAdjBreaks.[rAdjMonthID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[Companies] Companies ON SiteGroups.[CompanyID] =
Companies.[ID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[PlansAvailable] PlansAvailable ON ReportPlans.[AvailID] =
PlansAvailable.[ID]) INNER JOIN
                      [wolftracs].[dbo].[Users] Users ON Companies.[Brokers] = Users.[ID]) LEFT
OUTER JOIN
                      [wolftracs].[dbo].[Constructs] Constructs ON PlansAvailable.[ConstructID] =
Constructs.[ID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[GroupInfo] GroupInfo ON Companies.[ID] =
GroupInfo.[TargetID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[CommissionScales] CommissionScales ON
PlansAvailable.[CommissionScaleID] = CommissionScales.[ID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[BasePlans] BasePlans ON Constructs.[BasePlanID] =
BasePlans.[ID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[Brokerages] Brokerages ON Users.[BrokerageID] =
Brokerages.[ID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[PlanTypes] PlanTypes ON BasePlans.[PlanTypeID] =
PlanTypes.[ID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[Brokers] Brokers ON Brokerages.[BrokerageID] =
Brokers.[AgentNumber]}  
WHERE     Reports.[Received] = 1  and GroupInfo.[TargetType]='C'

What I want to do is get this select statement that is also a view and make it a correlated sub querry to that previous statement rather than have it call itself.

SELECT     CompanyName, GroupNumber, ScaleName, SitesName, 
SUM(CAST(Rate AS decimal(10, 2))) AS rate, 
SUM(CAST(AmountReceived AS decimal(10, 2))) AS received, 
SUM( 
(case WHEN iscredit = 1 THEN -1 else 1 END)
* months * CAST(AdjRate AS decimal(10, 2))) AS 'adjrate',ReportMonth,ReportYear
FROM         dbo.vComm_Combo 
GROUP BY CompanyName, GroupNumber, ScaleName, SitesName,ReportMonth,ReportYear

So what I did was try this, without some of the columns that are already showing up in the main query:

SELECT     'True Adj' AS DataType, Reports.[Siteid], Reports.[ReportMonth], Reports.[ReportYear],
Reports.[AmountDue], Reports.[Received], Reports.[AmountReceived], 
                      CAST(Reports.DateTimeReceived AS datetime) AS DateTimeReceived,
Reports.[IncludeDiffNextBill], Reports.[Difference], ReportAdjustments.[IsGeneral],
ReportAdjustments.[IsCredit], 
                      substring(Sites.[Name], 6, len(Sites.[Name]) - 5) AS SitesName,
Sites.[UseDepartments], ReportPlans.[PlanTypeName], ReportAdjMonths.[Months], 
                      ReportCarriers.[Name] AS CarrierName, SiteGroups.[Name] AS SiteGroupsName,
ReportAdjBreaks.[Rate] AS AdjRate, PlansAvailable.[AnniversaryDate], 
                      PlansAvailable.[Status], Companies.[Name] AS CompanyName,
CommissionScales.[Name] AS ScaleName, Constructs.[Name] AS ConstructsName, 
                      GroupInfo.[ReqEffDate], GroupInfo.[TargetType], GroupInfo.[GroupNumber],
GroupInfo.[RenewDate], Brokerages.[Name] AS BrokeragesName, 
                      BasePlans.[Name] AS BasePlansName, Brokers.[AgentName], Brokers.[Client],
Brokers.[AgentNumber], Brokers.[TaxID], Brokers.[AddressOne], 
                      Brokers.[AddressTwo], Brokers.[City], Brokers.[State], Brokers.[Zip],
PlanTypes.[Name] AS PlanTypesName, 
                      PlanTypes.[ShortName] AS PlanTypesShortName, NULL as Rate
FROM         { oj (((((((((((((((([wolftracs].[dbo].[Reports] Reports INNER JOIN
                      [wolftracs].[dbo].[ReportAdjustments] ReportAdjustments ON Reports.[ID] =
ReportAdjustments.[ReportID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[Sites] Sites ON Reports.[SiteID] = Sites.[ID]) INNER JOIN
                      [wolftracs].[dbo].[ReportAdjMonths] ReportAdjMonths ON
ReportAdjustments.[ID] = ReportAdjMonths.[rAdjustmentID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[ReportCarriers] ReportCarriers ON
ReportAdjustments.[rCarrierID] = ReportCarriers.[ID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[SiteGroups] SiteGroups ON Sites.[SiteGroupID] =
SiteGroups.[ID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[ReportPlans] ReportPlans ON ReportAdjustments.[rPlanID] =
ReportPlans.[ID]) INNER JOIN
                      [wolftracs].[dbo].[ReportAdjBreaks] ReportAdjBreaks ON ReportAdjMonths.[ID]
= ReportAdjBreaks.[rAdjMonthID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[Companies] Companies ON SiteGroups.[CompanyID] =
Companies.[ID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[PlansAvailable] PlansAvailable ON ReportPlans.[AvailID] =
PlansAvailable.[ID]) INNER JOIN
                      [wolftracs].[dbo].[Users] Users ON Companies.[Brokers] = Users.[ID]) LEFT
OUTER JOIN
                      [wolftracs].[dbo].[Constructs] Constructs ON PlansAvailable.[ConstructID] =
Constructs.[ID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[GroupInfo] GroupInfo ON Companies.[ID] =
GroupInfo.[TargetID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[CommissionScales] CommissionScales ON
PlansAvailable.[CommissionScaleID] = CommissionScales.[ID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[BasePlans] BasePlans ON Constructs.[BasePlanID] =
BasePlans.[ID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[Brokerages] Brokerages ON Users.[BrokerageID] =
Brokerages.[ID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[PlanTypes] PlanTypes ON BasePlans.[PlanTypeID] =
PlanTypes.[ID]) LEFT OUTER JOIN
                      [wolftracs].[dbo].[Brokers] Brokers ON Brokerages.[BrokerageID] =
Brokers.[AgentNumber]}  
WHERE     Reports.[Received] = 1  and GroupInfo.[TargetType]='C'; 
(
    SELECT      
            SUM(CAST(ReportAdjBreaks.Rate AS decimal(10, 2))) AS rate, 
            SUM(CAST(Reports.[AmountReceived] AS decimal(10, 2))) AS received, 
            SUM( 
                (case WHEN ReportAdjustments.iscredit = 1 THEN -1 else 1 END)
                * ReportadjMonths.months * CAST(ReportAdjBreaks.Rate AS decimal(10, 2))) AS 'adjrate'
    FROM         
        [wolftracs].[dbo].[Reports] Reports 
        LEFT OUTER JOIN [wolftracs].[dbo].[Sites] Sites ON Reports.[SiteID] = Sites.[ID]
        LEFT OUTER JOIN [wolftracs].[dbo].[SiteGroups] SiteGroups ON Sites.[SiteGroupID] =
SiteGroups.[ID] 
        LEFT OUTER JOIN [wolftracs].[dbo].[Companies] Companies ON SiteGroups.[CompanyID] =
Companies.[ID] 
        LEFT OUTER JOIN [wolftracs].[dbo].[GroupInfo] GroupInfo ON Companies.[ID] = GroupInfo.[TargetID]

        Inner join [wolftracs].[dbo].[ReportAdjustments] ReportAdjustments ON Reports.[ID] =
ReportAdjustments.[ReportID] 
        INNER JOIN [wolftracs].[dbo].[ReportAdjMonths] ReportAdjMonths ON ReportAdjustments.[ID] =
ReportAdjMonths.[rAdjustmentID] 
        INNER JOIN [wolftracs].[dbo].[ReportAdjBreaks] ReportAdjBreaks ON ReportAdjMonths.[ID] =
ReportAdjBreaks.[rAdjMonthID] 
        LEFT OUTER JOIN [wolftracs].[dbo].[ReportPlans] ReportPlans ON ReportAdjustments.[rPlanID] =
ReportPlans.[ID] 
        LEFT OUTER JOIN [wolftracs].[dbo].[PlansAvailable] PlansAvailable ON ReportPlans.[AvailID] =
PlansAvailable.[ID] 
        LEFT OUTER JOIN [wolftracs].[dbo].[CommissionScales] CommissionScales ON
PlansAvailable.[CommissionScaleID] = CommissionScales.[ID] 
    where        CompanyName=Companies.Name
            and GroupNumber=GroupInfo.GroupNumber
            and ScaleName=CommissionScales.Name
            and SitesName=Sites.Name
            and ReportMonth=Reports.ReportMonth
            and ReportYear=Reports.ReportYear
    GROUP BY Companies.Name, GroupInfo.GroupNumber, CommissionScales.Name, Sites.Name,
Reports.ReportMonth, Reports.ReportYear
)

But now I'm getting this:

MSG 207, level 16, state 1, line 48
Invalid Column Name 'Company Name'
MSG 207, level 16, state 1, line 50
Invalid Column Name 'ScaleName'
MSG 207, level 16, state 1, line 51
Invalid Column Name 'SitesName'

Any ideas how I can get through this quickly? Hopefully without writing a new stored proceedure.

Thanks,
Keith

 

[SQLSister]

Will the unions contain mutually exclusive data? If so try using Union all instead of Union. Union looks for and removes any duplicate rows which can be quite performance killing if you are reutrning large recordsets. Try it with union all and see if you get the smae number of records returned and how much faster it runs

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[kxramse]

I wasn't specific about the union because I wasn't thinking of examining them at this point. They use UNION ALL. They are different statements that have the same columns but go about getting their data differently. They do have mutually exclusive data.

I was hoping to focus on how to make this correlated sub-query work. Any ideas?

 

[kxramse]

Lets close this. I was way off trying to basically get 3 additional fields added to a long query without using a union while doing summations. I'm building a stored procedure.