2007-01-11

SQL Server amazes me!

Well, SQL Server amazes me, but not because I am impressed!

Today I wrote an application that does the following:
01) Find all ZIP files in a specific folder
02) Open each ZIP file in turn
03) Extract an XML file from the ZIP into an array of bytes
04) Insert that data into a table

There are 1,978 files in this folder and the XML within each ZIP file is around 2MB in size.

I ran this app and was really surprised at how soon my PC started to crawl, it was so slow that it was taking over 10 seconds to switch between MSN and a Skype text-chat window. So I decided to monitor the process.....

Importing the zip data into SQL Server took a total of 19 minutes and 15 seconds (this includes unzip time). What concerns me is that SQL Server's RAM usage went up to 830MB at its peak, this is what was crippling my PC. Twenty minutes after my app had finished SqlSevr.exe was still holding over 700MB of RAM, I then restarted my PC. SQL Server was using more RAM than I had available and was forcing my PC to use virtual memory!

So I decided to try FireBird 2.0

Importing the zip data into FB took a total of 15 minutes and 15 seconds (again including unzip time). RAM usage for FB started at 21MB but dropped to 16MB at home point within the first 5 minutes. It's memory consumption remained at 16MB.

In both cases I was creating + committing a transaction around each INSERT command. In fact it was the exact same code, I just did a search + replace from Sql to Fb.

Amazing!

1 comment:

shilpi said...

Hey I am trying to do the same thing -
Locating Zip files in a folder, unzip them and bulk import data from these files.

But I do not know how to do that, could you be able to help me on that

My mail id - shilpi14@gmail.com