when datatypes causes problems

When I visit my customers I often see the great DBA’s trying to create new databases for various reasons. One classical situation is that they have an ERP-system, and now they want to create a database that extracts information from one or two tables. What they often dois write:

Select *
into xxxx
from sourceDB.schema.table

two things often happens:

  1. To many columns are added to a row (Read: More than you need).
  2. Wrong datatypes are chosen

 The consequence of this is that each row will have a bigger size than necessary, and therefore there is a risk of a poor performance when extracting data from the new database. Let’s create an example and focus on the issue with wrong datatypes.

First we create the database

use master

go

— A nice little way to test if the
database exists and if it does

— then we drop it.

if DATABASEPROPERTYEX(‘SAFdatatypes’,‘Version’)>0

drop database SAFdatatypes

 

— We create the database

create
database SAFdatatypes

Now we have a database and now we create a table. Let’s call the table REPORTS

CREATE TABLE [Reports](

   ID int identity NOT NULL,
alphatext nchar(200) not null,
Number decimal(38, 20) NOT NULL,
CONSTRAINT
[Reports_PKEY] PRIMARY KEY CLUSTERED
(
[ID] ASC
)

)

We can now test and see how much space that are used/allocated for the table by introducing the stored procedure SP_SPACEUSED:

Exec sp_spaceused‘reports’

As you can see the size is 0 and that is because it is first on the first insert that space are allocated.

Lets try to add a simple row

insert into
reports VALUES (‘My name is soren’,1)

go

exec sp_spaceused‘Reports’

That does not make sence (because of the way SQL Server stores data), but lets try to add a lot of rows and see what happens.

Declare @I int
set
@I=0
while
@I<10000
begin
  Declare @SqlStatement varchar(2000)
set
@SqlStatement=‘insert into reports VALUES (”My name is soren”,”’+convert(varchar(5),@i)+”’)’
exec
(@SqlStatement)
set
@I=@I+1
end

 

 Now. If I want to see all rows, then I can write a select statement, but before I do it. I want to set statistics IO and TIME on to get some measures for the performance.

Set Statistics IO ON
set
Statistics TIme on

select *
from
Reports

What I can see on the message page is:

1.
It took 397 miliseconds to execute and I had 558 Logical reads.

Now lets try to optimize as much as possible. Optimization means that minimizing the datatypes as much as possible, and since I always have a text called My Name is soren, that are 16 bytes long
and I don’t need Unicode, then I could choose a Char(16) and since I have numbers up to approx. 10.000 then I can use a smallint, that are only two bytes long. I know this is theoretical, but when you evaluate your own system, then  you must not only take into consideration the current data, but also data that will be added at a later time.

I will change the datatypes:

ALTER TABLE Reports
ALTER COLUMN alphatext char(16) NOT NULL

ALTER TABLE Reports
ALTER COLUMN Number smallint NOT NULL

Alter index reports_pkey on reports rebuild

I just rebuild the index to make sure that my changes in datatypes are implemented. So now, lets try to see how much space this table takes up:

exec sp_spaceused‘Reports’

 

WOW!!!! That is less than 10% of the size from previous, so first conclusion:

IF YOU WANT TO CREATE A TABLE WITH SELECT INTO THEN REMEMBER TO CHANGE DATATYPES (IF POSSIBLE) AFTER THE CREATION OF THE TABLE. IT SAVES SPACE!!!!

But now lets try to see what happens with the performance:

Set Statistics IO ON

set Statistics TIme on

 

select *
from
Reports

What I can see on the message page is:

Before it required 558 read and now it is only 41 read!!! Before it took almost 400 MS now it only takes 152.

Can you imagine what happens if you have a database with 25 columns AND 100 mill. Rows and you use to large datatypes??????

Conclusion 2:

CHOOSING THE RIGHT DATATYPES ENSURES THAT YOU HAVE THE BEST POSSIBLE PERFORMANCE!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s