| « Prev | Page 1 | Page 2 | Next » |
|
Dude, that's wild...
Thanks God he started in 1990... |
|
Why did he end at 2100? Does he know something we don't?
|
|
Everyone knows that the world is going to come to an end in 2037 when the Unix clocks roll over. Why include dates past then?
|
|
Jeff S: Regarding your "too late to be posting on a Friday night" comment...think about it. How many people here were continually refreshing this page (late on Friday night) just to read the latest and greatest wisdom from their fellow programmer? Probably a lot. And everyone knows it's not "cool" to be home on a Friday night...especially on your computer...reading about what someone else programmed. That's pretty geeky...and a little sad? I really can't talk because I was one of those closely following this discussion...late on Friday night...but it's an interesting observation nonetheless.
|
|
actually it's not that stupid idea. once i worked on a system where the client used a different week numbering than in the real world ... creative accounting :) - in that system we had to remap days to a predefinied week
|
|
I think he used a table for a future change in our calendar... Alien invasion, Klingon world domination... Who knows ? Maybe in 2099 won't be a fixed number of day per week :)
|
manually - get a temp in, and set em typing... |
|
My guess (I submitted this code) is that he did not know about the FirstDayOfWeek parameter of DatePart, nor of the language settings in SQL and Windows that can change this. Here in Belgium weeks start at Mondays, not at Sundays (which is MSSQL Server default in not set otherwise).
As for indexes, it's quite possible that a JOIN would be faster in this case (DatePart are indeed extra function calls), but since the code only runs one time at a time, and already executes in less than 100ms, the performance gain I would get would probably be neglectable, and imho to small to justify the use of an extra table containing nothing else than static data. |
|
I've seen the exact same table in a production system with a slight twist: all quarters from 2000 to 3000, so like this:
1 2000 1-1-2000 2 2000 1-4-2000 3 2000 1-7-2000 and so on... Wicked :) |
|
@Jeff S
The point I'm trying to make is that although sometimes there is necessary an auxiliary table to speed up or simplify querying a date range, in this particular example you can do that without the need of such table. So the thing I didn't agree with your post in the daily WTF is that using Datepart function it's a WTF with a large database. As I said sometimes your date range is not the best way of discard records in your query so an index in that column plus the extra table won't give you nothing. But once you got all those extra db objects you will always have to pay the cost they impose. But if we talk only of the example in the daily WTF what I can say is that there is a way to calculate the starting and ending date of a week of a year just using build in functions, so for this scenario an extra table isn't justified. For example: case when @week = 1 then convert(datetime, '01/01/'+cast(@a as varchar), 103) else 1+((@week-1)*7) + convert(datetime, '01/01/'+cast(@jaar as varchar), 103) - datepart(dw,convert(datetime, '01/01/'+cast(@jaar as varchar), 103)) end will give you the start date of a given week of a given year. If you wrap that code in a UDF you could reuse that code easily across your queries. The code for get the end date of a given week it's similar. SO why do we need an extra table in this case. As I said sometimes such auxiliary tables are useful, I don't like them a lot as they are just a hack but hacks are sometimes the easy way of do something, in this case it's not the case IMO. |
|
@Guayo --
you're not following anything I've posted so I guess I need to give up ..... I don't recall EVER saying we NEED an extra table ... and I recall expliciting describing the EXACT notion you've just posted (actually, I've tried to get you to notice that TWICE now). If you decide to reread all my posts carefully and respond, that would be great, but until then I will have to accept the fact that I am not able to convey these concepts to you so I will just have to let it go. |
|
@Jeff S
I can totally relate to why Guayo briefly skims your posts and then responds on a whim. AS of late, the posts on the daily WTF have been rather extensive and very wordy IMO. There's not a lot there in terms of content, just a bunch of posturing and pontificating. "Look at me, I'm such a wonderful coder"...that sort of thing. I used to read through most of the posts. Nowadays, if it's over a couple sentences in length I briefly skim and move on. What they're saying really isn't that important after all. |
|
It's great to skim long posts when browsing, but don't try to have an intelligent discussion with someone if you ignore most of what they say! (that applies to real-like conversations as well!)
|
|
Jeff S,
I feel for you, but ignore people's comments. A number of people here will shift the argument if they are incorrect or on shaky ground... |
|
Cakkie, "but since the code only runs one time at a time, and already executes in less than 100ms," means that your code can only handle 10-11 connections a second. In other words, it is built with a growth potential severely limited - especially since it'll get even slower as more records are added. Once another department wants to start using it (or worse, it goes live on a webserver) you're only going to have to create these optimizations all over again. (A join like this can be a few milliseconds, more than an order of magnitude less than 100ms. How is that "negligible"?)
You are the WTF. |
|
Well, easy :)
Currently the code is used by only one person, only once a week. They first have to check if the inputted data is correct (which they have to do manual (can take a few minutes per timesheet), and then it needs to be approved (what this code does) by a click on a button. Say the number of people using this code at the very same moment doubles, no tripples, no tenfolds, then it would still run within 1 second. If they come bitching over that, I'll start complaining that it takes to coffee machine 15 seconds to prepare my coffee :p |
|
Are you kidding me?
W. T. F. |
|
Anon: Haven't you read Nostradamus's predictions?
|
|
Wow! Nice solution... I think I'm gonna use that tblDateYearWeekDay for my next project, just a little question, how do I fill the table?
... ... Hahh! I got it! I'm gonna make a script to fill that table, all that I need is to call DatePart in a loop, it was easier than I thought... |
|
Guayo! Step Away from the keyboard with your hands in the air!
:) |
|
WTF is that?
WTF was he thinking? WTF does he still have a job? Ive done some odd things with date functions... but this is uh... It almost transcends WTF. Is the datetime field the primary key? can you do that? What does he do to turn int into day of week? Bet theres another table... ... 3 Wed W 4 Thur T 5 Fri F ... |
|
I would be careful about replacing usage of that table with DATEPART functions.
It is entirely possible he is using some non-standard dates, or at least leaving open the possibility to do so. I wouldn't necessarily want to give up that flexibility. Faster is not always better... At the very least, this requires a full regression test. |
|
No one is defending the obvious reason this could be a good implementation?
This is the best way to handle dates and times if you're a total goddamn retard. |
|
Hey - what's the story on the redneck. I see mentions of him from time to time.
|
|
Tom's solution would not have worked for the company that I worked for. They had a 4-4-5 calendar, and because of that they needed a correction week every seven years. So any of the built in functions that gave you a week number could not be used. That 53 week year every 7 years caused a lot of grief. The use of a table is silly but has anybody looked at the table data? It is possible some dates where manually hard coded to specific weeks/years etc. So Tom's solution could cause more problems then it solves. We may have to see the spec before we pitch this programmer into the fire.
|
|
It's ... almost ... beautiful. Transfixing, in any case. Sort of like the best of Rube Goldberg's contraptions.
I've always believed that the best way to get anything done is to give a lazy man a tight deadline. This guy obviously has too much get-up-and-go for his own good. |
|
Clearly this table should be optimized by moving the DateYear values out to a lookup table. I'm sure that'll fix their long-running queries!
|
|
Actually -- believe it or not -- if that table was properly indexed on the WeekDay and Year columns, and RegDatum was also indexed, a simple join to that table would FAR outperform the "solution" of:
AND DatePart(WEEK,regdatum) = @WEEK AND DatePart(YEAR,regdatum) = @JAAR The above is actually a WTF in itself if this was a large database -- if regdatum is indexed (as key date columns often should be), the above WHERE clause cannot make use of it and must retrieve EVERY row in the table and evaluate the two functions. It would not be an optimal solution; as all of us "experts" here at TheDailyWTF know, that would be to calculate the startdate/enddate range of the desired data and retrieve rows within that range like this: AND RegDatum BETWEEN @Start AND @End Causing index seeks on RegDatum and returning the rows very quickly. By the way -- while the range and size of this "table of dates" is probably a little insane, tables of dates can be extremely handy, and often is the only way to produce resultsets dynamically for every day in a date range, even when no data exists. (which can be a very common request). You will often see tables of numbers (often called Tally tables) in many databases for some advances calculations and reporting which would otherwise require cursors and other WTF-esque constructs. |
|
He'll be having th last laugh if we move to a decimal week!
|
|
WTF...?
@Jeff S: the people never thought about Y2K - so why care about 2100 + n? ummmm.... WTF? |
|
@drift -- apples to oranges my friend. not only are we talking about code written in 1980 that must run 20 years later vs. code that must run 100 years later, but we are talking code vs. data.
Y2K : all programs had to be rewritten, recomplied, data files altered, etc. This : some rows need to be added to a table. Not a good comparison. This is a *little* easier to *fix* a hundred years from now! |
|
@Jeff: so it's still no good software design - and remember: speed might be important, but it isn't everything that counts.
|
|
@drift --
>>so it's still no good software design huh? What's not good software design? the existance of this table? the ideas I presented in my first post? not sure what you are referring to. |
|
I'm just glad he coded this to run to 2100, because any good engineer knows that code has to be written to survive for a hundred years. ^_^
|
|
@Jeff S
I seriously doubt that if that table was properly indexed a simple join would "FAR outperform" using DatePart. Unless I'm missing something really obvious (like if the SQL Server DatePart implementation sux) I can't believe a table access (indexed or not) would be faster than a function call (AFAIK, IO access still is orders of magnitude slower than normal computational tasks) |
|
Guayo --
You missed the entire point. you're right -- I/O is much slower than a function call. Using an index results in exactly as many I/O operations as you need. If 5 rows match the criteria, 5 I/O's are used (essentially). Not using an index -- i.e., using a function (datepart) that will require a calculation to be performed on every value in a table or in an index -- requires an I/O operation for EVERY VALUE IN THE TABLE. if 5,000,000 rows are in the table, and 5 rows match the criteria, 5,000,000 I/O's are still performed. let me give you an analogy: using a regular old book index in a large textbox, which would you be able to do faster: give me the page numbers of all key words starting with the letter "e", or the page number of all key words in which "e" is the 5th letter? Do you understand the difference, and how this analogy is relevent to the problem? |
|
I would have coded this to the year 2200, just to be on the safe side.
|
|
Jeff S
>>>> Actually -- believe it or not -- if that table was properly indexed on the WeekDay and Year columns, and RegDatum was also indexed, a simple join to that table would FAR outperform the "solution" of: <<<< You took the words right out of my mouth. The existence of this table, per se, is in no way a WTF. A table of dates and even a table of integers is a perfectly prudent thing to have and leads to some wonderfully elegant solutions that are EFFICIENT and ACCURATE. The WTF here lies entirely in the poor indexing applied to the table and the the fact that the SELECT is performing a subquery rather than a join. The secondary WTF here is the number of people who feel that the table is the problem. This can be attributed to the following axioms (held to be self evident): 1) MOST People don't know how to use a database. (No real surprise here) 2) MOST Programmers don't know how to use a database (Still no real surprise) 3) MOST Database programmers don't know how to use a database (A little surprising, perhaps) 4) TOO MANY DBA's don't know how to use a database (Professional misconduct but not that great a surprise) 5) The state of the art in databases is, with some exceptions, pretty darn good. Room for improvement? Damn right but still pretty darn good. The state of affairs (actual common usage) is pretty damn sorry. Yes, you can quote me on that! |
|
not long ago software developers doubt, that software would run for more than 20 years...
|
|
@Jeff S... again
I just undestand what you mean... You mean that if regdatum was indexed and that index were used in the query then the db engine would have to read less rows in the table instead of a full scan. However there could be an index on regdatum and still the db engine could chose another index (a key maybe) and then that tblDateYearWeekDay table would give you cero performance improvement. In the query posted it is shown an additional column used to discard records, that column could very well be indexed. So ye,s you may be right that using such auxiliary table could improve performance in some queries, but not always, and let's not forget the cost of having an extra index. I can't agree with you that using DatePart is a WTF instead of the table as you (as well as I) don't know what indexes those db have. However if you say that using DatePart no matter what is a WTF then I would suggest you do some performance tests to find out. |
|
>>>Donald K. Burleson<<<
I love the fact that his dress code is so exacting on one page and yet he has taken to working in the nude on another page. |
|
@Bustaz Kool
Very well said, and very true ..... That's a common thought, from non-database programmers to say stuff like "the datePart function must SUCK if it can't use an index! what a dumb computer". Guayo -- swing by my blog I'll put some sample code up for you. should be up in a half hour or so -- do you know how to you query analyzer to look at execution plans and all that? |
|
Assuming that regdatum has an index applied to it. Using the value in a function renders it non-sargable. This means that what Jeff S is saying is absolutely true. Now you have the overhead associated with the index but none of the benefits.
As to the overhead itself: The table is fairly static in nature. We are not going to be adding deleting or updating this table for essentially one hundred years (ish). A table of a few thousand records just isn't that big of an entity to concern ourselves with. If you're really that worried about the size of the index itself, make it a clustered index. |
|
Just throwing this out there: how about an index on a computed column?
|
|
@Phil -- that would work, too. Or perhaps an indexed View.
Again, the only point of most of these comments isn't to defend this particular piece of code per se, but the knee-jerk reaction of many (including DBA's) when they see tables like this in a database and don't understand the (huge) potential benefits of setting stuff like this up. It's funny, some here are making fun of this guy for expecting his program to still be running in the year 2100 (as if his code will last THAT long!) and others are saying it is a WTF because it won't work Jan 1st, 2101 !! This site cracks me up sometimes .. always entertaining ... (by the way, my weblog post with some sample code is up, so check it out for those who are curious ... ) |
|
Phil Scott
>>>index on a computed column? <<< Yes, you can do this, at least in some products, but what's the upside? The table already exists and I assume has accurate data. |
|
@Jeff S
I posted in your blog but I’ll do here to. First. I don’t have SQL Server at hand from where I’m posting (just Informix and Oracle, that BTW supports function-based indexes). When I first posted here I didn’t realise that as you said with proper indexation that query would perform better and even it could out-perform using datepart). I do see that know, however I don’t agree with you that using datepart is a WTF in itself). I don’t know if you fail to see that the query posted in today WTF includes an additional column in the condition. That column could indexed and that index could be a better way to scan the tblTijdregistratie table instead of the dates condition. Maybe I’m still missing something? Anyway. I’m a little rusty at T-SQL today but I wonder if that the condition could be redone using the Datepart function making the date condition clause sargable? (Like creating a date from the @week and @jar parameters and the missing part of the date obtained from the regdatum column). |
|
I repeat, I don’t have SQL Server at hand but. Wouldn’t this statement be equivalent to what was posted as the fix but in this case sargable for an index on regdatum?
regdatum = dateadd('dd', datepart('dd', regdatum), dateadd('ww', @WEEK, convert('01/01/'+@JAAR, 103))) I could be totally wrong and that condition could be a WTF so this is a good place to post it. |
|
Sure the code is bad, but creating a date table like this is often a very good solution (Jeff S and Bustaz Kool have pretty much gone over the reasons for that). In fact, this is very standard for data warehouses, and I'd go so far as to say that a data warehouse that doesn't use a date table is poorly implemented.
It doesn't excuse the bad code, but having a date table is definitely not a WTF. |
|
upss... replace
convert('01/01/'+@JAAR, 103) with convert(datetime,'01/01/'+@JAAR, 103) |
| « Prev | Page 1 | Page 2 | Next » |
