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!

Comments

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
20 June 2007 at 13:23
Post a Comment

Popular posts from this blog

Connascence

-
I’ve just finished reading What every programmer should know about object oriented design . The first couple of sections are a bit useless really, but the 3rd section is very good. I particularly liked the section on the difference types of connascence in software. There’s some basic information about it here if you fancy a brief overview of what it is about. It’s a really good book, I recommend you read it! Should only take a day if you only read the 3rd section.
Read more

Convert absolute path to relative path

-
Today I needed to convert an absolute path to a relative path based on a specified base path. E.g. c:\a\b\c -> c:\a\b\c\d\file.txt = d\file.txt c:\a\b\c -> c:\a\file.txt = ..\..\file.txt c:\a\b\c -> c:\a\x\file.txt = ..\..\x\file.txt I am surprised there is nothing in the .NET framework so I had a hunt around and converted the code from the following URL ( http://www.vergentsoftware.com/blogs/ckinsman/default.aspx?date=2006-08-07 ) into C#.... private string RelativePath(string absolutePath, string relativeTo) { string[] absoluteDirectories = absolutePath.Split('\\'); string[] relativeDirectories = relativeTo.Split('\\'); //Get the shortest of the two paths int length = absoluteDirectories.Length < relativeDirectories.Length ? absoluteDirectories.Length : relativeDirectories.Length; //Use to determine where in the loop we exited int lastCommonRoot = -1; int i
Read more

Printing bitmaps using CPCL

-
I've had no end of grief trying to print a PCX to a Zebra Printer using the CPCL printer language. Silly me, didn't notice the EG command (expanded graphics) so there was no need to convert my BMP to a PCX and then struggle with binary data. I still had a bit of grief working out how to print using the EG command because the documentation is quite frankly crap. The expected command format is EG {WidthInBytes} {HeightInPixels} {XPos} {YPos} {Data}\r\n The printer expects a 1 bit pixel matrix. So if pixel(0, 0) is set you will set "80" in the data. If pixel(0, 0) is set and pixel (7, 0) is also set you would sent "81". Basically what you need to do is to read each set of 8 horizontal pixels and then use bit operations to create a byte value 0..255, and then output this as hex 00..FF. Here's the routine :-) public void DrawBitmap(Bitmap bmp, int xPosition, int yPosition) { if (bmp == null) throw new ArgumentNullException("bmp");
Read more