Performance hit Joining Datetime to SmallDateTime?

[jsteph]

If I have the two above-mentioned data types in a Join, I assume the query engine must Cast one to the other, but is this a major performance hit? Is it maybe less a hit because the data-types are 'somewhat related'? Or is it just as big a hit as, say, casting a varchar to an Int or vice-versa? I'm not doing an explicit cast, I'm just joining and letting sql-server do the cast.

Both table's dates in these fields have no time value stored--it's date only. But I have dates that fall outside the value-range of smalldatetime so I need to use the larger datetime for that, and the other table was built with smalldatetime from the start--it's date-only but it's values never fall outside the range.

Would it be worth just converting the other table's smalldatetime to datetime? Or is the performance hit small enough that I can just leave it?
Thanks for any info on this...
-Jim

 

[r937]

no proof, but i believe the performance hit is negligible

after all, datetime and smalldatetime are stored as (pairs of) 4-byte and 2-byte integers respectively

i don't recall anyone ever mentioning the performance hit of joining an integer column to a smallint column, so the datetime-smalldatetime comparison should be just as fast





r937.com | rudy.ca

 

[mrdenny]

I would assume there would be some sort of hit. Any indexes on the smalldatetime field probably won't be used as it will be converted to datetime (I think SQL will go this directions, it could be the other way). For best performance I'd simply switch the smalldatetime to datetime. You won't loose much in the way of storage.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[vongrunt]

Hm... I just did profiler thing on a lab example and...

Difference in execution plans is more than negligible. With identical date types everything is perfect. With different types (datetime vs smalldatetime) server performs additional Compute Scalar and Sort. Plain stupid IMO. I didn't get much of a time difference probably because of small data sample and beefed dev machine but logical reads were significantly up. If anyone is interested maybe we can make more thorough testing these days...

Coming from old school ("... every wasted byte counts blah blah") I'd probably try to use smalldatetime everywhere. There are not much real-life applications for which range of smalldatetime type is not enough. But yes, switching both to datetime may be definitely... more practical.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jsteph]

Thanks very much all. I ended up changing the smalldatetimes to datetime.

As for the values of smalldatetime, there is a lot of back-and-forth data transfer with this particular project--mainly between Access Jet data and SQL.

Much stuff I'm importing from a third-party Access table did not make use of NULL for blank dates, but just left it at Dec 30, 1899 12:00 AM, which is out of range of smalldatetime. I know there are simple conversions to NULL or zero, but it's all over the place, there's logic that had been sprinkled about dealing with that, so it's easier for me to just change to datetime.

What's ironic is that the one table with smalldatetimes was created by DTS from an Access table. I guess it's one of my pet-peeves that SQL-Server, which should know JET intimately, insists on converting JET Datetime fields to Smalldatetime by default--when it knows that very type can't handle some of the (very common) values that come in.

So that'll be a check-list item for every DTS conversion/transfer I do--change all the smalldatetimes it puts there by default to datetime.
--Jim