Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Castelar
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Slow ADO Connection - Database Connec Performance (2003)

    I asked a couple of posts before in the Excel room a problem with a retrieving function in Excel. There, I had to retrieve many many data to Excel spreadsheets at user's request. It was solved with matrix functions.
    The question now comes out from the same problem. If a user was to ask for a 500x2 matrix, the function would take 2 minutes to retrieve that to the spreadsheet. I checked everything in the functions and it seems to be all ok. Well... here comes the question.

    Do you know any web page or manual with reference regarding "velocity of connections"? I suspect that I should migrate all the database (+350,000 entries and 10 potential users of complex queries) to MySQL (or SQL Server or any other) to increase the performance. Am I right? Where can I read about database performance in a straightforward language? Could be the object ADODB.Connection the problem? Or should I suspect of the LAN?

    Thank you very much!

    All the best! (and sorry for my 5-years-old-kid english)


    Federico

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Slow ADO Connection - Database Connec Performance (2003)

    Don't worry about your English - it's fine.

    I don't know if there is a manual about connection speed - there are many factors that play a role: size of the database, number of simultaneous users, capacity of the network, etc.

    350,000 records is not excessive for an Access database, but 10 users simultaneously executing complex queries will stress its capabilities. Storing the data in a server database such as SQL Server and running the queries on the server instead of locally in Access would probably improve performance.
    Microsoft has published some papers on migrating from Access to SQL Server:

    Microsoft Access or SQL Server 2005: What's Right in Your Organization?
    When to Migrate from Microsoft Access to Microsoft SQL Server
    Guide to Migrating from Microsoft Access to SQL Server 2005
    Optimizing Microsoft Office Access Applications Linked to SQL Server

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Location
    Castelar
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow ADO Connection - Database Connec Performance (2003)

    Excelent response, as always. I'll take a look of these web pages.

    Thank you very much.

    Federico.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •