OLE DB Connections

[Corey Henschel]

I'm trying to optimize a vb.net connection to an Access 2003 backend.
The db is installed on a mapped network drive. There will be 5
concurrent users, however the performance issue happens regardless of
the number of users. We are using an oledb connection to connect to
the db. I've run the performance analyzer on the db and can not find
anything that needs to be done in the db itself. The Jet update has
been installed on the client and the server. The connection to the db
is opened and closed each time a sql statement is run. I've also
added the string OLE DB Services=-4; to the connection string used to
open the connection.

The performance issue is severe, taking 3 seconds to retrieve a single
record from the db which is selected using an indexed field. I've
researched msdn and googled this extensively but can not find anything
that helps. The issue is the same when the application and database
are installed on a completely different network, so that would tend to
rule out any network traffic issues.

Can anyone point me in the right direction or provide some insight as
to what's causing the performance issue?

Thanks in Advance,

Corey

[David Schofield]

On 14 Jan 2005 12:55:47 -0800, ch*****@telus.net (Corey Henschel)
wrote:

I'm trying to optimize a vb.net connection to an Access 2003 backend.
The db is installed on a mapped network drive. There will be 5
concurrent users, however the performance issue happens regardless of
the number of users. We are using an oledb connection to connect to
the db. I've run the performance analyzer on the db and can not find
anything that needs to be done in the db itself. The Jet update has
been installed on the client and the server. The connection to the db
is opened and closed each time a sql statement is run. I've also
added the string OLE DB Services=-4; to the connection string used to
open the connection.

The performance issue is severe, taking 3 seconds to retrieve a single
record from the db which is selected using an indexed field. I've
researched msdn and googled this extensively but can not find anything
that helps. The issue is the same when the application and database
are installed on a completely different network, so that would tend to
rule out any network traffic issues.

Can anyone point me in the right direction or provide some insight as
to what's causing the performance issue?

Thanks in Advance,

Corey

Hi
I should start by keeping an open connection or taking one from a pool
David

[Ruben Baumann]

When using JET as a backend, it is recommended to open at least one table,
and keep it open for the life of the session. Tremendous overhead in
opening especially the Lock MDB over a network. You might open a small(1
record) table at the beginning of your session that you never close until
the app is closed. This should speed things up quite a bit.

For more info, look at Tony Toews http://www.granite.ab.ca/accsmstr.htm,
Another good reference is The Access Developer Handbook (whatever version of
Access you have).

HTH
Ruben B

"Corey Henschel" <ch*****@telus.net> wrote in message
news:4d**************************@posting.google.c om...

I'm trying to optimize a vb.net connection to an Access 2003 backend.
The db is installed on a mapped network drive. There will be 5
concurrent users, however the performance issue happens regardless of
the number of users. We are using an oledb connection to connect to
the db. I've run the performance analyzer on the db and can not find
anything that needs to be done in the db itself. The Jet update has
been installed on the client and the server. The connection to the db
is opened and closed each time a sql statement is run. I've also
added the string OLE DB Services=-4; to the connection string used to
open the connection.

The performance issue is severe, taking 3 seconds to retrieve a single
record from the db which is selected using an indexed field. I've
researched msdn and googled this extensively but can not find anything
that helps. The issue is the same when the application and database
are installed on a completely different network, so that would tend to
rule out any network traffic issues.

Can anyone point me in the right direction or provide some insight as
to what's causing the performance issue?

Thanks in Advance,

Corey