Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

JET engine becomes slow

Discussion in 'Information Technology' started by dnoyeB, Jul 28, 2006.

  1. dnoyeB

    dnoyeB
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Travel Jet-lag Study Reveals Why Time Changes Are A Struggle Breaking News Sep 11, 2013
    Nature Mysterious Deep Sea Jets Alter Global Climate Breaking News May 20, 2011
    World French Jets Start Patrols as Libyan Rebels Urge Action Breaking News Mar 19, 2011
    Akal Takhat Jethedar - A History (by J.S. Tiwana) History of Sikhism Dec 23, 2009
    Jeth.. Gurmat Vichaar May 15, 2009

  3. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    > 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 (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  4. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

    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" <dnoyeB@ThisOneIsFake.invalid> wrote in message
    news:cqmdnYuPJ5uYvujZ4p2dnA@comcast.com...
    >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
     
  5. dnoyeB

    dnoyeB
    Expand Collapse
    Guest

    Thanks for taking the time to reply.

    Albert D.Kallal wrote:
    >>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.
    >


    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
     
  6. dnoyeB

    dnoyeB
    Expand Collapse
    Guest

    david epsom dot com dot au wrote:
    > 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
     
  7. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    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.....


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  8. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

    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)

    "dnoyeB" <dnoyeB@ThisOneIsFake.invalid> wrote in message
    news:8IOdnZCR7ZmZCejZ4p2dnA@comcast.com...
    > david epsom dot com dot au wrote:
    >> 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
     

Share This Page