SQL Server performance: 50 columns vs single binary/varbinary

Question

Is it possible to improve SQL Server 2008 R2 (and newer) insert performance by replacing (say) 50 float columns with a single binary(n) (n being 50 x 4)?

I would presume that using a fixed size binary(n) should improve performance (amount of data is the same, with less work needed to handle all the columns and shorter SQL queries), but many sites recommend against using binary columns, so I would like to see if there are really issues with using this?

Also, the issue is that the table is rather denormalized, and not all columns are filled with values usually, so varbinary(n) would allow me to reduce the row size in many cases. Sometimes only a single column is filled, but ~10 on average.

And then the third question is, how about going a step further and replacing (say) 5 rows x 50 float32 columns with a single varbinary(5*50*4)?

So it would be cool to get some insights into:

1. Replacing 1 row of 50 float columns with single binary(200);
2. Replacing 1 row of 50 x float with single varbinary(204) (several bytes for flags/length info) - to save space when columns are unused;
3. Replacing 5 rows of 50 x float with single varbinary(1024) (several bytes for flags/length info).

Entire row is always read at once in all cases.

(Update)

To clarify, the data being stored is:

 Timestamp_rounded    Value_0ms  Value_20ms  Value_40ms ... Value_980ms
 2016-01-10 10:00:00    10.0       11.1        10.5     ...    10.5

I am always reading the entire row, the primary clustered key is the first column (Timestamp), and I will never have to query the table by any of the other columns.

Normalized data would obviously have a Timestamp/Value pair, where Timestamp would then have millisecond precision. But then I would have to store 50 rows of two columns, instead of 1 row (Timestamp + BLOB).

Gibraltar

Storing multiple values in a single field is almost always the wrong decision. – Aileen

What do you want to do with this later? How do you want to read / manipulate this I'm-a-multi-value-binary-something? – Philippines

Well these floats are measurements within a single second, and I always store and read them at once. So my app can easily serialize/deserialize these columns if this will improve insert performance. And I just want to be able to store as fast as possible – Gibraltar

Further more: The difference won't be that big as you have to transfer the same amount of data (roughly)... Are you talking about millions of bytes per second? Why is performance so crucial? It is the first NF telling you, that you should never store more than one value into one column... – Philippines

@Lousy, if you will never need to read these values within SQL (Hey, let's create a neat report out of the database! Oh, wait...) you might store a binary stream. This will have no meaning within SQL-Server, but you might win some milli seconds. I would not do this... – Philippines

You probably don't want to store "as fast as possible" - you just want to store "fast enough". If you gain anything, it won't be much. Anyway, have you tried using a profile analyzer tool to see which is actually faster, and by how much? – Southwick

and tell me how your cluster index will sort your data if it is composite..? – Toback

@Schnugo: who said I will never need to read them? What I wrote is that I need to read the entire row at once, each time, and never have to read individual columns, or query by any column other than first. (I have updated my answer). – Gibraltar

How are you currently inserting your rows? One-by-one by the application? Or Bulk somehow? – Interlocutrix

@PeterHenell: Yes, I store it in batches (ADO.NET batching). I wait (say) 5 seconds until I get a batch of data and then store it at once. I haven't tried SqlBulkCopy. – Gibraltar

I tried it using SqlBulkCopy, with 50 columns vs 1 varbinary column. In my tests the varbinary version was crazy fast compared to 50 columns. The 50Cols version managed to insert 36000 rows over 30 seconds and Binary version did 1.6M rows. You can try it using my testcode here github.com/PeterHenell/binaryBulkInsertComparison. With that being said, I think there is something in my code which is punishing the 50Cols version far more than the single col version so a better version of the testcode would provide a more accurate result. – Interlocutrix

@PeterHenell GitHub is down right now can't check, but the likely culprit is failure to achieve minimally logged insert. Read The Data Loading Performance Guide, is an awesome resource on the topic and cover pretty much everything for the SQL Server 2008/R2 releases (ie. it does not cover columnstores). – Behring

@RemusRusanu I was expecting both methods to suffer from that, but now I'm not sure. – Interlocutrix

After removing the overhead of reflection in my testcode, the score is much more even. There is now only a 3X difference between the two methods; 2M vs 6M rows over 30 seconds. I would definitely try to tune your insert-batches before resorting to binary fields. – Interlocutrix

@Peter: thanks a lot for the suggestion, I will do my own benchmarking, unfortunately I was rather busy these couple of days so I didn't have the time to check it myself, I hoped this data perhaps existed somewhere already. When you wrote "the score is much more even", I expected it would be something like a "10% improvement", but if I can handle 300 devices instead of 100 using the same hardware, it's quite an achievement I believe. :) Very nice of you for taking the time to benchmark this. Care to add this as an answer perhaps? – Gibraltar

Answer 1

As an experiment I tried out the two different methods to compare them.

I found that after some tuning the binary version was about 3X faster than the 50 col version.

This scenario is very specific and my test only tested something very specific. Any deviation from my test-setup will have an impact on the result.

How the test was made

For the 50 col version I had 50 nullable float columns which I populated all with float.MaxValue.

For the binary version I had a single column. The value for the column was constructed from a string of 50x float.MaxValue + "|", all concatenated into a single long string. The string was then converted to byte[] to be stored in the table.

Both tables were heaps with no indexes or constraints.

My test code can be found here https://github.com/PeterHenell/binaryBulkInsertComparison

I ran the tests on SQL Server 2014 Developer Edition on a 6 Core workstation with SSD drives.

Interlocutrix

Thanks a lot, this answers my question. I will probably repeat the test with a slightly more efficient encoding (i.e. nullable float32 must fit in 33 bits, probably slightly lower with some compression, while simple string.Format with an ascii char delimiter will usually result in more than 10 bytes of data). Meaning that I can probably fit several rows of data into a single blob, so I expect ~10x improvement in that case. – Gibraltar

Answer 2

This is a bad idea. Having 50 columns of 4 bytes vs. having one column of 200 bytes obliterates any hope of optimizing the query for any of those 50 columns. To begin, from a 'classic' SQL Server pov:

• You eliminate push-down predicates and scan time filtering
• You eliminate indexing possibilities
• You eliminate data purity checks (especially important for floats, since not all bit patterns make valid floats!)
• You eliminate column statistics based cost optimizations

As you go more 'modern' and start considering SQL Server newer options:

• You eliminate in-row compression options
• You eliminate columnar storage options
• You eliminate in-memory storage optimizations

All these without even considering the pain you inflict on your fellow humans trying to query the data.

the issue is that the table is rather denormalized, and not all columns are filled with values usually, so varbinary(n) would allow me to reduce the row size in many cases. Sometimes only a single column is filled, but ~10 on average.

Then use row-compressed storage:

ALTER TABLE <your table> REBUILD PARTITION = ALL  
   WITH (DATA_COMPRESSION = ROW);

If the data is append-only and seldom updated/deleted and most queries are analytical, then even better use columnstores. Since SQL Server 2016 SP1 columnstores are available across every SQL Server edition.

Behring

Thanks, your remarks are exactly what I expected (basically what I read on the internet). But the thing is that I will only query by the first column (Timestamp), never by any other column, and always read each row at once (I have updated my question). So I know what I eliminate (basically, things which I don't need: possibility to index and keep stats on these 50 columns), but I wanted to see what I gain in terms of performance. – Gibraltar

1. Read rusanu.com/2014/02/24/how-to-analyse-sql-server-performance. 2. Identify your botleneck. 3. Address the problem. – Behring

Answer 3

I know the purists will hate the approach, but if your use case really is that limited then of course the single column style will be faster.

But, to be honest, if its that simple, then you would get far higher performance writing/querying a single file with a simple fixed width format directly, then any database can offer. Features like backups, security etc can all be done at the file level.

There is a jdbc csv file driver for example that can make your file look like a SQL connection if you still want a SQL like API. I'm sure others exist for whatever you are programming in. Cue gasps of horror!

Note also that there are many database technologies out there. Some are optimized rather better for insert performance than others.

It sounds like you have something like sensor data, and your columns are actually more like a matrix, perhaps with spatial meaning. In that case you might want to look at database technologies that code explicitly for that data format. SciDB is one such database. It was partly designed by the same guy who worked on Vertica, so I expect it has pretty good ingestion performance.

Herisau

Sqlite is a better option than a CSV file or fixed-width file. – Prandial

But also the original 50 fields may be used in SQL Server, and move the table to memory storage IFF is a temporary summary statistic (which it appears to be) - and you also get the benefit of reducing wear on SSD. – Prandial