Dates in Queries

Question

Dates in Queries

Billpl

SSC-Addicted

Points: 430
More actions
October 31, 2006 at 2:26 pm

#170977

Hi All,

I've been working on this one project for quite a while and it's about a optimized as it can get, but it's never been run under really heavy loads. One major concern is that about 90% of the output reports require date manipulation of some sort. Tons of Year(), DatePart(), DateDiff() functions. However, one set of queries works off of a "Month Number" field which is simply the month count (number) starting from the year 1990. It's really clean and simple. Just predetermine the "Month Numbers" and fire away at the various ranges. The queries become straight "Int to Int" comparisons. It's so simple and clean I'm thinking of converting the whole system over, but only if it's really worthwhile.

Question being, would this have a significant enough performance gain or does SQL's built in handling of dates already adequate to manage all these date functions?

Thanks in Advance
Bill

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

jezemine SSCrazy Eights Points: 8059 More actions · Answer 1

I wouldn't consider doing this unless you have identified that your queries using these date functions have perf problems.

I have found that it's not a good idea to make assumptions about where perf problems lie - even the most experienced devs are almost always wrong about where the most time is spent in their code. the only way to find out is to measure, and for sql that means using profiler and showplan.

I would suggest testing your system under heavy load and finding the bottlenecks explicitly, rather than making changes in an a part of the code that's not known to have perf problems.

---------------------------------------
elsasoft.org

Vladan SSC-Insane Points: 22072 More actions · Answer 2

DATEADD and DATEDIFF functions have very good performance, as long as you stick to this way, you should be OK. Performance problems can be sometimes caused by excessive use of conversions (e.g. to varchar and back to datetime), especially if they are used in a way that forces scans instead of index seek.

For a range from-to, datetime data are very efficient (or "clean", as you said) and I don't see any reason to replace them with INT columns. On the contrary, I see so often on these forums questions of the type "how to do this if my date column is varchar/int instead of datetime" that I suppose doing what you said would only cause more problems. More often than not they end in "change the datatype to datetime or create another column with this datatype, then it will be easy". Stick to DATETIME datatype for all date and time entries.

If you are not thinking about performance, but about how simple the queries are for someone reading the code, then yes - "WHERE mytable.month = 3" is shorter than "WHERE mytable.date >= '20060301' AND mytable.date < '20060401' .." - but I don't think it will influence the performance. And maybe this particular query will be by 5% quicker, but some other will be by 20% slower. Everything depends on what you need to do with the data and how often, and how many rows are in the tables etc... But my opinion remains the same : don't do that, stick to datetime.

Think more about proper indexing than about nonstandard solutions. With a good index, queries are very fast with conditions on datetime columns. Choose the right column for clustered index, index all columns that need it, and you should be OK.