order of execution

  • Can someone tell me what the order of execution would be in the underlined portion of my user's query? I believe it is running the CAST on each value before the COUNT, but I can't find any source information to back me up.

    SELECT convert(varchar,c.lastupdated,101)

    ,cast(count(b.defendantid) as float)/cast(count(a.defendantID) as float)

    FROM dbo.ActivityReceipt c(nolock)

    inner join [dbo].[Defendants] a(nolock) on(c.caseid = c.caseid)

    left join [dbo].[Matched] b(nolock) on (a.defendantid = b.defendantid)

    where c.lastupdated > getdate()-1

    Group by convert(varchar,c.lastupdated,101)

    FYI - I changed it to run as follows and it ran in seconds. Fundamentally the only thing that changed was removing the cast until I needed to do the computation.

    SELECT convert(varchar,c.lastupdated,101) date, 'case' as , count(a.defendantid) as counts

    into #a

    FROM dbo.ActivityReceipt c

    inner join [dbo].[Defendants] a on(c.caseid = a.caseid)

    left join [dbo].[Matched] b on (a.defendantid = b.defendantid)

    where c.lastupdated >getdate()-1

    group by convert(varchar,c.lastupdated,101)

    SELECT convert(varchar,c.lastupdated,101) date, 'matched' as , count(b.defendantid) as counts

    into #b

    FROM dbo.ActivityReceipt c

    inner join [dbo].[Defendants] a on(c.caseid = a.caseid)

    left join [dbo].[Matched] b on (a.defendantid = b.defendantid)

    where c.lastupdated >getdate()-1

    group by convert(varchar,c.lastupdated,101)

    select a.date, cast(b.counts as float)/cast(a.counts as float) as [Verification Rate]

    from #a a

    join #b b on a.date =b.date

    drop table #a

    drop table #b

    THANKS!!!

    DEB

    😎

  • Regarding order of CAST ( COUNT() ) , setting showplan_text on shows that the COUNT is performed before the CAST.

    StmtText

    SELECT CAST( count(type) as float) / CAST( count(number ) as float)

    FROM [master].[dbo].[spt_values]

    StmtText

    |--Compute Scalar(DEFINE:([Expr1005]=CONVERT(float(53),[Expr1003],0)/CONVERT(float(53),[Expr1004],0)))

    |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1006],0), [Expr1004]=CONVERT_IMPLICIT(int,[Expr1006],0)))

    |--Stream Aggregate(DEFINE:([Expr1006]=Count(*)))

    |--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]))

    SQL = Scarcely Qualifies as a Language

  • I would think it is doing the count before the cast. Since it's just counting the number of non-null values, it doesn't really need to know what kind of data is in there, nor convert it to anything, just know whether it is null or not. Take a look at the query plan. I set up a similar query and saw a Stream Aggregate (the count) then two Compute Scalars. Although I couldn't tell the two Compute Scalars apart, I assume the cast was first and the division second. Either way the aggregate was before both of them.

    I'll bet if you cast the counts as floats in your second queries, they still run just as fast. The time difference is probably due to writing the query differently, not to having the cast done later.

    Good Luck!

    Chad

  • good information guys, thanks!

    😎

  • Re: your performance difference, you have a tautology in the first SQL Statment. "c.caseid = c.caseid"

    SELECT convert(varchar,c.lastupdated,101)

    ,cast(count(b.defendantid) as float)/cast(count(a.defendantID) as float)

    FROM dbo.ActivityReceipt c(nolock)

    inner join [dbo].[Defendants] a(nolock) on(c.caseid = c.caseid)

    left join [dbo].[Matched] b(nolock) on (a.defendantid = b.defendantid)

    where c.lastupdated > getdate()-1

    Group by convert(varchar,c.lastupdated,101)

    Suggest you do not use a one character alias for table names but instead use the full table names without any abbreviations - cut and paste is easy but reading SQL with one character aliases is impossible. "Replace all" is a method to make the change but sure to check "match whole word".

    Joe Cellko recommends that alias names followin the same standards as tables names.

    SQL = Scarcely Qualifies as a Language

  • Get the execution plan and it will tell you exactly what's occurring when. That's what they do.

    "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

  • thanks for the information again.

    Carl - the "tautology" was a typo. Even though it was corrected, the original query ran in excess of an hour.

    😎

  • When there is a function used in the select and the grouping, I have seen cases where the optimizer is confused if everything is not typed identically - do not know why any extra whitespaces or case would make a diffference, but the below SQL avoids that problem.

    selectActivityReceiptDate

    , cast( count( Matched_defendantid ) AS float)

    / cast( count( Defendants_DefendantId ) AS float)

    AS MatchedDefendants_Rate

    FROM(SELECT convert( varchar,ActivityReceipt.lastupdated,101) AS ActivityReceiptDate

    ,Matched.defendantidAS Matched_defendantid

    ,Defendants.defendantID AS Defendants_DefendantId

    FROM dbo.ActivityReceiptWITH (nolock)

    join dbo.Defendants WITH (nolock)

    on ActivityReceipt.caseid = Defendants.caseid

    left outer join

    dbo.Matched WITH (nolock)

    on Defendants.defendantid = Matched.defendantid

    where ActivityReceipt.lastupdated > current_timestamp -1

    )as MatchedDefendants

    Group by ActivityReceiptDate

    SQL = Scarcely Qualifies as a Language

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply