« Home | LINQ » | New Methodolgy for Explaining Software Engineering... » | PInvoke and IJW » | DBMS Blogs Moved » 

Wednesday, June 28, 2006 

Microsoft JET Engine

The Microsoft JET engine is one of the oldest database engines that is used till today to handle simple operations on simple databases such as Microsoft Access.
I know it is a simple database engine, but I didn't think that it is this bad.
I am working on a small C# business application for a company. The application needs simple database operations, so I decided to use Microsoft SQL server express as my database management system, but after designing the database I found that the computer on which the application is to be deployed has low memory resources, and SQL server express takes about 80 MB of memory. Another important reason was the backup operation. The people who were to work on this application have very low technical level, so backing up and restoring the database from SQL server management studio would be hard for them, while for Access database you only have to copy and replace the mdb file. For these reasons I switched to Access database. Every thing was going for a while, but after some coding and adding some more functionality to the program, I got three great surprises.

1- There's no transactions in the JET engine, it only do database locks when you ask for a transaction. When database locks are done, you get exceptions when you try to read and write the same record during the same transaction whatever isolation level you are using!

2-No multiple connections is allowed. if two objects (on the same thread) open a connection to the database at the same time, they are considered two users, and when each one starts a query on a table, the table is locked for this user. The other user (object) cannot read or write in the same table.

3-Manual processing and filtering of data inside C# code is faster than using the database engine.
The data that I store in the database are a copy of the file - folder hierarchy of a CD ROM, so I have folders, each folder can have child folders, and each folder can have files too.
When reading data from the database to load this folder - file hierarchy in a TreeView using multiple queries, the application becomes very slow. This was very clear when I added about 2000 folders and 5000 files to the database and then tried to read them recursively.

I got a mad Idea. I got all records from the database in two queries, and I have done the filtering of the data manually in the C# code. It took less than 1/8 of the time of the old technique.
I suppose this is a database engine that should perform queries faster than manual processing of data, but I was wrong!

Links to this post

Create a Link