reducing columns and consolidating to one column for performance?

Hi

I have a table with 39 columns
the table holds telemetry data, the table grows rapidly at a rate of 10-50 rows per second
It holds hundreds to millions of records and will just keep growing

I want to try and increase performance using multiple means, one of the areas is related to this question
I have identified 20 columns which could be consolidated into one column as a CSV as the data does not need to be directly queried

These columns are of data types Char(1), varchar(6), varchar(20), float, smallInt, int etc
A lot of times these column values are null as the data simply isn't present

I want to store all these in varchar column (not sure of size yet)
If all data is null, there would be 20 commas in this column
usually it will contain some type of data though so may look like this:
,,,,,,,,,,,,,1,1,1,1,,,,,,,200,,

My question is, is it worth doing the above to save on data size and increase performance since 39 columns would span many pages

I know I haven't given much in depth detail, so please ask if I need to give specifics

My goal is to reduce DB size and increase performance (yes i've already looked at indexes etc, and they are working well...thats a conversation for a different thread)
websssAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
imo there are many other things you should be considering...
  • Is there a process which 'archives' old rows to another database, to minimize the size of the active table?
  • Are all of these columns actually used such that some of them can be deleted?
  • Can data modeling of column data types reduce unused size, such as a state code of two characters stored as a nvarchar(200) that can be changed to a char(2)?
  • Does this table have a primary key or any indexes?

The problem with the multiple values stored as comma approach is that it'll take a while to calculate that and store it in the table, which can be a big deal based on INSERT volume, and it will really slow down any queries that need to SELECT that data.

>I know I haven't given much in depth detail, so please ask if I need to give specifics
Just to set expectations, it's the asker's job to provide enough details to answer a question, and not necessarily the contributing expert's job to perform a lot of requirements elicitation to flush them out, as that leads to increasing the time it takes for you to get an answer.
0
Scott PletcherSenior DBACommented:
My question is, is it worth doing the above to save on data size and increase performance since 39 columns would span many pages

Absolutely not.  NULL varchar columns don't use any space at all.  Fixed-length columns, like ints, do still require space.  You could consider:

encoding some varchar values, or possibly even int values if a limited list of values is used;
a separate table to hold rare/less often used columns;
data (row/page) compression;
1
Vitor MontalvãoMSSQL Senior EngineerCommented:
IMHO you have two options:
  1. Vertical partition - create another table with the 20 columns that aren't queryable
  2. Horizontal partition - create a table partition by date so you have much less rows in the live partition
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
If your version and edition of SQL Server you are using supports page compression, that would be the first thing to try.  Naturally if possible you'd like to rebuild the index online.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.