<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Steve Gray
»
Sins of SQL: The Time Table
84 posts, Page 1 of 9
1
2
3
4
5
»
»»
Sins of SQL: The Time Table
Rate Topic
Display Mode
Topic Options
Author
Message
Steven James Gray
Steven James Gray
Posted Monday, May 31, 2010 9:24 PM
Valued Member
Group: General Forum Members
Last Login: Friday, March 04, 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
eshandeepak
eshandeepak
Posted Tuesday, June 01, 2010 12:16 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, February 14, 2013 3:08 AM
Points: 2,
Visits: 25
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
Mark Fitzgerald-331224
Mark Fitzgerald-331224
Posted Tuesday, June 01, 2010 12:18 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 5:37 AM
Points: 2,714,
Visits: 971
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
Koen Verbeeck
Koen Verbeeck
Posted Tuesday, June 01, 2010 12:26 AM
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 2:11 AM
Points: 9,378,
Visits: 6,473
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
Lynn Pettis
Lynn Pettis
Posted Tuesday, June 01, 2010 1:05 AM
SSC-Insane
Group: General Forum Members
Last Login: Yesterday @ 11:52 PM
Points: 21,635,
Visits: 27,495
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
246747
246747
Posted Tuesday, June 01, 2010 1:06 AM
Forum 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
wildh
wildh
Posted Tuesday, June 01, 2010 1:15 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 13, 2013 7:08 AM
Points: 292,
Visits: 657
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
Lynn Pettis
Lynn Pettis
Posted Tuesday, June 01, 2010 1:17 AM
SSC-Insane
Group: General Forum Members
Last Login: Yesterday @ 11:52 PM
Points: 21,635,
Visits: 27,495
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
Paul White
Paul White
Posted Tuesday, June 01, 2010 1:24 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 5:01 PM
Points: 10,990,
Visits: 10,545
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
jvanderlouw-960980
jvanderlouw-960980
Posted Tuesday, June 01, 2010 2:01 AM
Grasshopper
Group: General Forum Members
Last Login: Thursday, October 06, 2011 2:45 AM
Points: 12,
Visits: 68
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 »
84 posts, Page 1 of 9
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
