Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sins of SQL: The Time Table


Sins of SQL: The Time Table

Author
Message
Steven James Gray
Steven James Gray
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 99
Comments posted to this topic are about the item Sins of SQL: The Time Table
eshandeepak
eshandeepak
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 52
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?
Mark Fitzgerald-331224
Mark Fitzgerald-331224
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3694 Visits: 1513
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
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16469 Visits: 13207
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24233 Visits: 37978
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.

Cool
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)
246747
246747
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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."
wildh
wildh
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 675
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.



Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24233 Visits: 37978
Its lack of scalability is its biggest problem. A recursive CTE is still RBAR (a Modenism for Row By Agonizing Row).

Cool
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)
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10340 Visits: 11350
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
jvanderlouw-960980
jvanderlouw-960980
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 74
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
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