Slow access opening ADO connection by second user

Discussion in 'Computer Support' started by Mark S., May 16, 2006.

  1. Mark S.

    Mark S. Guest

    Hi,

    Any software developers out there on the 24hoursupport newsgroup? If so, can
    you help?

    The same application will run on different computers, each one connecting to
    the same Microsoft Access 97 database on a shared network drive. When the
    application on ComputerA runs, it can connect to the database fine, but when
    its connection is kept open, any connections to the database by ComputerB
    are significantly slower. In this case, a connection time is typically 1770
    milliseconds, instead of 260 milliseconds.
    Here's some sample code I'm trying this with:

    Public Declare Sub OutputDebugStringA Lib "kernel32" (ByVal pMsg As String)
    Public Declare Function timeGetTime Lib "winmm.dll" () As Long
    dim m_cnn As ADODB.Connection

    Private Function DoSimpleTest_OpenAndKeepOpen(strResult$, strDatabase$)
    Dim t1&, t2&
    t1 = timeGetTime
    Set m_cnn = New ADODB.Connection
    m_cnn.Mode = adModeShareDenyNone
    m_cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase
    t2 = timeGetTime
    strResult = "OpenKeepOpen took " & t2 - t1 & " ms."
    OutputDebugStringA (strResult & vbCrLf)
    End Function

    Private Function DoSimpleTest_CloseExisting(strResult$)
    Dim t1&, t2&
    t1 = timeGetTime
    m_cnn.Close
    Set m_cnn = Nothing
    t2 = timeGetTime
    strResult = "CloseExisting took " & t2 - t1 & " ms."
    OutputDebugStringA (strResult & vbCrLf)
    End Function

    Private Function DoSimpleTest_OpenAndClose(strResult$, strDatabase$)
    Dim t1&, t2&, t3&
    t1 = timeGetTime
    Dim cnn As New ADODB.Connection
    cnn.Mode = adModeShareDenyNone
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase
    t2 = timeGetTime
    cnn.Close
    Set cnn = Nothing
    t3 = timeGetTime
    strResult = "Open db connection took " & t2 - t1 & " ms. Close took " & t3 -
    t2 & " ms."
    OutputDebugStringA (strResult & vbCrLf)
    End Function


    Here's some results where ComputerA and ComputerB are using the database
    X:\temp\test.mdb:
    ComputerA calls OpenAndClose which takes 317ms to open the connection and
    80ms to close.
    ComputerB calls OpenAndClose which takes 431ms to open the connection and
    70ms to close.
    ComputerA calls OpenKeepOpen which takes 344ms to open the connection.
    ComputerB calls OpenAndClose which takes 1773ms to open the connection and
    60ms to close.
    ComputerA calls CloseExisting which takes 80ms to execute.
    ComputeRB calls OpenAndClose which takes 260ms to open the connection and
    70ms to close.
    All times do vary slightly, except for the very long time to open the
    connection while an existing connection is made which always takes a
    significantly long time to execute.
    I've written a test application to do these tests, and it references
    "Microsoft ActiveX Data Objects 2.8 Library".
    If ComputerA runs two instances of this program, the second instance does
    not experience a reduction in speed while opening the connection.
    What can be done to speed up multi-user access to the Microsoft Access
    database using ADO?
    thanks in advance,
    Mark.
     
    Mark S., May 16, 2006
    #1
    1. Advertising

  2. Mark S.

    Dan Evans Guest

    "Mark S." <> wrote in message
    news:e4cag4$tm3$...

    > What can be done to speed up multi-user access to the Microsoft Access
    > database using ADO?


    Access isn't designed for multi user access - is there a reason that you
    can't use another database, MySQL for example?

    Dan





    .................................................................
    Posted via TITANnews - Uncensored Newsgroups Access
    >>>> at http://www.TitanNews.com <<<<

    -=Every Newsgroup - Anonymous, UNCENSORED, BROADBAND Downloads=-
     
    Dan Evans, May 16, 2006
    #2
    1. Advertising

  3. Mark S.

    Mark S. Guest

    "Dan Evans" <> wrote in message
    news:4469baf0$0$24141$...
    >
    > Access isn't designed for multi user access - is there a reason that you
    > can't use another database, MySQL for example?
    >


    It would be great if I could, but that would require a large existing
    application to be rewritten, which simlply isnt feasible at present.
     
    Mark S., May 16, 2006
    #3
  4. Dan Evans wrote:

    > "Mark S." <> wrote in message
    > news:e4cag4$tm3$...
    >
    >> What can be done to speed up multi-user access to the Microsoft
    >> Access database using ADO?

    >
    > Access isn't designed for multi user access - is there a reason that
    > you can't use another database, MySQL for example?


    Technically, it is multi-user, but it is so bad that it certainly is not
    recommended. I gave up on it years ago.

    Its biggest fault is that when a query is running, it locks a 'page' of
    records, maybe 2kb at a time, instead of individual rows. Any other
    query running at that time will have to wait. Also, once you get a table
    to 10,000 rows, it nearly dies.

    I've always used Visual FoxPro for office network data applications; it
    is very, very good at that.

    --
    -bts
    -Warning: I brake for lawn deer
     
    Beauregard T. Shagnasty, May 16, 2006
    #4
  5. Mark S.

    Leythos Guest

    In article <hpjag.109337$>,
    lid says...
    > Dan Evans wrote:
    >
    > > "Mark S." <> wrote in message
    > > news:e4cag4$tm3$...
    > >
    > >> What can be done to speed up multi-user access to the Microsoft
    > >> Access database using ADO?

    > >
    > > Access isn't designed for multi user access - is there a reason that
    > > you can't use another database, MySQL for example?

    >
    > Technically, it is multi-user, but it is so bad that it certainly is not
    > recommended. I gave up on it years ago.
    >
    > Its biggest fault is that when a query is running, it locks a 'page' of
    > records, maybe 2kb at a time, instead of individual rows. Any other
    > query running at that time will have to wait. Also, once you get a table
    > to 10,000 rows, it nearly dies.
    >
    > I've always used Visual FoxPro for office network data applications; it
    > is very, very good at that.


    You can also port a MS Access database to MSDE for free. There is also
    the SQL 2005 version that is free. Both MSDE and 2005 support the
    features necessary to support multiple users - one thing to keep in mind
    is that Windows XP is limited to 10 connections, which could also be
    part of the problem.

    --


    remove 999 in order to email me
     
    Leythos, May 16, 2006
    #5
  6. Mark S.

    Dan Evans Guest

    "Beauregard T. Shagnasty" <> wrote in message
    news:hpjag.109337$...
    > Dan Evans wrote:
    >
    >> "Mark S." <> wrote in message
    >> news:e4cag4$tm3$...
    >>
    >>> What can be done to speed up multi-user access to the Microsoft
    >>> Access database using ADO?

    >>
    >> Access isn't designed for multi user access - is there a reason that
    >> you can't use another database, MySQL for example?

    >
    > Technically, it is multi-user, but it is so bad that it certainly is not
    > recommended. I gave up on it years ago.


    I'd say it's multi user in the same way my unicycle is.

    > Its biggest fault is that when a query is running, it locks a 'page' of
    > records, maybe 2kb at a time, instead of individual rows. Any other
    > query running at that time will have to wait. Also, once you get a table
    > to 10,000 rows, it nearly dies.


    I had to use an access database for barcelona.com (way back when) - 2
    million page views a month. I was well pleased when I managed to persuade
    everyone we should be using MySQL.





    .................................................................
    Posted via TITANnews - Uncensored Newsgroups Access
    >>>> at http://www.TitanNews.com <<<<

    -=Every Newsgroup - Anonymous, UNCENSORED, BROADBAND Downloads=-
     
    Dan Evans, May 16, 2006
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. MadMike42

    ODBC or ADO Connection String

    MadMike42, Feb 23, 2006, in forum: Computer Support
    Replies:
    3
    Views:
    643
    MadMike42
    Feb 23, 2006
  2. Joker

    ADO connection from Outlook form

    Joker, Mar 20, 2007, in forum: Software
    Replies:
    1
    Views:
    1,023
    Joker
    Mar 21, 2007
  3. Expert lino fitter

    Re: slow slow slow!

    Expert lino fitter, Dec 10, 2008, in forum: Computer Support
    Replies:
    0
    Views:
    698
    Expert lino fitter
    Dec 10, 2008
  4. Expert lino fitter

    Re: slow slow slow!

    Expert lino fitter, Dec 10, 2008, in forum: Computer Support
    Replies:
    5
    Views:
    701
    Expert lino fitter
    Dec 12, 2008
  5. Beauregard T. Shagnasty

    Re: slow slow slow!

    Beauregard T. Shagnasty, Dec 10, 2008, in forum: Computer Support
    Replies:
    2
    Views:
    711
    Shel-hed
    Dec 10, 2008
Loading...

Share This Page