Upload - to database, or not to database? Article

Upload - to database, or not to database?

Performance tests of several methods to store files on server-side. Save file to disk, store to database.

To database or not to database? What to do with uploaded files on server-side? This article contains performance tests of several store-file methods in server-side ASP.

We test store files to disk, to MDB file using ADO recordset, to MS SQL 2000 using ADO recordset and to MS SQL 2000 using SQL UPDATETEXT command.

1. Store files to the disk.

This is the first method we can use. File is stored to the destination directory:

'Code 1
Server.ScriptTimeout = 120

Dim Form: Set Form = Server.CreateObject("ScriptUtils.ASPForm")
If Form.State = 0 Then
 Form.Files("MyFile").Save "f:\Uploads\UpFile"
End If

Field.Save
	Consumed
FileSize [MB]	Memory [MB]	Processor [ms]
5	9.2	94
10	10.3	172
15	10.3	281
20	10.3	328
25	10.3	531
30	10.3	547
35	10.3	781
40	10.3	734
45	10.3	859
50	10.3	1 031

Save method consumes constant amount of memory (block size is set to 1MB). Processor time (save time) depends linearly on file length.

2a. Store files to MDB database using recordset, without chunks

Connection to MDB database was created using JetOLEDB provider (not ODBC):

'Code 2
Function GetConnection()
 Dim Conn: Set Conn = CreateObject("ADODB.Connection")
 Conn.Provider = "Microsoft.Jet.OLEDB.4.0"
 Conn.Open "Data Source=e:\upload2000.mdb"
 Conn.CommandTimeout = 1800
 Set GetConnection = Conn
End Function

Then we use simple code to store uploaded file in database:

'Code 3
 'Open dynamic recordset, table Upload
 RS.Open "Upload", Conn, 2, 2

 'Add a new record
 RS.AddNew
 'update info fields in "Upload" table
 '.... 
 'Add file from source field 'DBFile' to table field 'Data'
 RS("Data") = Form("DBFile").ByteArray
 RS.Update

MDB, RS("Data") = ...
	Consumed
FileSize [MB]	Memory [MB]	Processor [ms]
1	10.9	187
5	23.5	265
10	39.2	312
15	67.9	468
20	83.3	578
25	98.7	671
30	114.1	703
35	129.5	937
40	129.8	921
45	140.2	875
50	138.0	625

Memory consumption grows linearly, up to 140 MB, as consumed processor time. Then JetOLEDB provider stores data using blocks and some consumed time was moved to System process.

2b. Store files to MDB database using recordset + AppendChunk

We have the same connection and recordset, but we change 'RS("Data") = Form("DBFile").ByteArray' by the next code:

'Code 4
'Set block size to 64kB
Const BlockSize = &H10000

'Process source data using blocks
Dim BlockCounter, DataBlock
For BlockCounter = 0 To Form("DBFile").Length Step BlockSize
 'Get a part of source data
 DataBlock = Form("DBFile").ByteArray(BlockCounter, BlockSize)

 'Add a part of source data to the field
 RS("Data").AppendChunk DataBlock
Next 'BlockCounter

RS.AppendChunk - JetOLEDB
	Consumed
FileSize [MB]	Memory [MB]	Processor [ms]
5	13.8	296
6	14.9	312
7	16.0	375
8	17.0	390
9	18.1	406
10	19.2	468
15	22.6	687
20	23.9	1 000
25	23.9	1 250
30	23.9	1 609
35	23.9	1 859
40	23.9	2 125
45	23.9	2 312
50	23.9	2 546

Memory consumption grows linearly, up to 20MB and then stops at 23.9MB. Consumed processor time grows also linearly.

It looks like Microsoft.Jet.OLEDB.4.0 provider works very well with chunks. But remember, that physical limit of MDB database is 2GB (Jet 4.0, Access 2000) or 1GB (Jet 3.51, Access 97). So there is no good idea to store big files in MDB database.

3a. Store files to MS SQL 2000 database using recordset

I have done the same tests with MS SQL 2000 , OLEDB and ODBC providers.

'Code 5
'open connection for SQLOLEDB
Function GetConnection()
 Dim Conn: Set Conn = CreateObject("ADODB.Connection")
 Conn.Provider = "SQLOLEDB"
 Conn.Open "Data Source=Muj;Database=Upload", "sa", ""
 Set GetConnection = Conn
End Function

'open connection for MSDASQL
Function GetConnection()
 Dim Conn: Set Conn = CreateObject("ADODB.Connection")
 Conn.Open "DSN=Upload", "sa", ""
 Set GetConnection = Conn
End Function

SQL 2000, RS("Data") = ...
	Consumed
FileSize [MB]	Memory [MB]	Processor [ms]
1	8.1	234
5	26.6	343
10	47.1	234
15	67.6	315
20	88.1	406
25	108.7	515
30	129.2	616
35	149.7	722
40	170.3	856
45	190.6	984
50	211.4	1 111

I tested these two connections and get the same performance results. The results are in the table. The memory consumption grows linearly, but SQL provider takes 4Bytes to send one Byte to SQL! And MS SQL takes also some bytes.

