Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

Author
 Message
 Posted Sunday, February 16, 2014 9:38 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, August 5, 2016 1:52 AM Points: 109, Visits: 434
 Hello Dwayne,I tried the script but it just keeps on running.Here is the actual script:WITH Tally (n) AS( SELECT TOP ((SELECT 1+CAST(4*MAX([hours]) AS INT) FROM TOTALS where PERSON = '1010' and DATE between '02/05/2014' and '02/11/2014')) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)SELECT PERSON, DATE, [hours]=DATEADD(minute, 15*n, CAST('00:00' AS TIME))FROM TOTALS aCROSS APPLY( SELECT n FROM Tally WHERE n <= 4.*[hours]) bORDER BY PERSON, [DATE], n;
Post #1541983
 Posted Sunday, February 16, 2014 10:36 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 You might try putting the WHERE clause before ORDER BY instead of where you have it (the subquery that determines the TOP n rows in your Tally table). My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1541990
 Posted Monday, February 17, 2014 12:42 AM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 2:41 PM Points: 42,081, Visits: 39,473
 zulfansari (2/16/2014)Hi Jeff,Actually it's 0.25,0.50, etc. format, I made up the data for the post and used the wrong format.Thank you,Then a Tally Table will make the solution to this problem incredibly easy. I left the "Hours" mix in the output just so you can verify the output. You can remove that column whenever you're ready.`--===== Build a test table and populate it on-the-fly. -- THIS IS NOT A PART OF THE SOLUTION!!! -- WE JUST NEEDED SOMETHING TO DEMO THE SOLUTION WITH!!! SET DATEFORMAT MDY; SELECT d.Person ,[Date] = CAST(d.[Date] AS DATETIME) ,d.Hours INTO #TestTable FROM ( SELECT 101,'02/01/2014', 1.00 UNION ALL SELECT 101,'02/02/2014', 1.25 UNION ALL SELECT 101,'02/03/2014',11.75 )d(Person,[Date],Hours);--===== This is the solution made incredibly easy -- by the use of a Tally Table. SELECT Person,[Date],Hours,IntervalHours = t.N*.25 FROM #TestTable CROSS JOIN dbo.Tally t WHERE t.N <= Hours*4 ORDER BY Person, Date, IntervalHours;`Here's the output from the code above. Like I said, you can simply remove the "Hours" column if you don't really want it.`Person Date Hours IntervalHours----------- ----------------------- ----- -------------101 2014-02-01 00:00:00.000 1.00 0.25101 2014-02-01 00:00:00.000 1.00 0.50101 2014-02-01 00:00:00.000 1.00 0.75101 2014-02-01 00:00:00.000 1.00 1.00101 2014-02-02 00:00:00.000 1.25 0.25101 2014-02-02 00:00:00.000 1.25 0.50101 2014-02-02 00:00:00.000 1.25 0.75101 2014-02-02 00:00:00.000 1.25 1.00101 2014-02-02 00:00:00.000 1.25 1.25101 2014-02-03 00:00:00.000 11.75 0.25101 2014-02-03 00:00:00.000 11.75 0.50101 2014-02-03 00:00:00.000 11.75 0.75101 2014-02-03 00:00:00.000 11.75 1.00101 2014-02-03 00:00:00.000 11.75 1.25101 2014-02-03 00:00:00.000 11.75 1.50101 2014-02-03 00:00:00.000 11.75 1.75101 2014-02-03 00:00:00.000 11.75 2.00101 2014-02-03 00:00:00.000 11.75 2.25101 2014-02-03 00:00:00.000 11.75 2.50101 2014-02-03 00:00:00.000 11.75 2.75101 2014-02-03 00:00:00.000 11.75 3.00101 2014-02-03 00:00:00.000 11.75 3.25101 2014-02-03 00:00:00.000 11.75 3.50101 2014-02-03 00:00:00.000 11.75 3.75101 2014-02-03 00:00:00.000 11.75 4.00101 2014-02-03 00:00:00.000 11.75 4.25101 2014-02-03 00:00:00.000 11.75 4.50101 2014-02-03 00:00:00.000 11.75 4.75101 2014-02-03 00:00:00.000 11.75 5.00101 2014-02-03 00:00:00.000 11.75 5.25101 2014-02-03 00:00:00.000 11.75 5.50101 2014-02-03 00:00:00.000 11.75 5.75101 2014-02-03 00:00:00.000 11.75 6.00101 2014-02-03 00:00:00.000 11.75 6.25101 2014-02-03 00:00:00.000 11.75 6.50101 2014-02-03 00:00:00.000 11.75 6.75101 2014-02-03 00:00:00.000 11.75 7.00101 2014-02-03 00:00:00.000 11.75 7.25101 2014-02-03 00:00:00.000 11.75 7.50101 2014-02-03 00:00:00.000 11.75 7.75101 2014-02-03 00:00:00.000 11.75 8.00101 2014-02-03 00:00:00.000 11.75 8.25101 2014-02-03 00:00:00.000 11.75 8.50101 2014-02-03 00:00:00.000 11.75 8.75101 2014-02-03 00:00:00.000 11.75 9.00101 2014-02-03 00:00:00.000 11.75 9.25101 2014-02-03 00:00:00.000 11.75 9.50101 2014-02-03 00:00:00.000 11.75 9.75101 2014-02-03 00:00:00.000 11.75 10.00101 2014-02-03 00:00:00.000 11.75 10.25101 2014-02-03 00:00:00.000 11.75 10.50101 2014-02-03 00:00:00.000 11.75 10.75101 2014-02-03 00:00:00.000 11.75 11.00101 2014-02-03 00:00:00.000 11.75 11.25101 2014-02-03 00:00:00.000 11.75 11.50101 2014-02-03 00:00:00.000 11.75 11.75`As previously posted, here's where you can learn more about the Tally Table, how to build it, and how it works to avoid loops and cursors.http://www.sqlservercentral.com/articles/T-SQL/62867/Once you've mastered using a physical Tally Table, then you can get into what Jason and Dwain where showing... on the fly creation of Tally-Table-like structures, which is also introduced in the article I provided a link to.The reason why I don't just cough up the very simple method of building a Tally Table is because I really want you to understand how the Tally Table works. My personal belief is that it will improve your career as someone using T-SQL as it has done for nearly everyone that's ever used such a thing. It changes your mind from thinking in rows to thinking in columns and that's the main paradigm shift required to write some really nasty fast code in T-SQL. --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." Helpful Links:How to post code problemsHow to post performance problems
