Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Returning 0's in a SUM (SQL Spackle) Expand / Collapse
Author
Message
Posted Tuesday, June 14, 2011 1:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 15, 2013 3:14 AM
Points: 5, Visits: 94
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
Post #1124773
Posted Tuesday, June 14, 2011 1:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 15, 2013 3:14 AM
Points: 5, Visits: 94
Jeff, I must tell you that your solution is briliant as well!
Post #1124774
Posted Tuesday, June 14, 2011 3:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 3:25 AM
Points: 2, Visits: 113
What's this COALESCE(SUM(SalesTestData.SaleQty),0) ?

You do not know ISNULL ?
ISNULL(SUM(SalesTestData.SaleQty),0)
Post #1124833
Posted Tuesday, June 14, 2011 7:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 12:33 PM
Points: 392, Visits: 820
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? */
Post #1124981
Posted Tuesday, June 14, 2011 10:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 35,769, Visits: 32,438
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. )

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


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1125150
Posted Tuesday, June 14, 2011 10:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 35,769, Visits: 32,438
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1125156
Posted Tuesday, June 14, 2011 10:18 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 12:33 PM
Points: 392, Visits: 820
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? */
Post #1125159
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse