I was wondering if the performance gains from using the smaller size data types is worth the effort of worrying about sizes. I've always declared everything an int. Now I have a manager who spends time figuring out if a number can fit in a smallInt or tinyInt instead. My personal opinion is that it's not worth the hassle. Now what are the profoessional viewpoints on this matter?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Datta types smallInt vs tinyInt vs Int smalldateTime vs datetime 1
- Thread starter DarwinIT
- Start date
- Thread starter
- #2
In my case specializing in Data Warehouse solutions proper data typing can be crucial. I had ome across a previously implemented DW that I was tasked with optimizing but not redesign. The people that had designed the system defined all surrogate keys as a bigint. I came through and properly defined the data types, Gender why should it be a bigint? why should date or even customer be a bigint. After this exercise I was able to take an 80GB table and make it ~18GB. This also reduced the space needed for indexes, and reduced memory required for query results.
Does it make a query Faster? I'm not 100% sure I would say it probably depends on the number fo records both queried and returned.
Does it make a difference in other areas? Definately it takes less time to write or read smaller data types so when dealing with VLDBs it does make a difference.
Does it make a query Faster? I'm not 100% sure I would say it probably depends on the number fo records both queried and returned.
Does it make a difference in other areas? Definately it takes less time to write or read smaller data types so when dealing with VLDBs it does make a difference.
ousoonerjoe
Programmer
The obvious thing to point out about data types is storage space. TINYINT = 1 byte, SMALLINT = 2 bytes, INT = 4 bytes. While this doesn't seem like much, but scattered across 5 million records and it starts to add up quickly. This in turn means you have to spend more money on HDD space and more time during back ups, restores/rebuilds. You can always increase the size if your needs out grow your current settings with out many issues.
I always try to use the appropriate data type whether it's in a database or programming.
--------------------------------------------------
"...and did we give up when the Germans bombed Pearl Harbor? NO!"
"Don't stop him. He's roll'n."
--------------------------------------------------
I always try to use the appropriate data type whether it's in a database or programming.
--------------------------------------------------
"...and did we give up when the Germans bombed Pearl Harbor? NO!"
"Don't stop him. He's roll'n."
--------------------------------------------------
-
1
- #5
Believe it or not, this is a complicated question.
Probably, the biggest advantage to using a smaller data type is to 'put' more data in a page. You see, SQL Server stores data in your table on a disk. It does this in 8K chunks (page). Now, depending on your table, you may get 1 row in an 8K page, or you could get 100's (or even thousands) or rows in a page. Now, think about what happens when you write a query. Assuming there is a where clause on the query, SQL Server must search through the tables to get the rows (from the table) to return. It loads an entire page at a time. If there is 1 row in the table per page, it will have more pages to load than if there are 100's of rows per page. File I/O is extremely slow compared to memory, so reducing the amount of file I/O is always a good thing.
Now, don't misunderstand me. It's not like you can get a cup of coffee every time SQL Server needs to read a page from the hard drive. It's fast for each one, but if there are millions of pages to read, then things start to slow down. Ya know?
Anyway... by using a smaller data type, you may be able to get more rows per page, which would increase your performance on large tables. And... when I say large tables, I really mean 'more than a couple million rows'.
Also... there is no valid reason to avoid the numeric data type. ADO is perfectly happy with it. I use it all the time. In fact, if your other choice is Real/Float, then I would suggest you change immediately. The Real and Float data types are approximate data types, with weird issues that are easily fixed by using the numeric/decimal data type.
Basically, if you have large tables, then it is in your best interest to use a smaller data type. For smaller tables... not so important.
-George
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
Probably, the biggest advantage to using a smaller data type is to 'put' more data in a page. You see, SQL Server stores data in your table on a disk. It does this in 8K chunks (page). Now, depending on your table, you may get 1 row in an 8K page, or you could get 100's (or even thousands) or rows in a page. Now, think about what happens when you write a query. Assuming there is a where clause on the query, SQL Server must search through the tables to get the rows (from the table) to return. It loads an entire page at a time. If there is 1 row in the table per page, it will have more pages to load than if there are 100's of rows per page. File I/O is extremely slow compared to memory, so reducing the amount of file I/O is always a good thing.
Now, don't misunderstand me. It's not like you can get a cup of coffee every time SQL Server needs to read a page from the hard drive. It's fast for each one, but if there are millions of pages to read, then things start to slow down. Ya know?
Anyway... by using a smaller data type, you may be able to get more rows per page, which would increase your performance on large tables. And... when I say large tables, I really mean 'more than a couple million rows'.
Also... there is no valid reason to avoid the numeric data type. ADO is perfectly happy with it. I use it all the time. In fact, if your other choice is Real/Float, then I would suggest you change immediately. The Real and Float data types are approximate data types, with weird issues that are easily fixed by using the numeric/decimal data type.
Basically, if you have large tables, then it is in your best interest to use a smaller data type. For smaller tables... not so important.
-George
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
- Thread starter
- #6
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question