Retrieving Large Videos

Question

Retrieving Large Videos

sumnerdu

SSC Rookie

Points: 48
More actions
July 1, 2010 at 8:08 am

#141151

We are storing some somewhat large (15-20MB) videos files in an SQL 2005 table within a varbinary(max) type column. The video files are used and displayed in our CMS web application which is written in Coldfusion. Retrieving the videos with a standard SQL select statement through Coldfusion is very slow.
Does anyone know if there is a way to speed up the retrieval of the data through SQL Server or T-SQL? Kind of reaching here, but is there a way to break up the binary data and then concatenate it back together (yuck!)??
I appreciate all suggestions.
Thanks ahead of time!

Viewing 7 posts - 1 through 7 (of 7 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

You can use SUBSTRING function, it works on varbinary datatype.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

sumnerdu SSC Rookie Points: 48 More actions · Answer 2

Thanks Eugene! I may try that if worse comes to worst, I would prefer not to break up the data if I can avoid it though.

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 3

I would flat out not recommend using SQL Server to store files. It does a lousy job of it, as you're finding. I'd store the files on the file system and simply store links to the file in SQL Server. This is how many of the document management systems I've used have done the job. If you look into SQL Server 2008 you can try the FileStream object which is sort of the best of both worlds. It stores the file on the file system, but maintains a link with data integrity and does all this through TSQL. It's quite a bit faster.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Steve Jones - SSC Editor SSC Guru Points: 738663 More actions · Answer 4

I'm with Grant. Lots of testing has shown that above 1MB files, the file system is much faster. It does complicated backup/restore/sync with the db, but you can write a job to check and look for missing files and notify someone.

If you can set up a small web server, this can stream things out (and cache them) very easily.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

Just remember, to use FileStream in SQL2008 your system must use Windows Authentication when connecting to SQLServer, but I beleive it already does :-D.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

sumnerdu SSC Rookie Points: 48 More actions · Answer 6

Thanks a bunch everyone, definitely going the file system route until we can upgrade to 2008.