470,631 Members | 1,607 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,631 developers. It's quick & easy.

Number of columns and performance

Hi,

I'm designing a new database and I have a doubt in which surely you
can help me.
I'm storing in this database historical data of some measurements and
the system in constantly growing, new measurements are added every
day.
So, I have to set some extra columns in advance, so space is available
whenever is needed and the client doesn't have to modify the structure
in SQL server.
The question is: the more columns I add "just in case", the slower the
SQL reads the table?
Of course the "empty" columns are not included in any query until they
have some valid data inside.
Will I have better performance if I configure only the columns being
used at the moment, without any empty columns?

Thanks in advance.

Ignacio

Apr 25 '07 #1
2 3378
"Nacho" <na*********@gmail.comwrote in message
news:11**********************@s33g2000prh.googlegr oups.com...
Hi,

I'm designing a new database and I have a doubt in which surely you
can help me.
I'm storing in this database historical data of some measurements and
the system in constantly growing, new measurements are added every
day.
So, I have to set some extra columns in advance, so space is available
whenever is needed and the client doesn't have to modify the structure
in SQL server.
Umm... I don't see why you have to do this now.

Do it later.

I mean will you even know the type or size of the columns now?

And how will you even give them meaningful names when they have none now.

The question is: the more columns I add "just in case", the slower the
SQL reads the table?
Of course the "empty" columns are not included in any query until they
have some valid data inside.
Will I have better performance if I configure only the columns being
used at the moment, without any empty columns?
A tad (since there's a small metadate overhead on the leave nodes).

But more importantly, you'll have a messy schema on your hands. I'd wait to
add them.

>
Thanks in advance.

Ignacio


--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Apr 25 '07 #2
Nacho (na*********@gmail.com) writes:
I'm designing a new database and I have a doubt in which surely you
can help me.
I'm storing in this database historical data of some measurements and
the system in constantly growing, new measurements are added every
day.
So, I have to set some extra columns in advance, so space is available
whenever is needed and the client doesn't have to modify the structure
in SQL server.
The question is: the more columns I add "just in case", the slower the
SQL reads the table?
Of course the "empty" columns are not included in any query until they
have some valid data inside.
Will I have better performance if I configure only the columns being
used at the moment, without any empty columns?
As always with performance questions, there are several "it depends".

If these just-in-case columns are varchar (or nvarchar or varbinary),
they only take up two bytes each extra, which may not be cause for
alarm. On the other hand, if they are char(50) or some other fixed
length, they take up the full space, NULL or not.

Next thing that matters is how the access against the tables are. If
there plenty of table scans, or for that matter range seeks in the clustered
index, like OrderDate BETWEEN @fromdate AND @todate, there is certainly
a performance cost for adding more bytes to every rows. More bytes per
row, fewer rows per page, and more pages to read for the same number
of rows. On the other hand, if access mainly is by non-clustered index
and key lookup, then the extra bytes do not have the same cost.

But this latter pattern is rarely the only access pattern for a table,
so the conclusion is that, yes, there is a cost. But how big the cost
is, is more difficult to tell.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 25 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by elams | last post: by
6 posts views Thread by Doug Baroter | last post: by
2 posts views Thread by Neil Zanella | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.