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,