Upload - to database, or not to database? Article

Member of  ScriptUtils | Changes | Purchase | Download

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]

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]

10.9 

187 

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]

13.8 

296 

14.9 

312 

16.0 

375 

17.0 

390 

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]

8.1 

234 

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]

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]

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? article

ScriptUtils.FAQ for Huge ASP file upload and scriptUtilities

Frequently asked questions about ScriptUtilities, Pure-ASP and Huge-ASP upload functionality.

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 4GB 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.

© 1996 - 2011 Antonin Foller, Motobit Software | About, Contacts | e-mail: info@pstruh.cz


Other Motobit links:   IISTracer, real-time IIS monitor   ASP file upload - upload files to ASP. 
ActiveX/VBSScript registry editor  ActiveX NT User account manager  Export MDB/DBF from ASP Active LogFile  Email export  ActiveX/ASP Scripting Dictionary object