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 12345»»»

Sins of SQL: The Time Table Expand / Collapse
Author
Message
Posted Monday, May 31, 2010 9:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 4, 2011 11:38 AM
Points: 53, Visits: 99
Comments posted to this topic are about the item Sins of SQL: The Time Table
Post #930511
Posted Tuesday, June 1, 2010 12:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:34 AM
Points: 2, Visits: 38
When I executed this statement

SELECT * FROM [MyDB].[dbo].[fn_GetTimestampRange] (DateAdd(YY,-10,GETDATE()), GETDATE(), 1)

it returned 8726399 records in 2.29 mins and then gave error as

"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

Any idea why this error cropped up?
Post #930537
Posted Tuesday, June 1, 2010 12:18 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 3:15 PM
Points: 3,166, Visits: 1,378
Just a brief note.

This would be ok until you look at the "special" cases we find everyday with clients such as :
- wanting weekday/nonworking day (including national holidays) per country,
- end of month days being decided by the company not actual calendar,
- requiring more than 100 days (maxrecursion option accepted),
- being able to change the information for a day easily using values not code,

On the maintainance side not really a problem if you design the cube with 2 dimension tables (date and time). In one table all dates, in the other all times.

Fitz
Post #930538
Posted Tuesday, June 1, 2010 12:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 13,295, Visits: 11,086
If you use a "logical" surrogate key in the fact table, for example 20100601, you can still have the time table AND fast searching and simple SQL queries against the fact table. (This method also has its drawbacks, but it works for me)

Furthermore, in some companies, the time table contains much more than just the time, but also holiday information, fiscal information et cetera.

So, I'm not convinced to drop my time tables




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #930539
Posted Tuesday, June 1, 2010 1:05 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:09 PM
Points: 20,739, Visits: 32,528
Using a recursive CTE to generate what is basically a dynamic tally table except returning a dynamic calendar/time table is not scalable.

For an article on dynamic tally tables, please read this one: The Dynamic Tally or Numbers Table.

You can easily modify it to meet your needs as detailed in the article, and it will be much more scalable as it won't be using the RBAR approach inherent in recursive CTE's.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #930547
Posted Tuesday, June 1, 2010 1:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 21, 2010 12:25 PM
Points: 3, Visits: 51
eshandeepak (6/1/2010)
When I executed this statement

SELECT * FROM [MyDB].[dbo].[fn_GetTimestampRange] (DateAdd(YY,-10,GETDATE()), GETDATE(), 1)

it returned 8726399 records in 2.29 mins and then gave error as

"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

Any idea why this error cropped up?


It does say in the article: "(Note that if you want to produce more than 100 distinct days in a single query, you'll need to append OPTION(MAXRECURSION numberOfDays) to your query, where numberOfDays is the number of days you wish to loop through. This can be hidden away in the stored procedures, and SQL Server allows you to specify a MAXRECURSION of 0, which does not limit the amount of days that can be processed."
Post #930548
Posted Tuesday, June 1, 2010 1:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:38 AM
Points: 292, Visits: 662
Like others here I'd need a bit more to convince me to remove my table or use this as an alternative. Like most, I also us the time table to build a time dimension and provide multiple hierarchies to users 12 hour view, 24 hour view, short time descriptions long time descriptions ect without further work on coding in descriptions this wouldn't meet all my needs. This seems like a good option for using in a data retrieval query if you dont already have a time table and your resultset was small to medium, but as a replacement, not for me.


Post #930553
Posted Tuesday, June 1, 2010 1:17 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:09 PM
Points: 20,739, Visits: 32,528
Its lack of scalability is its biggest problem. A recursive CTE is still RBAR (a Modenism for Row By Agonizing Row).



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #930556
Posted Tuesday, June 1, 2010 1:24 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
da-zero (6/1/2010)
If you use a "logical" surrogate key in the fact table, for example 20100601, you can still have the time table AND fast searching and simple SQL queries against the fact table. (This method also has its drawbacks, but it works for me)

And me

So, I'm not convinced to drop my time tables


The SQL Server team added all sorts of cool optimisations to the database engine for star-schema processing.
Do I want to defeat that by replacing a small, efficient table with a recursive CTE? Not really!

One star.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #930559
Posted Tuesday, June 1, 2010 2:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 2, 2014 3:25 AM
Points: 12, Visits: 73
Some remarks:
1) Dimensional modelling means denormalized dimensions and normalized facts, so the argument of duplicate data is not valid
2) Treat Date and Time as different dimensions. For the Date dimension (day up to year plus any extra values based upon them like holidays etc) you have a Date_key (no lookup needed when a date is included in your facts). For the Time dimension (for example seconds up to hour plus any extra values based upon them such as specific time frames) there is a Time_key which also does not require a lookup. Based upon a time (for instance a timestamp) in your fact, you can calculate a unique key on forehand (14:32:21 results in 14 x 3600 plus 32 x 60 plus 21 = 52341) in your facttable. The Time dimension can be precalculated based on the same method (results in 86400 rows = seconds in a day)

Since dimensions are in principle used for analysis the specific second of a time dimension is usually not interesting, however one wants to discover predictive patterns based on intra-day values. If one needs to know what happened the last 45 minutes (how many transactions for some specific call option for some stock with an exercise price of X and expiration of Y) one uses the timestamp of the transaction which is included in the fact table.
3) Using stored procedures can perhaps be necessary (calculate a value based on spot prices in the stock market during a day), however since dimensional modelling involves ETL and a datamodel, one needs to avoid as much as possible complexity on the query side. Introducing stored procedures there as well does decrease the maintainability
4) Query performance is only an issue when you query on the fly. Using a cube you can pre-calculate the values, so this should not be an issue in the first place
Post #930571
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse