March 5, 2009 at 2:08 pm
No sweat. I didn't see the requirement in the first post either. You owe me a millisecond 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 6, 2009 at 1:26 am
Hi,
Try this... I guess very easy to understand and modify as well 🙂
;WITH Mangal as
(
SELECT Dateadd(yy,datediff(yy,0,getdate())-4,0) Date --Start Date,5 years including current
UNION ALL
SELECT dateadd(dd,1,date)
FROM Mangal
WHERE Date + 1 < =getdate() --End date
)
SELECT Date
FROM Mangal
OPTION(MAXRECURSION 0)
Also see - Time Dimension
John Smith
March 6, 2009 at 3:29 am
Good One
Thank you all
Mangal9i (3/6/2009)
Hi,Try this... I guess very easy to understand and modify as well 🙂
;WITH Mangal as
(
SELECT Dateadd(yy,datediff(yy,0,getdate())-4,0) Date --Start Date,5 years including current
UNION ALL
SELECT dateadd(dd,1,date)
FROM Mangal
WHERE Date + 1 < =getdate() --End date
)
SELECT Date
FROM Mangal
OPTION(MAXRECURSION 0)
Also see - Time Dimension
Mr. 25$ (Article authors) stay away.
March 6, 2009 at 4:59 am
Mangal9i (3/6/2009)
Hi,Try this... I guess very easy to understand and modify as well 🙂
;WITH Mangal as
(
SELECT Dateadd(yy,datediff(yy,0,getdate())-4,0) Date --Start Date,5 years including current
UNION ALL
SELECT dateadd(dd,1,date)
FROM Mangal
WHERE Date + 1 < =getdate() --End date
)
SELECT Date
FROM Mangal
OPTION(MAXRECURSION 0)
Also see - Time Dimension
Great solution Mangal ...
March 6, 2009 at 9:56 am
I respectfully beg to differ. It's a different solution, but there are many many possible solutions. This solution solves the problem with a recursive cte, which is cute but horribly inefficient. (What do you expect? It's a recursive loop at heart. Did anyone look at the execution plan for Mangal's solution?)
I can't call apply the term "great" to any solution that runs 30 times slower. Just because a problem CAN be solved with a recursive CTE, or a loop, doesn't mean it SHOULD be. The fast solutions could also be made more readable through use of a cte and some comments, or through assigning values to variables step by step. But why slow it down? File the three lines of code away in your library and cut and paste as needed. Better yet, go ahead and build yourself a dates table. SQL is really, really good at looking stuff up.
For some really good insights on a variety of date topics, go read the discussion to this article:
http://www.sqlservercentral.com/articles/T-SQL/65423/
Please note: I do NOT recommend the technique the article itself used, but the critique set off a long running thread that covered a lot of ground and brought up many excellent points.
set statistics time on;
;WITH Mangal as
(
SELECT Dateadd(yy,datediff(yy,0,getdate())-4,0) Date --Start Date,5 years including current
UNION ALL
SELECT dateadd(dd,1,date)
FROM Mangal
WHERE Date + 1 < =getdate() --End date
)
SELECT Date
FROM Mangal
OPTION(MAXRECURSION 0)
set statistics time off;
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 1277 ms.
set statistics time on;
select dateadd(yy,datediff(yy,0,getdate())-4,0)+ N-1
from dbo.tally
where N < datediff(dd,dateadd(yy,datediff(yy,0,getdate())-4,0),getdate())+2
set statistics time off;
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 222 ms.
set statistics time on;
;WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
tally AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4)
select dateadd(yy,datediff(yy,0,getdate())-3,0)+ N-1
from tally
where N < datediff(dd,dateadd(yy,datediff(yy,0,getdate())-3,0),getdate())+2
set statistics time off;
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 392 ms.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 6, 2009 at 10:18 am
Yes my query little slow.
Talking about execution plan, run both my query and your query and see cost of the query.
Its 1% VS 99%.
I was not aware that someone will raise question about putting comments on query so OP can uderstand my idea, strange and funny as well.
Good spirit, keep it up.
John Smith
March 6, 2009 at 10:33 am
Mangal9i (3/6/2009)
Yes my query little slow.Talking about execution plan, run both my query and your query and see cost of the query.
Its 1% VS 99%.
I was not aware that someone will raise question about putting comments on query so OP can uderstand my idea, strange and funny as well.
Good spirit, keep it up.
Unfortunately, those costs can't really be compared against each other. Remember that the cost is relative to THAT plan. Just because it has a high doesn't mean it is not efficient. The best thing to do is to test all options, and when possible against as much data as possible, such as a million row test table.
March 6, 2009 at 11:20 am
question about putting comments on query
There's nothing wrong with comments. Their use is to be encouraged and applauded, yet rarely do I feel the need to comment three lines of code. I mentioned that in response to your suggestion that the code you posted was somehow easier to read and modify. It isn't. Even if it was, that would be no excuse for using a technique that is orders of magnitude more inefficient.
You might think that both techniques run in under a second at 4 years' worth of days, so what is the big deal? In a casual environment with a light transaction load it isn't a big deal. But when the numbers get large, replicating poor techniques over and over and over again can drag a box down. I've seen people accept 15 second run times for queries that should have run in less than a second. There is no excuse for that, other than that they didn't really understand SQL Server and its capabilities.
There is more to getting it right than just getting the output desired.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 6, 2009 at 11:29 am
Lynn Pettis (3/6/2009)
Mangal9i (3/6/2009)
Yes my query little slow.Talking about execution plan, run both my query and your query and see cost of the query.
Its 1% VS 99%.
I was not aware that someone will raise question about putting comments on query so OP can uderstand my idea, strange and funny as well.
Good spirit, keep it up.
Unfortunately, those costs can't really be compared against each other. Remember that the cost is relative to THAT plan. Just because it has a high doesn't mean it is not efficient. The best thing to do is to test all options, and when possible against as much data as possible, such as a million row test table.
And also that costs are just estimates, so they frequently have nothing to do with the real world.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2009 at 11:29 am
Mangal, the only reason yours shows as 1% is because it's estimating that it will only return 2 rows. It's like a UDF, SQL doesn't know what it's going to end up with.
Anyone who knows much of anything about SQL should know that "estimated cost" is a nearly meaningless concept. I'm surprised, with your credentials, that you don't know that. Or is writing and performance-tuning SQL not your strong point?
As far as the most efficient way to generate the inital "Tally" CTE, you can always select from the table you're about to compare dates in, in the CTE. If you can't select from that, then you simply can't write the query at all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 6, 2009 at 12:27 pm
Mangal9i (3/6/2009)
Talking about execution plan, run both my query and your query and see cost of the query.Its 1% VS 99%.
SQL mis-estimates recursive CTEs very badly. The cost is based on a single iteration, not the full recursive process. As such, any costing that involves a recursive CTE absolutely cannot be trusted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2009 at 1:07 pm
Thanks for all comments.
My strong point is BI, so you may be right but no one nver stops learning.
That was Actaul evexution plan and not estimated, though most of the time they both are same.
AVoid making comments apart from technical stuff, as Bob's comments about "Comment" was not in good taste.
And no need comment about my credintials also, as when I made comment about your credentials, you din't like that.
I still like my recusrssive query.
My last comment on this thread, as I know, since I replied on this thread, there are 100 prople will be dying to comment on me.
All points about PLAN and TIME appreciated.
John Smith
March 6, 2009 at 1:17 pm
Mangal9i (3/6/2009)
That was Actaul evexution plan and not estimated, though most of the time they both are same.
Doesn't matter. The only difference between those two are that the actual has run-time information (row counts, actual IO costs) and the estimated doesn't. The % of the various operators and of the plans is based on the estimated costs. There are several cases where those estimated costs are completely wrong, recursive CTEs are one of them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2009 at 1:23 pm
Mangal, my comment WAS about "technical stuff". It was about costs in execution plans. I'm sorry if you don't think that's technical.
I have to ask: Why would you like the CTE, when it's a less-optimum solution? That doesn't appear to be logical to me. What am I missing?
As you say, always room to learn more. I'm interested in your statements on this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 6, 2009 at 1:26 pm
GSquared (3/6/2009)
I'm surprised, with your credentials, .
John Smith
Viewing 15 posts - 16 through 30 (of 55 total)
You must be logged in to reply to this topic. Login to reply