Re: drawbacks of Saving documents to Sql server

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: How do i move an SQLDatabase to another location?
    ... I went to my clients and installed SQL Server ... Express and copied my database to the same location it was in while i was ... knowing that i can bring a database with me and install ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: New to Windows CE Development - Have some questions
    ... validating against the main database, or querying the database during data ... iterate through the SQL CE table and create new entries in the ... SQL Server database that correspond. ... >>> not connected to the Enterprise. ...
    (microsoft.public.windowsce.app.development)
  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)
  • RE: SBS 2003 Unable to connect to database STS_Config
    ... MSDE to store SharePoint uses the MSSQL$SharePoint database, ... SharePoint named instance to full SQL. ... You cannot upgrade the Monitoring instance to SQL ... What I see is the SQL server group and under ...
    (microsoft.public.windows.server.sbs)
  • Re: Using Access 2007 to connect to SQL
    ... MDB database file format, ODBC linked tables, passthrough queries ... probably the easiest way of retrieving data from a SQL-Server ... I'm a total newbie w MS SQL Server. ...
    (microsoft.public.access.adp.sqlserver)