SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Returning 0's in a SUM (SQL Spackle)


Returning 0's in a SUM (SQL Spackle)

Author
Message
berzat.museski
berzat.museski
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 102
Jeff, thank you for your effort measuring the two methods!
It is true, my solution is a way slower as it is now. That is so because there is no index over the computed coumn in the example (how can it be SARGable without any indexes).
After adding it, your query was only twice faster, which is ok I guess having the code optimized as it is :-)
berzat.museski
berzat.museski
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 102
Jeff, I must tell you that your solution is briliant as well!
Alexey Voronin
Alexey Voronin
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 227
What's this COALESCE(SUM(SalesTestData.SaleQty),0) ?

You do not know ISNULL ?
ISNULL(SUM(SalesTestData.SaleQty),0)
Gatekeeper
Gatekeeper
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 888
Alexey Voronin (6/14/2011)
What's this COALESCE(SUM(SalesTestData.SaleQty),0) ?

You do not know ISNULL ?
ISNULL(SUM(SalesTestData.SaleQty),0)


They are interchangeable. Both are the same thing except COALESCE() allows for more than one evaluation.

/* Anything is possible but is it worth it? */
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221122 Visits: 42002
Gatekeeper (6/14/2011)
Alexey Voronin (6/14/2011)
What's this COALESCE(SUM(SalesTestData.SaleQty),0) ?

You do not know ISNULL ?
ISNULL(SUM(SalesTestData.SaleQty),0)


They are interchangeable. Both are the same thing except COALESCE() allows for more than one evaluation.


Actually, they're not interchangeable at the semantic level. COALESCE is slower than ISNULL (although it takes a substantial number of rows to tell). COALESCE is in the ANSI standard where ISNULL is proprietary. COALESCE can change the datatype of the result based on which non-null element is selected (which can cause a major performance problem, BTW). ISNULL will keep the datatype of the return the same as the datatype of the first operand. COALESCE takes more keystrokes to type than ISNULL (BIG selling point for me. :-P)

Heh... other than that, they're interchangeable when two operands are necessary. :-D

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221122 Visits: 42002
berzat.museski (6/14/2011)
Jeff, I must tell you that your solution is briliant as well!


Thanks for the compliment but, nah... I just used a variation of what is in the article. :-) That was the whole point, really. The code in the article is some of the fastest code you can find to do this and you don't need a custom column (frequently disallowed on real tables) nor an extra index to support it.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Gatekeeper
Gatekeeper
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 888
Jeff Moden (6/14/2011)
COALESCE can change the datatype of the result based on which non-null element is selected (which can cause a major performance problem, BTW). ISNULL will keep the datatype of the return the same as the datatype of the first operand


I've never run into that but it's good to learn something new! Thanks for the clarification.

/* Anything is possible but is it worth it? */
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search