May 4, 2010 at 2:03 pm
I'm working with an Oracle database that was converted to SQL Server by headless horsemen (no thought given to performance, and they have all vanished into thin air).
There are many columns of datatype DECIMAL(9,0), but filled with INTEGERS no greater than 10.
CREATE TABLE [bonehead_table](
[bonehead_column] [decimal](9, 0) NULL)
Is it worth my time to convert the columns of datatype DECIMAL(9,0) and chock full of relatively small INTEGERS to datatype INT?
My concern is that there is an IMPLICIT conversion from INT to DECIMAL(9,0) everytime the application SELECTS * FROM bonehead_table WHERE bonehead_column = 3.
Thanks in advance for humoring me.
May 4, 2010 at 2:24 pm
My take on it is that you've probably got bigger fish to fry.
Having decimal columns instead of integer ones will impact how much disk space the database takes more then the performance.
I think it should be fine the way it is.
Kev
-=Conan The Canadian=-
Kev -=Conan The Canadian=-
@ConanTheCdn
May 4, 2010 at 2:27 pm
Thanks, Kev, just checking.
May 4, 2010 at 4:55 pm
If/When you do come back to it, it shouldn't be too hard to write some code that will script it all out for you. No real need to do these all one at a time.
May 5, 2010 at 6:33 am
I was thinking of this last night and there is one spot where it could bite you.
If you have an index on any of those columns that are DEC type and you have a stored procedure that has an INT variable that is queried against those DEC columns, that could cause some lag. If the variables in the stored procedure are also DEC, then you're fine.
Kev
-=Conan The Canadian=-
Kev -=Conan The Canadian=-
@ConanTheCdn
May 5, 2010 at 7:10 am
I appreciate the thought given, Kev...
All of the application code uses INTEGERS to compare to the database's DECIMALS except in very few cases where the DECIMAL datatype is actually warranted.
In their infinite wisdom, the horsemen used absolutely no stored procedures. None whatsoever.
The database is mostly read and, now that I have inherited it, is quickly becoming heavily indexed.
Many of the columns of DECIMAL datatype are named %_ID, primary or foreign keys, referenced in many WHERE clauses and join operations, and indexed.
Please let me know if your concerns are still valid in light of the fact that there are absolutely no stored procedures used to access the data.
May 5, 2010 at 7:24 am
Then yes, you will want to either change the data type of your columns to INT or get the code that is querying against them to use a Decimal datatype.
The issue that I've seen in the past, is that instead of implicitly converting the INT variable to decimal to match the column, the engine will sometimes do an implicit convert of every row in the table to INT to match the variable which can really slow down a query.
Kev
-=Conan The Canadian=-
Kev -=Conan The Canadian=-
@ConanTheCdn
May 5, 2010 at 7:49 am
Thank you gentlemen for your time!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply