Sql-server – Splitting a table with large columns for performance

sql server

I am working on a system that stores images in its database (SQL Server 2014) using the varbinary datatype. The table looks like this (extra columns removed for brevity):

| ImageDataId | DateUploaded |
|   1         | 1-1-2001     |
|   2         | 1-1-2001     |
|   3         | 1-1-2001     |

There is also a seperate table for the image itself.

| ImageId | ImageFile    |
|   1     | [byte array] |    
|   2     | [byte array] |
|   3     | [byte array] |

The database was made with Entity Framework Code First, so the ImageId key in the second table is a foreign key reference to the Id in the first table.

I was told these tables were designed to improve performance. If I only need ImageData, but no image, querying the first table without any imagedata in it would be fast. I'm not an experienced DBA, so I may be missing something, but wouldn't a query be just as fast if I merge both tables:

| ImageDataId | DateUploaded | ImageFile   |
|   1         | 1-1-2001     | [byteArray] |
|   2         | 1-1-2001     | [byteArray] |
|   3         | 1-1-2001     | [byteArray] |

And then do a SELECT without the ImageFile column? Or is this query:

SELECT DateUploaded 
FROM Images

Somehow faster with split tables?

Best Answer

Generally this can make sense, BUT: you have first to make sure the data is in the table.

If you use large table fields (varbinary(max)) then the data is actually not stored in the table but in separate pages anyway. If the original table as as thin as you indicate (id, date upload, not a lot more fields) Then this is a fallacy, with one exception: EF will gladly load all the data on object access - which makes acess slow thanks to EF inefficiency.

You can avoid that by not using EF for accessing the file field. That does make a TON of sense because you may never want to load the whole file into memory anyway. You want to be able to access the file in a loop reading max x bytes - so you can stream them off.

Welcome to the limitations of EF and the even worse limitations of code first.

Related Solutions

Sql-server – Splitting a large table to improve performance

I personally would go with your first option. If you use a `DELETE dbo.p1 OUTPUT INTO dbo.p2' pattern to move the data there is not a lot that can fail. Moving 250K x 3 rows that way in the timeframe you have should not be a problem either if you do it in batches with around 10K rows.

The advantage I see over the more common calendar based partitioning is that your "partitions" stay the same size. With the amount of data you are dealing with a calendar base partitioning approach would work very well in the beginning of the month and potentially significantly slower at the end of the month.

I have written an article together with Kalen Delany about the advantages of the "moving data from partition to partition as it ages" approach a while back in sqlmag: http://sqlmag.com/database-administration/using-table-partitions-archive-old-data-oltp-environments

That article is using the enterprise-only build-in partitioning feature, but you can implement it using handmade multi-table-partitioning too.

I would try to place the different tables (partitions) on separate drives. Because your data in the older partitions changes only during downtime, you could still mark those filegroups readonly during the day. Alternatively you can use TRANSACTION ISOLATION READ UNCOMMITTED or READ COMMITTED SNAPSHOTisolation. The later should speed things up on the "current" partition too, assuming you really do not ever update data. But even with updates it might help. Either way, make sure you test the performance in your environment. (In any event, do not use UNCOMMITTED in the active table/partition as you read queries might end up seeing half-written rows, depending on the data types you are using.)

Sql-server – Full Text Index and database structure

Did you try to replace the insert..union statements with separate insert statements?

For example:

insert into @containsTable select [key],[rank] from ContainsTable(File2010,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File2000,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File1990,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File1980,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File1970,Content,@keywords)

It could be as simple as the increased processing cost of union plus the increased memory usage from having 5 processes open at the same time to use them on that insert, slowing down your UDF.

(Also, since you're querying from - supposedly - unrelated data tables, the 'distinct' part of union should be unnecessary.)

Best Answer

Related Solutions

Sql-server – Splitting a large table to improve performance

Sql-server – Full Text Index and database structure

Related Question