Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    4

    Unanswered: Access / MySQL - Slow Searches

    Hello,

    I have migrated a customer contracts database to MySQL today, however I am still using Access as a frontend. This seems to have gone really well with no impact on the function of the database apart from searching (ctrl+f). Unfortunately searching now takes 10 times longer than it used to, I have read that a few others have had this problem but as of yet I have not found a solution that has been posted. One person mentioned they created there own custom search button but did not elaborate.

    Does anyone know how I can create a button on my form (without using built in search) that will search and find in my contacts table and cycle through the matches (for company name field only) if they are partial?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    shifting an Access applications that was storing data in JET to a server db will make little improvement in the app. to leverage the advantage of the server you need to make certain the applciation uses pass through queries or ODBC connections.

    however there are somethings you can do right now
    check the tables, make certain columns used for most searches are indexed
    check the state of the indexes, after a bulk load it may well be that several indexes are banjaxed
    make use to the EXPLAIN command in MySQL to find where the queries are consuming resources.

    if you use built in featurtes of access (eg autoloading combo boxes, bound controls you potentailly have the worst of both worlds, extracting data from a server and processing it in JET on the local machine.. your network trolls will love that....)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2009
    Posts
    4
    Well, looking at the network activity when I run a search its almost as if access is downloading the contents of the table before searching.

    Apart from that performance seems to be okay, the main reason behind the move was issues with 15+ users causing access to lock up for a few seconds a few times per day. This seems to be solved but now I've got this search issue and i'm not exactly an expert.
    Last edited by JohnAddison; 07-28-09 at 16:10.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by JohnAddison
    Well, looking at the network activity when I run a search its almost as if access is downloading the contents of the table before searching
    yep that sounds like a conventional Access application talking to a server, where the queries are being run in JET on the local machine. as I style it the worst of both worlds and few if any of the benefits from using a server backend.

    mind you most of the porblems associated with Access locking up with users is usually workable on JET as well as a server
    use unbound recordsets, only get what you need.

    slow searches are almost certainly down to indexes either being corrupted or not present, check your indexes on the server.

    using the wsiwyg search tool in Access will almost certainly be appallingly slow if you are using a server backend as its optimised for JET. if you want this functionality then you need to roll your own form that does precisely what you want in the manner you want, rahter than a generalised code generator would do it.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2009
    Posts
    4
    Sadly I don't have the skill to write a custom search. The peformance apart fromt he seach issue is exactly whats needed but the search things is a killer.

    MIght have to look for an off the shelf contacts DB.

    Thanks
    John

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you checked the columns in the MySQL table(s) are indexed?
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2009
    Posts
    4
    I have no idea how to do that, from what I can understand and after looking at the database it's not indexed but I have no idea how to do it.

    Thanks
    John

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a tool such as MySQL Admin (part of the MySQL gui tools form the MySQL site
    or phpmyadmin
    or HeidiSQL
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2009
    Posts
    1

    Slow searches in Access/My SQL

    I have recently migrated an Access database to SQL Server, retaining the Access front end. I've also experienced slow searches using the existing Find button on my forms, this despite the fact that I have the appropriate indexes on all of my tables and queries. Instead of using a button, I've now put in a combo box which allows me to find records in my form with lighting speed (I have >60,000 records in the particular form). The process is simple and requires no knowledge of VBA coding. See http://www.vb123.com.au/toolbox/07_a...findrecord.htm for a step-through of the method of building this combo box.

    Hope this helps.

Posting Permissions

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