May 29, 2008 at 4:23 pm
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
😎
May 29, 2008 at 4:51 pm
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
May 29, 2008 at 4:53 pm
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
May 29, 2008 at 4:59 pm
good information guys, thanks!
😎
May 29, 2008 at 5:00 pm
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
May 30, 2008 at 5:52 am
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
May 30, 2008 at 7:51 am
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.
😎
May 30, 2008 at 11:20 am
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