Re: drawbacks of Saving documents to Sql server

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

"Steve" wrote:

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

"Bryan Phillips" <bphillips@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:%23u6CLYa8GHA.3280@xxxxxxxxxxxxxxxxxxxxxxx
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




"sameer" <sameer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EC07B737-4741-4D74-B581-14FE6C758471@xxxxxxxxxxxxx:

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



.

Relevant Pages

  • Re: why>?
    ... of ADP and OLAP just as you'd use them with SQL Server? ... Database as file system ain't new. ... talk of WinFS by years. ... Yup, database as storage subsystem. ...
    (microsoft.public.excel)
  • Re: drawbacks of Saving documents to Sql server
    ... Retrieving the files from the file system is always faster but not necessarily better. ... 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... ... SQL Server 2005 performs better than SQL Server 2000 hands down. ... that microsft is gong more towars saving everthing in sql server. ...
    (microsoft.public.dotnet.framework)
  • Re: file system permissions and cross-database certificates?
    ... one database to another within the same instance? ... is the use of the file system to BACKUP CERTIFICATE TO ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: drawbacks of Saving documents to Sql server
    ... by anychance know or have the code to retrive the documents from the database ... retriving documents, do you think u might be able to point me to online ... Writing random documents to SQL was slower than the file system. ... SQL Server 2005 performs better than SQL Server 2000 hands down. ...
    (microsoft.public.dotnet.framework)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)