JET engine becomes slow

[dnoyeB]

I am accessing the JET database engine through odbc. I have been having
this issue for years now. For some reason the JET will suddenly become
extremely slow. Something that will take 1 second takes 30 seconds.

This happens with a single client accessing the database, but takes a
while sometimes.

Currently I am using two different clients accessing two different
databases. In this case, the 2nd database becomes very slow. If I just
open one or the other, all is fine, but when I open both, this one
becomes very slow.

Is the ODBC driver shared or something? I don't see why having one
database open is affecting another. These are both very very small
databases at this point. Maybe 500 records.

I have searched for info on this but I can't find any. Most is about
concurrent access to the same database. or access to files on a network
share. I am not doing either of those...

Is there a better newsgroup to ask this question?



--
Thank you,



"Then said I, Wisdom [is] better than strength: nevertheless the poor
man's wisdom [is] despised, and his words are not heard." Ecclesiastes 9:16

 

[Albert D.Kallal]

I have searched for info on this but I can't find any.

Ok....note that on a near daily basis here, there is likely one post daily
on this subject about slow jet. And 9 out of 10 times, the answer is the
same:

Try a persistent connection, and 9 out of 10 times...this fixes this
problem.

Granted, your case is different, since your front end is not ms-access.

So, note sure...but, why not give the idea a try anyway!

When you *first* open up the shared file on a network, the JET engine tries
to get EXCLUSIVE mode access. By doing so, the data engine can perform MUCH
more freely then if it has to tippy toe over the data, and CHECK if other
users are going to over write something.

The problem is that WAY too much time is wasted building and connecting to
the mdb file. The solution in your code is to thus open up the connection
once.....KEEP it open. By *forcing* the connection to remain open, you will
find that this large startup delay should disappear, and then the LONG
process of opening, and keeping the connection will not occur (and, of note,
these long delays tend to ONLY occur when someone else has the mdb file
already open).

So, if your processing code closes all recordsets, and then re-opens them,
perhaps this is when the delay occurs? (if yes, then likely a persistent
connection can help you).

Give the above idea a try. In your startup code, try opening a table from
the back end..(any table....)...and KEEP it open up a reocrdset).......

Then, try running your other operations.

Not saying the above will work for you. (in fact, I never suggested this
when using a non ms-access front end. However, it is worth a try, and it
sure works wonders for ms-access based applications.

In fact, try it...and post back....it will be a learning experience for both
of us!!

There is a very good list of things to check for performance, but, the list
for the most part does apply to ms-access front ends...you can take a read
here:
http://www.granite.ab.ca/access/performancefaq.htm

 

[Albert D.Kallal]

I have searched for info on this but I can't find any.

Ok....note that on a near daily basis here, there is likely one post daily
on this subject about slow jet. And 9 out of 10 times, the answer is the
same:

Try a persistent connection, and 9 out of 10 times...this fixes this
problem.

Granted, your case is different, since your front end is not ms-access.

So, note sure...but, why not give the idea a try anyway!

When you *first* open up the shared file on a network, the JET engine tries
to get EXCLUSIVE mode access. By doing so, the data engine can perform MUCH
more freely then if it has to tippy toe over the data, and CHECK if other
users are going to over write something.

The problem is that WAY too much time is wasted building and connecting to
the mdb file. The solution in your code is to thus open up the connection
once.....KEEP it open. By *forcing* the connection to remain open, you will
find that this large startup delay should disappear, and then the LONG
process of opening, and keeping the connection will not occur (and, of note,
these long delays tend to ONLY occur when someone else has the mdb file
already open).

So, if your processing code closes all recordsets, and then re-opens them,
perhaps this is when the delay occurs? (if yes, then likely a persistent
connection can help you).

Give the above idea a try. In your startup code, try opening a table from
the back end..(any table....)...and KEEP it open up a reocrdset).......

Then, try running your other operations.

Not saying the above will work for you. (in fact, I never suggested this
when using a non ms-access front end. However, it is worth a try, and it
sure works wonders for ms-access based applications.

In fact, try it...and post back....it will be a learning experience for both
of us!!

There is a very good list of things to check for performance, but, the list
for the most part does apply to ms-access front ends...you can take a read
here:
http://www.granite.ab.ca/access/performancefaq.htm

 

[david epsom dot com dot au]

Yes, the ODBC driver is shared.
Yes, the default workgroup database is shared.

30 seconds!

If persistent connections do not solve the problem,
there may be other things to try.

(david)

 

[david epsom dot com dot au]

Yes, the ODBC driver is shared.
Yes, the default workgroup database is shared.

30 seconds!

If persistent connections do not solve the problem,
there may be other things to try.

(david)

 

[dnoyeB]

Thanks for taking the time to reply.

Ok....note that on a near daily basis here, there is likely one post daily
on this subject about slow jet. And 9 out of 10 times, the answer is the
same:

Try a persistent connection, and 9 out of 10 times...this fixes this
problem.

Basically the connection is persistent.

Granted, your case is different, since your front end is not ms-access.

So, note sure...but, why not give the idea a try anyway!

When you *first* open up the shared file on a network, the JET engine tries
to get EXCLUSIVE mode access. By doing so, the data engine can perform MUCH
more freely then if it has to tippy toe over the data, and CHECK if other
users are going to over write something.

Its a local file that is not shared. There is only a single user of the
file.

The problem is that WAY too much time is wasted building and connecting to
the mdb file. The solution in your code is to thus open up the connection
once.....KEEP it open. By *forcing* the connection to remain open, you will
find that this large startup delay should disappear, and then the LONG
process of opening, and keeping the connection will not occur (and, of note,
these long delays tend to ONLY occur when someone else has the mdb file
already open).

actually opening is still fast, and my first few reads are fast as well.
But when I try to do a lot of data reading, I get held up.




--
Thank you,



"Then said I, Wisdom [is] better than strength: nevertheless the poor
man's wisdom [is] despised, and his words are not heard." Ecclesiastes 9:16

 

[dnoyeB]

Thanks for taking the time to reply.

Ok....note that on a near daily basis here, there is likely one post daily
on this subject about slow jet. And 9 out of 10 times, the answer is the
same:

Try a persistent connection, and 9 out of 10 times...this fixes this
problem.

Basically the connection is persistent.

Granted, your case is different, since your front end is not ms-access.

So, note sure...but, why not give the idea a try anyway!

When you *first* open up the shared file on a network, the JET engine tries
to get EXCLUSIVE mode access. By doing so, the data engine can perform MUCH
more freely then if it has to tippy toe over the data, and CHECK if other
users are going to over write something.

Its a local file that is not shared. There is only a single user of the
file.

The problem is that WAY too much time is wasted building and connecting to
the mdb file. The solution in your code is to thus open up the connection
once.....KEEP it open. By *forcing* the connection to remain open, you will
find that this large startup delay should disappear, and then the LONG
process of opening, and keeping the connection will not occur (and, of note,
these long delays tend to ONLY occur when someone else has the mdb file
already open).

actually opening is still fast, and my first few reads are fast as well.
But when I try to do a lot of data reading, I get held up.




--
Thank you,



"Then said I, Wisdom [is] better than strength: nevertheless the poor
man's wisdom [is] despised, and his words are not heard." Ecclesiastes 9:16

 

[dnoyeB]

Yes, the ODBC driver is shared.
Yes, the default workgroup database is shared.

30 seconds!

If persistent connections do not solve the problem,
there may be other things to try.

(david)

OK, that explains that is is related to the opening of seperate
databases at least. What do you mean by 'default workgroup database?'

I think my connections are persistent. If you mean not to open and
close the database on each transaction, then I do not.

Also note that I open database 1, run some calcs on database 1, then
leave database 1 open, but it is not being used. Then I open database
2, which opens fine, then I run some calcs on database 2, and that is
where database 2 slows down.


Thanks for the info. im going to have to dig deeper.





--
Thank you,



"Then said I, Wisdom [is] better than strength: nevertheless the poor
man's wisdom [is] despised, and his words are not heard." Ecclesiastes 9:16

 

[dnoyeB]

Yes, the ODBC driver is shared.
Yes, the default workgroup database is shared.

30 seconds!

If persistent connections do not solve the problem,
there may be other things to try.

(david)

OK, that explains that is is related to the opening of seperate
databases at least. What do you mean by 'default workgroup database?'

I think my connections are persistent. If you mean not to open and
close the database on each transaction, then I do not.

Also note that I open database 1, run some calcs on database 1, then
leave database 1 open, but it is not being used. Then I open database
2, which opens fine, then I run some calcs on database 2, and that is
where database 2 slows down.


Thanks for the info. im going to have to dig deeper.





--
Thank you,



"Then said I, Wisdom [is] better than strength: nevertheless the poor
man's wisdom [is] despised, and his words are not heard." Ecclesiastes 9:16

 

[Albert D.Kallal]

Gee, I miss read your original post....

it seemed like you where saying that when you had more then one client to
the database it slowed down...

That is not *exactly* what you are saying. Reading your post, one instantly
comes to the conclusion that this is multi--user...and, now that you cleared
this up..that is not the case.

Given this clarity (my fault for confusing by the way), then I would
actually suggest that you DO close your reocrdssets, and keep nothing
open....

actually opening is still fast, and my first few reads are fast as well.
But when I try to do a lot of data reading, I get held up.

You might try breaking the above into chunks. Say, go with 25,000
records..and then close to force everything to be flushed to disk..and thus
you get more ram/buffers for the next set of data.....

 

[david epsom dot com dot au]

I'm surprised!

Working on local files, and you can see that the
LDB files and not appearing and disappearing.

The workgroup database contains lists of users,
groups, and ID's. It is used to match a user
against user and group ID's. The user and group
ID's are matched against user permissions, which
are stored in the data databases.

The default workgroup is called system.mdw, and
contains the default user ('admin') and the default
group ('Admins').

Depending on how Access is installed, you may be
using a copy of system.mdw from your windows system
directory, or from somewhere in your program files
folders.

When not using Access, it is sometimes possible
to access your data without using a workgroup,
just using the internal jet engine 'engine' user.

Find your workgroup, and watch the behaviour of
the .LDB file.

Increase the number of threads used by jet (I don't
have a reference, just search here and in help)

Is this normally going to be single user? You might
also increase the length of the cache time out and
the size of the jet cache.

Also, if you are working against a single table,
you could try keeping the table open, as well as
the connection.

(david)

Yes, the ODBC driver is shared.
Yes, the default workgroup database is shared.

30 seconds!

If persistent connections do not solve the problem,
there may be other things to try.

(david)

OK, that explains that is is related to the opening of seperate databases
at least. What do you mean by 'default workgroup database?'

I think my connections are persistent. If you mean not to open and close
the database on each transaction, then I do not.

Also note that I open database 1, run some calcs on database 1, then leave
database 1 open, but it is not being used. Then I open database 2, which
opens fine, then I run some calcs on database 2, and that is where
database 2 slows down.


Thanks for the info. im going to have to dig deeper.





--
Thank you,



"Then said I, Wisdom [is] better than strength: nevertheless the poor
man's wisdom [is] despised, and his words are not heard." Ecclesiastes
9:16