Storing File in Table

Question

Storing File in Table

Rechana Rajan

SSCertifiable

Points: 7709
More actions
June 19, 2017 at 2:21 am

#343285

Which is the best option to save files in a table.
Varbinary(max) or Varbinary (Max) Filestream or Filetable. Max file size expect is 1GB and same time we expect 50 such files.
I believe all 3 options make sure the data including the files are available in SQL Server backup file.
Now we have varbinary (max) ,file size is max 7MB and it take almost 3 minutes to download file from front end application. Will get any performance improvement if I change ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Rechana Rajan - Monday, June 19, 2017 2:21 AM
Which is the best option to save files in a table.
Varbinary(max) or Varbinary (Max) Filestream or Filetable. Max file size expect is 1GB and same time we expect 50 such files.
I believe all 3 options make sure the data including the files are available in SQL Server backup file.
Now we have varbinary (max) ,file size is max 7MB and it take almost 3 minutes to download file from front end application. Will get any performance improvement if I change ?

Each mechanism has it's pros and cons. You have not covered nearly enough details about your needs for anyone to offer guidance.

I will say quite emphatically however that I ALWAYS try to make this work by storing such data OUTSIDE SQL SERVER!!!! If you access 50GB worth of blobs from a table in SQL Server via a TSQL query, you are doing the following:

1) CRUSHING your SQL Server IO subsystem

2) Forcing HUGE amounts of active, hot data out of the buffer pool, CRUSHING ALL application performance on the SQL Server

3) Sending all those bits through the CPU - CRUSHING that too.

4) Bundling it all up into TDS packets and sending it across the network, blowing that out too

5) Oh, and you have to maintain this stuff to when you do checkdb, backups, etc.

If this is in a file system, it will hopefully be on different IO and then NONE of this will occur on you EXPENSIVE, PRECIOUS SQL SERVER RESOURCES!!!!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Rechana Rajan SSCertifiable Points: 7709 More actions · Answer 2

TheSQLGuru - Monday, June 19, 2017 5:52 AM
Rechana Rajan - Monday, June 19, 2017 2:21 AM
Which is the best option to save files in a table.
Varbinary(max) or Varbinary (Max) Filestream or Filetable. Max file size expect is 1GB and same time we expect 50 such files.
I believe all 3 options make sure the data including the files are available in SQL Server backup file.
Now we have varbinary (max) ,file size is max 7MB and it take almost 3 minutes to download file from front end application. Will get any performance improvement if I change ?
Each mechanism has it's pros and cons. You have not covered nearly enough details about your needs for anyone to offer guidance.
I will say quite emphatically however that I ALWAYS try to make this work by storing such data OUTSIDE SQL SERVER!!!! If you access 50GB worth of blobs from a table in SQL Server via a TSQL query, you are doing the following:
1) CRUSHING your SQL Server IO subsystem
2) Forcing HUGE amounts of active, hot data out of the buffer pool, CRUSHING ALL application performance on the SQL Server
3) Sending all those bits through the CPU - CRUSHING that too.
4) Bundling it all up into TDS packets and sending it across the network, blowing that out too
5) Oh, and you have to maintain this stuff to when you do checkdb, backups, etc.
If this is in a file system, it will hopefully be on different IO and then NONE of this will occur on you EXPENSIVE, PRECIOUS SQL SERVER RESOURCES!!!!!

Thanks Kevin for the detailed post. Really appreciate it.

We are planning to go with Filetable and before that ,experts advise is always helpful . Please let know what more information you want from my side so that I can get more understanding.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

There are WAY too many variables and needs for such a project to cover them all in a forum post and be able to offer sound and well-reasoned advice. Sorry. 🙁
Here is a pretty good reference for filetable though. He is a SQL Server MVP and may be willing to give you some pointers or additional links.

FileTable Session from Matija Lah

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service