Mange Large file in the database

[Husam]

Hi EveryBody:

I have a web application made by ASP.Net 2.0 and vb.net, the purpose of this
project is to upload data from clinte machine and save these data to SQL 2005
database and when the clinte see his or her files in his member area in my
web application he can retrive his or her data just by clicking them and the
download will be start.

When I experiance my web application with a lot of people its work fine for
small data size 5 MB or less but when I delt with large data 100 MB or 1 GB
here is a problem.

The problem I face it with upload and download was SystemOut Of Memory.
Some body toled me that If I had data set with 100 mB this is design problem.

So I do not want it to be so long Is there some body can Help me and Inform
me How can I bulid databsae and data set that can Mange large file let us
assume
2 GB?

Any help will be appreciated

regard's

HusaM

 

[George Ter-Saakov]

With a big amount of data that is uploaded to SQL server from the Web you
will face 2 problems...

1. ASP.NET does not allow to upload files bigger than 4 megabytes by
default.

Solution: Check out this article for details
http://msdn2.microsoft.com/en-us/library/aa479405.aspx
OR you might need to use non .NET way to do it with some third party
component.

2. MS SQL can handle huge amount of data. The problem to get that data in
there (into database). The problem is transaction log. It grows enormously
when you do repeated INSERT many thousand times. And MS SQL needs to
reallocate it constantly which is very costly and time consuming...

Solution: You need to do research on "bulk copy", specify correct BACKUP
model for your database.
You can use BULK INSERT or bcp.exe command.
bcp.exe allows you to have SQL server on separate machine than uploaded
file.

The way I did it is that I would take a file, make csv file out of it with
predefined columns and run bcp.exe to get it into SQL server.


PS: I heard that .NET 3.0 (or 3.5) supports bulk operation so you can do it
yourself instead of using bcp.exe

George.

 

[bruce barker]

you have two issues upload and download. we will cover download first because
its simple.

1) to download, you page shoudl turn off bufferering and use the sqlserver
getbytes with a buffer (say 2k) or readtext function to read the data in
chunks, then response.write to write it.

2) upload is a different problem. the builtin control load the uploaded file
into memory (thus your out of memory message). you need to replace it with a
new upload control, that either writes to a temp file (many 3rd party do
this), or streams it the database.

unless you know how to write c/c++ isapi filters (or on iis7.0) you will
probably have to buy a 3rd party upload control.

-- bruce (sqlwork.com)

 

[Jeff Dillon]

Have you considered writing the file to the file system, and only storing
the path/filename in the db?

Jeff

 

[Husam]

Thanks for all of you guys:

but mr. Jeff actully I did not try to use your way that I saved the path of
file in the data base and the file in the file system.

Can you tell me how can I do so, and dose it work with large file?

regrad's

 

[Jeff Dillon]

Copy the file to the server through file upload, and save the path in the
database.