just the date, please
-
My first test was with SS 2000 on a ThinkPad with only 768 Megs RAM. On my 2.8 GHz Pentium 4 HT desktop with 2 GB RAM and SS 2005, it took 0.8 seconds vs 5 seconds. I would expect on an actual server OS and the big hardware that goes with it, both of those figures would be still lower.
I realize that many members are focused on pure performance, but you have to strike a balance between performance, maintainability, extensibility and code re-use.
That's my story, and I'm sticking to it
-
That would be another problem altogether. They both would kill any hope of index seek. You should adapt the date range to fin all needed rows and not the other way around. To be honest I only need to use this time trim server side when I don't use an app to display the results, or need to do some daily aggregation.
But I agree with your point about code reuse. But on the other hand, how hard is it to scan the server side code for the datediff function and see if the change needs to be applied there? I know it's longer, but for the speed gain I would certainly consider it.
As for the remembering part, it's only a matter of deciding to remember it, how doyou remember where you live, or the t-sql language?? If it's too hard to rememebr the whole syntax, you can always remember it this way : [Date only] = (Nbr of days since day 0). Then you'll be able to figure it out when you need it.
-
Also 10 000 rows 100 times / day = 1M rows. That is another scenario where you can shade a few more seconds off the server's hands. I know it doesn't seem like much, but when the server is borderline overloaded, it can make all the difference in the world when THAT query runs 5-7 times faster and gives room to another big query to be processed or 100s of other small queries.
-
Dang... sorry Peter... I keep forgetting to post the results...
===== DateDiff/DateAdd method ======
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
846 Milliseconds
===== DateDiff/DateAdd method as a Function ======
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
8690 Milliseconds
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Lol, 10 to 1. Ya that's a close decision
. -
Now... THAT would be quite the trick... anyone know a way to get "date only" and still be able to use an Index SEEK?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I did quite a bit of testing of the performance difference of inline code vs. UDFs and posted it on this thread:
Demo Performance Penalty of User Defined Functions
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78601
One comment that I have about the tests that people posted earlier on this thread is that it is not good to clear the cache between test runs. It is best if all the data from the table is in memory, so that you are not measuring the speed of your disks, but just the performance of the different functions. This is why I began the test series that I posted on the thread above with a "do nothing", so that I could make sure the data was in memory.
I think the tests did illustrate that the dateadd/datediff seems to perform about as well as the other fast methods, compared to the very slow convert to string and back methods. I prefer the dateadd/datediff because is is so flexible. You can use the same method to get the start of hour, month, quarter, and year, and with minor changes to get the last day of month, quarter, and year. It's also farily simple to get the start of yesterday, 2 days ago, tomorrow, etc. It's also fairly simple to do offsets into the day, like today at 06:00, 12:00, or 14:00. Code in all of the following links make use of it one way or another.
Start of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755
Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
End Date of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759
End of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760
Time Only Function: F_TIME_FROM_DATETIME
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65358
Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
-
Lynn,
Your comment was extremely relevant re: the volume & environment. Many of the posters have done detailed analyses of performance using 1 million rows, and have poo-pooped my idea about using a UDF. However, I am using my UDF in three contexts:
The customer is a Realtor organization. I need to furnish queries for the following scenarios:
1. List the # of agents, from a table of 14,000 members, whose MLS status has changed within the past five days, totals by day, with a grand total. This is done a couple of times a week.
2. List the # of agents, from a table of 14,000 members, whose addresses have changed within the last 14 days, totals by day, with a grand total. This is done once or twice a week.
3. Prepare A/R statements for 3,000 Realtor offices, of which approx 1,000 have A/R balances. This is done monthly.
What I am getting at is that none of the posters asked about the scope of my queries. Therefore, 1 million rows is far outside the scope of my requirements. If it takes 6x as long to run the UDF, that means naught in the context of the business requirements.
-
Quoting myself : "But I agree with your point about code reuse. But on the other hand, how hard is it to scan the server side code for the datediff function and see if the change needs to be applied there? I know it's longer, but for the speed gain I would certainly consider it."
My point is that it will always make a difference someday for someone. My boss just bought a new server for 12-15 K. Now the good news is that he didn't need to do that. He had had complaints that the server was running slow of a few parts of the application. My move was to disable auditing for that particular table untill I could rewrite the code (application was making 100 calls to the DB instead of 1). Anyhow I thaught that the problem was solved but a few more days go by and a new server is getting installed. This is the good news because the old one was just borderline OK and this one kicks ass. In that situation, all the small improvments just make a huge difference. A few MS don't seem to matter that much, but over 1 or 2 M calls to the server over the 8 hours day, they can add up to not spending 12-15K for a server you don't really need... yet. Just to crunch numbers : 1.5M calls on the server at 2ms saved a piece, that's almost 1 hour of work saved on the server over 8 hours. This can (and is) the difference between a slow app and something that works OK, not perfect but very acceptable.
Now let's see what this new server has to offer
. -
dbo.fnNoTime(dtSomeDate) BETWEEN '1/1/2007' AND '1/31/2007'dtSomeDate >= '1/1/2007' AND dtSomeDate < '2/1/2007'
This utilize any index present and do a index seek, instead of an table scan.
N 56°04'39.16"
E 12°55'05.25" -
Jeff, we've done it once together. Here, on this forum.
Indexes computed column using dateadd/datediff function.
You must remember that issue with implicit conversion of 0 to date which made that function nondeterministic.
So, add explicit conversions to the formula and use it for a computed column.
_____________
Code for TallyGenerator -
You must remember that issue with implicit conversion of 0 to date which made that function nondeterministic.
THAT's what it was! Thanks Serqiy! I knew we did it once and I couldn't remember how to make the bloody formula deterministic!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Not quite what I was after, Peter. Sorry I wasn't clear... was looking to compare the dates in two tables for rows having occurred on the same day.
Serqiy reminded me
that we previously used calculated columns that had deterministic functions (can't use "0" as a date) so they could be indexed.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Bill,
I absolutely agree with writing consistant code and some of the methods to make the code consistant and easy. My only problems with such a UDF are (putting aside speed for consistancy)...
- You have to make all the Developers aware that the function exists... can be a problem in a high turnover shop.
- You must enforce its usage... unless you do code reviews of all code (we do and it's worth it) and you have the authority to insist on corrections, that's not possible.
- This may be the real key... If you try to enforce the usage of such a function, you may have to deal with folks in an odd fashion... some will say "whatever" and do it your way and others will tell you that growth happens and that performance and scalability are very important all the time and fight you all the way. They may even loose respect in you as a "mentor/goto person" because they know its wrong for performance (every bit helps). If you're the lead in the shop, you just can't afford to loose the respect...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Not to make too big a deal about it, I think a function for something like this has its place:
You may just need to call it once in a query or store procedure to setup a from/to date range.
It’s also useful to document a correct way to do it, even if it will be implemented with inline code.
It’s more important to make sure that you are calling it as little as necessary (same with inline code!), and don’t do a query where you apply the function (or inline code) to every row, unless there is no other way to do it.
Viewing 15 posts - 31 through 45 (of 62 total)
You must be logged in to reply to this topic. Login to reply