Post #1541997
 Posted Monday, February 17, 2014 5:47 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, August 5, 2016 1:52 AM Points: 109, Visits: 434
 Hi Jeff, Dwain.c and SQLRNNR,You guys are AWESOME..I really appreciate your help and learned new tips and tricks from all of you..God bless you all!Just an FYI, I ended up creating a Tally table and used the last Script provided by Jeff..Best Regards,
Post #1542319
 Posted Monday, February 17, 2014 6:23 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 2:52 PM Points: 20,083, Visits: 18,257
 zulfansari (2/17/2014)Hi Jeff, Dwain.c and SQLRNNR,You guys are AWESOME..I really appreciate your help and learned new tips and tricks from all of you..God bless you all!Just an FYI, I ended up creating a Tally table and used the last Script provided by Jeff..Best Regards,Good to hear. FWIW, the Numbers table in the script I shared and the Tally table in Jeff's script are the same thing - just different names. Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL Server, MVPSQL RNNRPosting Performance Based Questions - Gail Shaw
Post #1542325
 Posted Monday, February 17, 2014 8:27 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, August 5, 2016 1:52 AM Points: 109, Visits: 434
 Thank you SQLRNNR.By the way, what is FWIW?
Post #1542340
 Posted Monday, February 17, 2014 8:30 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 2:52 PM Points: 20,083, Visits: 18,257
 FWIW = "For What it's Worth" Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL Server, MVPSQL RNNRPosting Performance Based Questions - Gail Shaw
