SQL Sub-query run's very fast individually, but when used in select is very slow

Question

I have a set of tables which i am joining to create a record set and then run a additional select on the set to retrieve some records. The query in the code is being used in a view as a CTE.

While checking the sub-query only it runs extremely faster like 0.01 - 0.02 secs. same applies for if a temp table is used to retrieve the records. As I am planning to use it inside a view the temp table solution is Out of scope. The regular query runs from 37-50 mins instead.

SELECT 
 CallDate
,MediaChannel
,SubCategory
,Vendor
,BusinessVertical
,SUM(NumberOfLeads) AS NumberOfLeads
,CASE 
    WHEN SUM([CostPerLead]) <> 0
        THEN SUM([CostPerLead])
    ELSE NULL
    END AS Cost
,[CostPerLead]
,SourceName
,ParentLeadSource
,IsBillable
,dvce_type

FROM (
SELECT [PhoneLabel]
    ,[DialogTechCallId] = cd.DialogTechCallId
    ,[LeadId] = c.LeadId
    ,[CostPerLead] = CAST(cpl.cost AS INT)
    ,[SourceName] = bt.LeadCompany
    ,[ParentLeadSource] = ftlc.fruit
    ,[DialogTechPhoneNumber] = cd.CalledNumber
    ,[CallDate] = CAST(cd.[CallDateTime] AS DATE)
    ,[CallType] = cd.CallType
    ,[TalkTime] = cd.[TalkTimeMinutes]
    ,[TalkTimeSeconds] = CASE 
        WHEN ISNUMERIC(cd.[TalkTimeMinutes]) = 1
            THEN CAST(cd.[TalkTimeMinutes] AS DECIMAL(10, 2)) * 60
        ELSE 0
        END
    ,[TimeToQualify] = bt.Billabletime
    ,[IsBillable] = CASE 
        WHEN ISNUMERIC(cd.[TalkTimeMinutes]) = 1
            THEN CASE 
                    WHEN CAST(cd.[TalkTimeMinutes] AS DECIMAL(10, 2)) * 60 
    >= CAST(bt.Billabletime AS INT)
                        THEN 1
                    WHEN bt.Billabletime = 900
                        THEN 1
                    ELSE 0
                    END
        ELSE 0
        END
    ,[MediaChannel] = ftlc.channel2
    ,[SubCategory] = ftlc.sub
    ,[Vendor] = ftlc.vendor
    ,[BusinessVertical] = ftlc.business_vertical
    ,[NumberOfLeads] = 1
    ,[dvce_type] = ftlc.dvce_type

    FROM [dbo].[Abc] d WITH (NOLOCK)

    LEFT JOIN [dt].[cde] cd WITH (NOLOCK) ON d.FullDate = 
    CAST(cd.CallDateTime AS DATE)

    LEFT JOIN [dt].[efg] c WITH (NOLOCK) ON cd.DialogtechCallId = 
    c.DialogTechCallid

    INNER JOIN [dt].[hij] m WITH (NOLOCK) ON cd.CallerId = 
    m.DialogTechPhoneNumber

    INNER JOIN [dt].[klm] bt WITH (NOLOCK) ON m.LeadSourceInfoId = 
    bt.LeadSourceId AND cd.[CallDateTime] BETWEEN bt.[StartDateTime]
    AND ISNULL(bt.[EndDateTime], GETDATE())

    INNER JOIN [dbo].[jkl] ftlc WITH (NOLOCK) ON bt.ParentLeadSource = 
    ftlc.fruit

    INNER JOIN dbo.xyz cpl WITH (NOLOCK) ON ftlc.lead_company =        
    cpl.lead_company AND cd.[CallDateTime] >= cpl.[start_date]
    AND cd.[CallDateTime] <= ISNULL(cpl.[end_date], GETDATE())

    WHERE CAST(cd.[CallDateTime] AS DATE) >= '2018-08-01'
    AND CASE WHEN ISNUMERIC(cd.[TalkTimeMinutes]) = 1
            THEN CASE 
                    WHEN CAST(cd.[TalkTimeMinutes] AS DECIMAL(10, 2)) * 60 
    >= CAST(bt.Billabletime AS INT)
                        THEN 1
                    WHEN bt.Billabletime = 900
                        THEN 1
                    ELSE 0
                    END
        ELSE 0
        END = 1
   ) sub

 GROUP BY 
 CallDate
,MediaChannel
,SubCategory
,Vendor
,BusinessVertical
,SourceName
,ParentLeadSource
,IsBillable
,dvce_type
,CostPerLead;

----The query is being used in view so need to tackle the sub query issue and reduce the running time.

Answer 1

You have some data in your subquery that is not used in your outer query. Some of your joins could also introduce joining NULL values to NULL values, which will kill your performance.

Without knowing your table structures or functions, nor any of the actual data, I came up with the following simplified version of your query. It removes unused columns from the subquery, a subsequently-useless join, simplified CASE statements, and the removal of CostPerLead from the main query, since you're aggregating this.

SELECT
    CallDate
    ,MediaChannel
    ,SubCategory
    ,Vendor
    ,BusinessVertical
    ,NumberOfLeads   = COUNT(*)
    ,Cost            = NULLIF(SUM(CostPerLead), 0)
    ,SourceName
    ,ParentLeadSource
    ,IsBillable
    ,dvce_type
FROM
(



    SELECT
        CallDate            = CAST(cd.CallDateTime AS DATE)
        ,MediaChannel       = ftlc.channel2
        ,SubCategory        = ftlc.sub
        ,Vendor             = ftlc.vendor
        ,BusinessVertical   = ftlc.business_vertical
        ,CostPerLead        = CAST(cpl.cost AS INT)
        ,SourceName         = bt.LeadCompany
        ,ParentLeadSource   = ftlc.fruit
        ,IsBillable         = CASE WHEN ISNUMERIC(cd.TalkTimeMinutes) = 1
                                        AND (
                                                CAST(cd.TalkTimeMinutes AS DECIMAL(10, 2)) * 60 >= CAST(bt.Billabletime AS INT)
                                                OR
                                                bt.Billabletime = 900
                                            )
                                    THEN 1
                                    ELSE 0
                                 END
        ,dvce_type          = ftlc.dvce_type    
    FROM dbo.Abc       d WITH (NOLOCK)
    INNER JOIN dt.cde cd WITH (NOLOCK) ON d.FullDate = CAST(cd.CallDateTime AS DATE)
    INNER JOIN dt.hij  m WITH (NOLOCK) ON cd.CallerId = m.DialogTechPhoneNumber
    INNER JOIN dt.klm  bt WITH (NOLOCK) ON m.LeadSourceInfoId = bt.LeadSourceId
                                            AND cd.CallDateTime BETWEEN bt.StartDateTime AND ISNULL(bt.EndDateTime, GETDATE())
    INNER JOIN dbo.jkl ftlc WITH (NOLOCK) ON bt.ParentLeadSource = ftlc.fruit
    INNER JOIN dbo.xyz cpl WITH (NOLOCK) ON ftlc.lead_company = cpl.lead_company
                                             AND cd.CallDateTime BETWEEN cpl.start_date AND ISNULL(cpl.end_date, GETDATE())
    WHERE
        d.FullDate >= '2018-08-01'
        AND (
                ISNUMERIC(cd.TalkTimeMinutes) = 1 
                AND (
                        CAST(cd.TalkTimeMinutes AS DECIMAL(10, 2)) * 60 >= CAST(bt.Billabletime AS INT)
                        OR
                        bt.Billabletime = 900
                    )
            )
) sub
GROUP BY
    CallDate
    ,MediaChannel
    ,SubCategory
    ,Vendor
    ,BusinessVertical
    ,SourceName
    ,ParentLeadSource
    ,IsBillable
    ,dvce_type;