OleDb / JET unknown performance problem

[Sai Kit Tong]

We developed an application library which uses Access 2000 database to
maintain look up data. Right now, whenever the library need data look up, it
opened the database and then closed it after it is done. We created a simple
test application utilizing the library. We observe a call in the library, in
which multiple data lookups (multiple open/closes) took place. All it took
was several hundreds of milliseconds. We then integrated it with the actual
application. The performance significant dropped significant - taking
multiple seconds to finish the same call. In the debugging window, we
observed multiple Win32 Thread exits each time the OleDbConnection.Close()
was executed. We didn't see those exits in the test application.

Could anyone help me to understand the reason?

Additional information in our case:
- All code in C#
- Connection string: "Provider=Microsoft.JET.OLEDB.4.0;Data Source =
lookup.mdb"
- Main application also have other objects using SqlConnection
- After taking out the repetitive Open/Close in the library, the performance
improved significantly in the main application.

 

[Paul Clement]

¤ We developed an application library which uses Access 2000 database to
¤ maintain look up data. Right now, whenever the library need data look up, it
¤ opened the database and then closed it after it is done. We created a simple
¤ test application utilizing the library. We observe a call in the library, in
¤ which multiple data lookups (multiple open/closes) took place. All it took
¤ was several hundreds of milliseconds. We then integrated it with the actual
¤ application. The performance significant dropped significant - taking
¤ multiple seconds to finish the same call. In the debugging window, we
¤ observed multiple Win32 Thread exits each time the OleDbConnection.Close()
¤ was executed. We didn't see those exits in the test application.
¤
¤ Could anyone help me to understand the reason?
¤
¤ Additional information in our case:
¤ - All code in C#
¤ - Connection string: "Provider=Microsoft.JET.OLEDB.4.0;Data Source =
¤ lookup.mdb"
¤ - Main application also have other objects using SqlConnection
¤ - After taking out the repetitive Open/Close in the library, the performance
¤ improved significantly in the main application.
¤

Is your database on a network resource? It could be performance overhead from reading and writing to
the corresponding .LDB file.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Alex Cachia]

Jet is not very good with multiple threads. Although you might find it
strange, try the following (if possible of course):

Pipe all your calls to a single instance of OleDbConnection;
Serialize all calls which make use of the connection, directly or
indirectly, using some synchronization mechanism like the C# lock.

If you are not able to pipe all calls to the same connection, go ahead and
create multiple connectionss, but make sure you serialize access on each
connection.

As much as possible, try not to open and close connections repeatedly. Open
the connections you need and keep them opened.

I know the serialization bit seems to defy logic, but you will get much
better performance from a Jet database like this.

-Alex.