Post #1542342
 Posted Monday, February 17, 2014 8:40 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 2:41 PM Points: 42,081, Visits: 39,473
 zulfansari (2/17/2014)Hi Jeff, Dwain.c and SQLRNNR,You guys are AWESOME..I really appreciate your help and learned new tips and tricks from all of you..God bless you all!Just an FYI, I ended up creating a Tally table and used the last Script provided by Jeff..Best Regards,Thanks for the great feedback. As Red Green would say, "We're all in this together and I'm pullin' for ya."I need to double-check, though. You made sure to include the clustered index on the Tally Table, right? It's a critical piece of the performance. It'll run relatively dog slow without it.Also, as Jason (SQLRNNR) said, his "Numbers" table is the same as what I call a "Tally" Table. Dwain used the classic method of using a "pseudo-cursor" (use the presence of rows to act as a "loop") to build his on the fly. The article I pointed you to also contains a "cCTE" (Cascading CTE, not to be confused with a "Recursive" CTE which is horribly slow) method by Itzik Ben-Gan which is a tiny bit slower than a physical Tally Table but produces exactly zero reads.Both Dwain and Jason used CROSS APPLY while I used a CROSS JOIN. All of that just shows the flexibility of the tool. It can be used for a whole lot more in a whole lot of different ways.Hat's off to both Dwain and Jason for pumping out the code. --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." Helpful Links:How to post code problemsHow to post performance problems
Post #1542346
 Posted Monday, February 17, 2014 9:44 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 Jeff Moden (2/17/2014)zulfansari (2/17/2014)Hi Jeff, Dwain.c and SQLRNNR,You guys are AWESOME..I really appreciate your help and learned new tips and tricks from all of you..God bless you all!Just an FYI, I ended up creating a Tally table and used the last Script provided by Jeff..Best Regards,Thanks for the great feedback. As Red Green would say, "We're all in this together and I'm pullin' for ya."I need to double-check, though. You made sure to include the clustered index on the Tally Table, right? It's a critical piece of the performance. It'll run relatively dog slow without it.Also, as Jason (SQLRNNR) said, his "Numbers" table is the same as what I call a "Tally" Table. Dwain used the classic method of using a "pseudo-cursor" (use the presence of rows to act as a "loop") to build his on the fly. The article I pointed you to also contains a "cCTE" (Cascading CTE, not to be confused with a "Recursive" CTE which is horribly slow) method by Itzik Ben-Gan which is a tiny bit slower than a physical Tally Table but produces exactly zero reads.Both Dwain and Jason used CROSS APPLY while I used a CROSS JOIN. All of that just shows the flexibility of the tool. It can be used for a whole lot more in a whole lot of different ways.Hat's off to both Dwain and Jason for pumping out the code.Who is Red Green? Any relation to Opal Azure? My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1542355
 Posted Monday, February 17, 2014 10:16 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 2:41 PM Points: 42,081, Visits: 39,473
 dwain.c (2/17/2014)Jeff Moden (2/17/2014)zulfansari (2/17/2014)Hi Jeff, Dwain.c and SQLRNNR,You guys are AWESOME..I really appreciate your help and learned new tips and tricks from all of you..God bless you all!Just an FYI, I ended up creating a Tally table and used the last Script provided by Jeff..Best Regards,Thanks for the great feedback. As Red Green would say, "We're all in this together and I'm pullin' for ya."I need to double-check, though. You made sure to include the clustered index on the Tally Table, right? It's a critical piece of the performance. It'll run relatively dog slow without it.Also, as Jason (SQLRNNR) said, his "Numbers" table is the same as what I call a "Tally" Table. Dwain used the classic method of using a "pseudo-cursor" (use the presence of rows to act as a "loop") to build his on the fly. The article I pointed you to also contains a "cCTE" (Cascading CTE, not to be confused with a "Recursive" CTE which is horribly slow) method by Itzik Ben-Gan which is a tiny bit slower than a physical Tally Table but produces exactly zero reads.Both Dwain and Jason used CROSS APPLY while I used a CROSS JOIN. All of that just shows the flexibility of the tool. It can be used for a whole lot more in a whole lot of different ways.Hat's off to both Dwain and Jason for pumping out the code.Who is Red Green? Any relation to Opal Azure?I didn't get to see it very often (I never did watch TV much), but I laughed my hinny off when I did get to see it.http://en.wikipedia.org/wiki/The_Red_Green_Show --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." Helpful Links:How to post code problemsHow to post performance problems
Post #1542363

 Permissions