How it probably works? For example, if you have 10MB file. Recordset takes source byte array (10MB), allocates a new array (second 10MB), then converts this data to HexString (2*10MB), sends this HexString as ODBC command to MS SQL (+2*10MB in MS SQL), SQL converts this data to binary (+10MB in MS SQL) and then are the data stored in a database file ... Terrible.

3b. Store files to MS SQL 2000 database using recordset + AppendChunk

I test next idea - use AppendChunk to store data. This method works well for MDB, it will works also with MS SQL, may be. MS says in its documentation (AppendChunk method):
"Use the AppendChunk method on a Field or Parameter object to fill it with long binary or character data. In situations where system memory is limited, you can use the AppendChunk method to manipulate long values in portions rather than in their entirety."

RS.AppendChunk - SQLOLEDB
	Consumed
FileSize [MB]	Memory [MB]	Processor [ms]
5	16.9	484
10	27.3	1 328
15	37.7	2 312
20	48.0	3 921
25	58.2	5 578
30	68.5	7 750
35	78.8	10 093
40	88.2	12 546
45	97.9	15 671
50	107.3	19 568

So let's go on it. I take the same code as with MDB (Code no. 4) and tested it with MS SQL 2000. And I must say - great idea, very bad implementation. As you can see, this code takes a little bit les memory than the code without chunks - only two bytes per one source file. But it has another big problem - consumed processor time depends by square on data length!

And once again - how this code probably works? For 1MB blocks/50MB file: First AppendChunk allocates 1MB memory. Second allocates 2MB, copies 1MB data from first chunk, copies 1MB data from second chunk and deallocates first chunk. Third AppendChunk allocates 3MB, copies 2MB from previous chunks, copies 1MB data from third chunk and deallocates 2MB from previous chunks ...

And at the last? 50MB of memory is converted to HexString (100MB) this HexString is sent as SQL command to MS SQL (+100MB in SQL), SQL converts this data to binary and stores this data to data file.

4. So what to do with MS SQL 2000?

Recordset With/Without AppendChunk is unusable to store large binary data to MS SQL (>10th of MB). We have to use native MS SQL methods - UPDATETEXT command.

I used the same code with Recordset to store extra upload field - client IP, description, ... As a result of it I have UploadID - primary key of row, which has zero length of binary field named 'Data'

'Code 6
'Set block size to 1MB
Const BlockSize = &H100000

'Process source data using blocks
Dim BlockCounter, HexDataBlock
For BlockCounter = 0 To Form("DBFile").Length Step BlockSize
 'Get a part of source data as a Hex string
 HexDataBlock = Form("DBFile").HexString(BlockCounter, BlockSize)

 'Add a part of source data to the field using SQL UPDATETEXT command
 SQL = "DECLARE @dataptr binary(16)" & vbCrLf
 SQL = SQL & "SELECT @dataptr = TEXTPTR(Data)"
 SQL = SQL & " FROM Upload WHERE UploadID=" & UploadID & vbCrLf
 SQL = SQL & "UPDATETEXT Upload.Data @dataptr " & BlockCounter & " NULL 0x" & HexDataBlock
 'Execute prepared SQL command
 Conn.Execute SQL, 0, adCmdText + adExecuteNoRecords
Next 'BlockCounter

HexString - SQLOLEDB
	Consumed
FileSize [MB]	Memory [MB]	Processor [ms]
5	27.4	5 078
10	27.4	9 953
15	27.4	14 734
20	27.4	20 093
25	27.4	24 312
30	27.4	29 125
35	27.4	33 609
40	27.4	38 484
45	27.4	43 437
50	27.4	48 390

And how this code works? For example 100MB file. Uploaded data are stored on the disk. We add another fields of upload record to the database (some bytes/kB).
Then we process source binary data using blocks. HexString reads 1MB of source data from the disk, converts this data to HexString (+4MB - Unicode HexString), creates UPDATETEXT SQL (+4MB) and sends this SQL command to MS SQL. MS SQL gets this string (+10MB ADO +2MB in SQL) converts it to binary data (+1MB) and the data are stored to the database file.

This code takes constant amount of memory - the amount of memory depends on source block size. For 1MB block - VBS code takes 8MB of memory; ADO Execute method takes 10MB (I do not know why such big amount).

Although this code takes a big amount of processor time, I test it to store files with up to 512MB of size.

Other links for Upload - to database, or not to database?

ScriptUtils

     Huge ASP upload is easy to use, hi-performance ASP file upload component with progress bar indicator. This component lets you upload multiple files with size up to 2GB to a disk or a database along with another form fields. Huge ASP file upload is a most featured upload component on a market with competitive price and a great performance.

     The software has also a free version of asp upload with progress, called Pure asp upload, written in plain VBS, without components (so you do not need to install anything on server).

     This installation package contains also ScriptUtilities library. Script Utilities lets you create hi-performance log files, works with binary data, you can download multiple files with zip/arj compression, work with INI files and much more with the ASP utility.