drawbacks of Saving documents to Sql server

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

..Net 1.1
Sql server 2000

i am working on a document managment module of my application, all i want to
do here is save the documents uplaoded by the user from my winforms over
webservices application\ webforms applicatoin to sql server 2000. I have been
told saving it on the webserver's file system might be a better way but seems
that microsft is gong more towars saving everthing in sql server. So my
question to the gurus out there is

1. does any one really have any hands on experience saving documents to sql
server 2000 and if so please tell me if u have any kind of performance issues
with this.

2. i was told that sql server is kind of a little slow in streaming out the
documents from the database, expecially bigger ones when compared to the
retriving it from the web server's file system, is this true ?

3. please tell me any other problesm that u might have faced when saving\
retriving files from the database.

4. is sql server 2005 any better in saving and retriving documents from the
database.

thanks

sameer
 
1. I have done this before with various sized Word docs. The
performance penalty was not noticeable.
2. Retrieving the files from the file system is always faster but not
necessarily better. Flat-file databases are faster than relational
databases but the features of the relational database outweigh the
performance hit.
3. The biggest problem with the files will be transport between the web
server and the web client. I broke the files into 64K chunks and
up/downloaded them via a web service. In the database I used a table to
store a "header" record with one row per file containing the filename,
filesize, number of 64K chunks, etc... Another child table was the
"detail" table with one row per file chunk. An image column was used to
store the actual chunk of binary data.
4. SQL Server 2005 performs better than SQL Server 2000 hands down.

Bryan Phillips
MCSD, MCDBA, MCSE
Blog: http://bphillips76.spaces.live.com
 
Bryan, thanks for yor reply and got some more questions for you.

1. Did you ever had to do virus scan on these documents? Clients wants me to
virus scan them before savig them to the database.

2. how about image files like Tiff which are pretty huge in size, did you
have to trim them up and bring them down to a smaller size before uploading
them, and if so how much did you resize it?

3. The user can view these documents in both winforms and webforms. And i
intend to open up the local machine software resources in both the winform
appilction and weform application but the question is how can i show this
document to the user when it is stored in the database.

thanks
Sameer
 
I too have done this.

We also wrote some tests

Retrieving random documents from SQL was faster than retrieving random
documents from the file system., it was much much faster.

Writing random documents to SQL was slower than the file system.

To get good read speeds, we wrote our own stream class that streamed the
data from SQL.

SQLs is better at locating the doc when you have millions off rows.

One more plus point is transactions, you can have a transaction for writing
you doc to SQL, so you can :

Update metadata
Write doc
Do some other SQL stuff

and its all in one transaction, so you can easily roll back.

Steve
 
I have never done virus scan on them. That was not a requirement at the
time. If you are concerned about large files, you can use the a library
like SharpZipLib
(http://www.icsharpcode.com/OpenSource/SharpZipLib/Default.aspx) to
compress them before upload. Tiff images can usually compress to 1/10
of the original size.

To show the image in the web application, you will need to create an
HttpHandler and pick a file extension like .mygif and configure the
web.config file to associate the .mygif extension to the new HttpHandler
you create. Then, any requests for URL's that end in that file
extension will be processed by your new HttpHandler class. Since the
file is really just a binary array divided into big chunks, you can get
the chunks from the database as byte arrays and use Response.BinaryWrite
for the arrays. The browser won't know the difference.

You can do the same with the WinForms application. Either get the
chunks and assemble them on the client side, or use the HttpHandler
method. In other words, just get the browser to download the file the
same way the browser does.

Bryan Phillips
MCSD, MCDBA, MCSE
Blog: http://bphillips76.spaces.live.com
 
thanks for the reply guys,

Bryan : i am already using Gzip compressing, it really does help. could you
by anychance know or have the code to retrive the documents from the database
as you have mentioned ?

Steve : about the code that you have mentioned which is more optmized in
retriving documents, do you think u might be able to point me to online
resource or share it with me.

running on a very strick deadline, any help is appreciated.

thanks
 
Bryan, another question,
why did you choose 64K byte and not any other number? what are the drawbacks
of uploading the whole document in 1 shot?

thanks
 
Back
Top