File Upload Issues

Last Post 24 Jan 2007 04:38 AM by pjmcbride. 7 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
pjmcbride
New Member
New Member

--
23 Jan 2007 08:54 AM
I have a table, Invoices, which contains a bunch of invoice information (Company, Month, Year, etc...), plus an Image field for storing an .snp (snapshot) file of the actual invoice itself. The invoices reside on our file server, but in order to make them available to our customers online, we are copying them to a database which is accessible on the other side of our firewall.

To copy the invoices requires a two step process:

First, the Invoices table is populated with all of the information for each invoice. At the initial load, this is 14,000 invoices.

Second, a command-line application reads all of the invoice records which have a NULL value for the InvoiceImage field, retrieves the file from the file stream, and loads the image into the database, one file at a time.

So, when I run the query to poulate the records, it runs just fine. Then I start the command line process, and it works really quicky: about .5 seconds per file. But as time goes on, the time per upload of each file gets bigger and bigger. After uploading a few hundred files, the time for each invoice is .75 seconds. After a thousand, the time for file is 1.2 seconds.

Currently, I have uploaded 11,000 files, and the time per file is 15.2 seconds!

If I stop and start the process, it still takes just as long per file. If I stop, and come back two days later, or even reboot the server, it still takes 15 seconds per file.

However, if I TRUNCATE the table, and repopulate it, and start the upload process all over again, it starts out at .5 seconds per file, and gradually increases all over again.

I assume that the increase is due to space being reallocated as invoices are added. Is there anything I can do to correct for this before beginning the upload process?

The files are between 10 and 16 KB per invoice.

Thanks in advance,
Paul McBride
pjmcbride
New Member
New Member

--
24 Jan 2007 01:44 AM
Russell:

Thanks for the quick response. I tried rebuilding indexes, but to no avail.

So then is what I'm trying to accomplish pretty much impossible? How do people store large numbers of files in SQL Server? Or do they always just use the file system, instead?

Thanks in advance,
Paul
JHunter
New Member
New Member

--
24 Jan 2007 02:07 AM
Hi Paul,

I assume you are using the IMAGE data type (and that your are using SQL 2000).

IMAGE (and other BLOB types) are not stored along side the row data. If you define the TEXTIMAGE_ON option on the CREATE TABLE statement you can instruct SQL Server to store the BLOB data on a completely different filegroup, hence separating the IO.

Could you give us some indication of "how" you are inserting the image data? Is this operation in a transaction per image, or a single transaction for the whole job?

If you are in a web based environment, I'd suggest only storing a URL to the image within your database. I'd typically store images/pdf etc on a NAS that is accessible by the web servers.

Jamie
pjmcbride
New Member
New Member

--
24 Jan 2007 04:25 AM
Thank you, Jamie.

I am uploading these as a single image per transaction. I will look into the option of having them on a different filegroup.

It is a web environment; we were storing them in a database, as we were afraid that, as they are invoices and thus somewhat sensitive, storing them in the database would be more secure than storing in a directory out on the web server. Do you think you would agree with this assessment?

Thank you,
Paul

pjmcbride
New Member
New Member

--
24 Jan 2007 04:38 AM
Also, would migrating this database to SQL 2005 help to resolve this issue? Because that is an option that is on the table...
pjmcbride
New Member
New Member

--
24 Jan 2007 04:38 AM
Also, would migrating this database to SQL 2005 help to resolve this issue? Because that is an option that is on the table...
JHunter
New Member
New Member

--
24 Jan 2007 05:46 AM
In answer to your security question, I don't believe one is more or less secure than the other. If, for example, your database and storage are behind a firewall, and only requests from the webserver can access these servers, the level of security for both solutions is down to that implemented on the web server.

When you insert the image, are you using a ADO.Net SQLCommand object?

Do you know if it is the IsNull check or the insert part of the program that is slowing down?

If possible, 2005 should definately be considered, it just offers so much more.

Whether or not it will fix your problem - can't say yet...

Jamie
pjmcbride
New Member
New Member

--
25 Jan 2007 04:42 AM
Yes, I am using an ADO.Net SQL Command object in the upload process.

The insert itself is the part of the process that is slowing things down; the NULL check occurs before we determine which records to go out and insert.

Is there any way to allocate space for the impending file at row insertion?

The database solution seems the most elegant here, but this is causing us fits...
You are not authorized to post a reply.

Acceptable Use Policy