Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194

    MySQL speed issues and ideas

    Hi everyone,



    I have an Access frontend connected via unicode ODBC to Mysql (MariaDB) on a host.

    Im relatively new to all this sql stuff!

    The program was great (took a year to build) and so fast, but now odbc it is crazzzzzzy slow.

    I have done my research and i KNOW this is because im a complete n00b and there are many things in my db that probably should change to make it faster.

    Can anyone tell me some key points on how to get this working better or some really good reading material on it?

    Currently my coding is vba not macros.

    I do use a large amount of DAO recordsets as that is what i taught myself from the get go, and have more recently been using SQL (docmd.execute) stuff.

    Is the sql preferential and much faster?

    Also are there any settings i should be using for the odbc driver to speed things up?

    One big part that i have an issue with is where i have used allen brownes code for filtering results.

    I have a table (only 5 records at moment) but prob going to end up as hundreds/thousand records.

    This shows results and gets filtered as text boxes get updated. This very procedure ( filteron - true, filter = "[thisfield] = " & me.txtfield) etc etc works great on access and so fast but now with sql backend its crazy slow.


    Anyway, i know theres a number of points and questions here but any help would be amazing. I did all this as i needed a web front end to my program for cleints to access a few tables but i still use the rest of it in my own pc access based system that i cant afford to get written into a native SQL online program just yet.

    Gangel
    TIA for reading

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    around speed issues:

    sql is faster than dao recordsets - but perhaps dao recordsets is the right thing to be using - depends on what you are doing
    use passthrough or stored procedures in the backend rather than processing on the front end
    ensure your tables are properly indexed for fast filtering and sorting
    only bring back required data - not whole tables by default - includes combo and listbox rowsources as well as form/report recordsources
    ensure your queries are optimised

    Look at how (fast) websites work - only a page of data is brought back at a time, combos/listboxes have short lists, often takes 2 or 3 steps to do something that might be 1 step in access etc

  3. #3
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194
    sql is faster than dao recordsets - but perhaps dao recordsets is the right thing to be using - depends on what you are doing
    Mainly...
    1. Create new entry in a main table (eg. InvoiceGroup) - then take that InvoiceGroupID
    - Cycle through [Invoice] table and allocate that invoice group ID to all those invoices that need to be done.

    So eg. select * from Invoices where CompanyID = ABC, dbopendynaset, doseechanges

    The do the above.
    So im having a RS for [invoiceGroup] then an RS for [Invoices]

    That isnt a real thing im doing, but is as close as i can get to really explaining what im using the dao for.

    Also using it for
    do until rs.eof
    *loop through records, dlookup the cost price for [itemid] then calculate a retail price
    rs!retail price = dlookup("ItemID","Items", "Itemid = " & rs!itemid) * 1.75 + 10 + variable
    etc




    use passthrough
    is this just for update and insert etc? It isnt for "select" queries is it!?
    I have no access queires that update or insert or delete, these are all via dao or sql


    or stored procedures in the backend rather than processing on the front end
    I think i need to look into these and read more about them
    I dont know what they are, but if they are code that can run in background and run on server that my program calls... then this is what i need...
    Am i right?
    What language are the procedures written in? or are they jus stored sql lines??

    ensure your tables are properly indexed for fast filtering and sorting
    TBH i believe they are.
    Everything that doesnt need to be sorted is not indexed. Everything that i use in sorts or ID rows IS indexed. I read it is good to have indexs but bad to over do it.


    only bring back required data - not whole tables by default - includes combo and listbox rowsources as well as form/report recordsources
    This is something im interested in. I dont fully understand how to do this.
    Lots of my combo boxes are 1-5 items only.
    One main one is a combo box list of clients (600 clients max at any time).. is this a "large" combo box?

    So for queries should i be returning only a certain amount of rows?

    eg. Select * from Invoices where InvoiceBatchID = 5 might return... 50 invoices.


    ensure your queries are optimised
    Sorry please explain this comment

    Reports:
    My reports are very slow My understanding is this is because i have many tables joined together and the data comes from all of them. Some basic reports take 30 seconds to generate.

    Is there a way around this??

    Wow
    Thanks for all the help so far, appreciate it and looking forward to hearing some more about the above and getting to work on it.

    Gangel

  4. #4
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194
    ok so i found this as well
    https://dev.mysql.com/doc/connectors...amming-vb.html

    This uses a conn.string to do conn.openrecordset(select etc etc)

    Do you think this would be better and faster than
    currentdb.openrecordset??

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    solutions are application specific - all depends on what you are trying to do as to what I the right solution.

    you are using sql to create your recordset

    your example should all be done in a query - using dlookup and other domain functions is really inefficient when used in queries - you should instead create a link to the items table

    passthrough queries are created in access and passed on to the db server for execution - they don't have to be action queries. Not tested but I presume they can be used in MySQL and would need to be written in the MySQL version of sql.

    stored procedures can run in the background on the server and can be used to run several queries on after other. The access equivalent would be a vba chain of query execution. They are written in sql

    indexes - sounds like you have it setup right - you are correct about not indexing fields with lots of nulls and/or limited range of values (such as Boolean). Also be aware that using and initial * in a like criteria (Like "*somevalue*") does not use indexing, so avoid if you can.

    limiting data to bring back - better to have a recordsouce with criteria rather than the full source which is then filtered in the form.

    One main one is a combo box list of clients (600 clients max at any time).. is this a "large" combo box?
    yes. anything over 10 -20 rows is large. Instead do something like the following:


    Set the combo rowsource to be something like
    Code:
    "SELECT ItemPK,  ItemName FROM tblItems WHERE false"
    - or even leave it blank

    and in the combo change event put
    Code:
    if myCombo.SelStart=3 then myCombo.Rowsource="SELECT ItemPK,  ItemName FROM tblItems WHERE ItemName Like '" & myCombo.Text & "*'"
    so no records are brought through until the user has typed 3 characters, upon which a limited range of items are brought through.

    eg. Select * from Invoices where InvoiceBatchID = 5 might return... 50 invoices
    may be OK, may not - depends if you really want 50 invoices or are looking for a specific one or two or there are some other parameters such as 'within the last month' or 'contains a certain item' or 'not paid' etc

    Optimising queries - there are tools you can use to check how well the query is performing and identifying bottlenecks. typically you will find them in the tools menu. As mentioned above, avoid dlookups, ensure indexing is up to par.

    Reports:
    My reports are very slow My understanding is this is because i have many tables joined together and the data comes from all of them. Some basic reports take 30 seconds to generate.

    Is there a way around this??
    without seeing your relationships, the queries used and having a good understanding how your business works, impossible to say. My rule of thumb for running processes where the user needs to wait until finished before they can carry on is if it takes more than 5 seconds, find another way

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Do you think this would be better and faster than
    currentdb.openrecordset??
    don't know, as previously advised, opening recordsets is significantly slower than running a query. If I was to guess I would say that because opening connections take time the currentdb method will be quicker if you have linked tables to MySQL and you have set it to keep the connection open - but this may be limiting if you have many concurrent users.

    Best way to find out is to try it and see.

  7. #7
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194
    k, that was amazing. definately going to sort out my dlookup stuff striaght away.

    Will look like i need to re write a huge portion of my code but im very sure it is going to prove to help.
    Thanks so much, il be back tomorrow and the next few days and show how its going... probably with questions :P

    Very much appreciate all this help.

  8. #8
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194
    Ok Ajax :P

    So i thought i was getting it, and i decided to tackle my very first set of code that was taking 17 seconds to run on SQL, and about... 0.5 sec on access.

    It is pretty much taking a [scriptbatch] which consists of [scripts]

    i originally used

    srst to select Scripts with scriptbatchID
    chemRST to select Chemist data

    take data from
    4 or 5 lookups

    insert data into table scripts.


    Main idea:
    customer submits a product
    I work out its price for them and input it into table....
    There isnt THAT much to think about!
    SO in reality what i need to achieve is easy.

    I deceided to get away from dlookup by making a query that puts all the lookup fields in it by links.

    So i created SRstString

    I then used this as my recordset.

    My vba now takes 25 secs not 17 LOL

    Below is the code. Hopefully you can follow.

    I know i mustbe making som serious mistakes but i need to find them on this code then correct my whole code base...

    Code:
    Public Sub btnCheckBatch_Click()Dim ChemRst As dao.Recordset
    Dim SRst As dao.Recordset
    
    
    Dim ScrBatchCode As String
    Dim ChemistID As String
    Dim ChemCodeOnly As String
    Dim SBID As Long
    Dim ChemInputDate As Date
    
    
    Dim DateDisp As Date
    Dim ClaimID As Long
    Dim ItemID As Long
    Dim Qty As Integer
    Dim d As String, ext, x
    Dim srcPath As String, destPath As String, srcFile As String
    Dim SRstString As String
    
    
    Dim invamt As Double
    Dim DF As Double
    Dim TP As Double
    Dim DD As Double
    Dim MUp As Double
    Dim BB As Double
    Dim bb2 As Double
    Dim bbf As Double
    Dim CostPrice As Double
    Dim Fboxes As Integer
    
    
    SRstString = "SELECT scripts.ScrBatchID, items.ManualInvoicing, scripts.InvAmount, scripts.PharmAmount, chemist.DispensingFee, chemist.AllowableExtraFee, chemist.DDFee, chemist.DDFee, chemist.Markup, items.Schedule, items.StdQty, customers.FirstName, customers.LastName, items.ItemID, items.ItemPrice,scripts.Qty, scripts.txtCustomer2 " & vbCrLf & _
    "FROM customers INNER JOIN (((scripts INNER JOIN items ON scripts.ItemID = items.ItemID) INNER JOIN chemist ON scripts.ChemistID = chemist.ChemShortCode) INNER JOIN claims ON scripts.ClaimID = claims.ClaimID) ON customers.CustomerID = claims.CustomerID " & vbCrLf & _
    "WHERE (((scripts.ScrBatchID)= " & Me.ScrBatchID & "));"
    
    
    
    
    Set SRst = CurrentDb.OpenRecordset(SRstString, dbOpenDynaset, dbSeeChanges)
    SRst.MoveFirst
    
    
    SBID = SRst!ScrBatchID
    Do Until SRst.EOF
    
    
    ItemID = SRst!ItemID
    
    
    If SRst!ManualInvoicing = 1 Then
    SRst.Edit
    SRst!InvAmount = SRst!PharmAmount
    SRst.Update
    GoTo NextScript
    End If
    
    
    If SRst!Schedule = 3 Then
    bbf = 0
    DF = 0
    TP = 0
    Fboxes = Qty
    MUp = 1
    GoTo Sched3
    End If
    
    
    'set additional charges
    DF = SRst!DispensingFee
    TP = SRst!AllowableExtraFee
    MUp = (SRst!Markup / 100) + 1
    CostPrice = SRst!itemprice
    
    
    
    
    
    
    'check if it is a DD script and add DD fee if so
    If SRst!Schedule = 8 Then
    DD = SRst!DDFee
    Else
    DD = 0
    End If
    
    
    'work out Broken box percent to nearest 0.05
    BB = 0.05 * -Int(-(SRst!Qty / SRst!StdQty) / 0.05)
    'BB = 0.05 * Round((Me.Qty / DLookup("stdqty", "items", "[itemid]=" & Me.[ItemID])) / 0.05, 0) THIS ROUNDS TO NEAREST 0.05, NOT UP
    
    
    
    
    
    
    'check to see if percent is MORE than 100% (eg more than one box given)
    If BB > 1 Then
    'If BB (non decimal integer) is the same as BB then Full Boxes is the same as BB
        If Int(BB) = BB Then
        bbf = BB
        Else
        'Otherwise save Full boxes amount (int(bb))
        'Calculate broken boxes minus the full boxes to get the percent broken still
        Fboxes = Int(BB)
        'bb2 = 0.05 * Round((BB - Fboxes) / 0.05, 0) THIS ROUNDS TO NEAREST 0.05, NOT UP
        bb2 = 0.05 * -Int(-(BB - Fboxes) / 0.05)
        
            bbf = DLookup("PercentCharged", "BrokenBoxes", "[PercentUsed] = " & bb2)
        End If
        
        
    Else
    Fboxes = 0
    
    
    bbf = DLookup("PercentCharged", "BrokenBoxes", "[PercentUsed] = " & BB)
    End If
    
    
    
    
    
    
    If SRst!Schedule = 2 Then
    DF = 0
    End If
    Sched3:
    
    
    'Round UP
    invamt = -Int(-(((CostPrice * (bbf + Fboxes)) * MUp) + TP + DF + DD) / 0.05) * 0.05
    
    
    'savelater = Me.Qty / DLookup("stdqty", "items", "[itemid]=" & Me.[ItemID])
    SRst.Edit
    SRst!InvAmount = invamt
    SRst!txtCustomer2 = SRst!FirstName & " " & SRst!LastName
    SRst.Update
    
    
    NextScript:
    SRst.MoveNext
    
    
    Loop
    'Me.StaffID = DLookup("SettingValue", "SettingsLocal", "SettingID = 1")
    '
    'DoCmd.OpenReport "rptscriptbatch", acViewNormal, , "[scrbatchid]=" & SBID
    Forms!Navigationpage!NavigationSubform.Form!FrmOnlineBatches.Requery
    DoCmd.OpenForm "FrmScriptBatch", , , "[ScrBatchID] = " & SBID, acFormEdit
    
    
    End Sub

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    my point about doing all in a query was not so that you then used vba to do the updates, you use the query
    you also do not need the '& vbcrlf & ' in the code

    taking one part of your code as an access query

    Code:
    UPDATE Scripts 
    SET
     scripts.InvAmount=iif(items.manualinvoicing=1,scripts.PharmAmount,scripts.invAmount)
    
    FROM
    customers INNER JOIN (((scripts INNER JOIN items ON scripts.ItemID = items.ItemID) INNER JOIN chemist ON scripts.ChemistID = chemist.ChemShortCode) INNER JOIN claims ON scripts.ClaimID = claims.ClaimID) ON customers.CustomerID = claims.CustomerID 
    WHERE (((scripts.ScrBatchID)= " & forms!myfrom!ScrBatchID & "))
    instead of iif you could use the choose or switch functons

    in MySQL you would put this in a parameterised stored procedure, passing the scrBatchID through as the parameter.You could also use the case when action instead of iif (choose and switch are not options). SQL Server also has a way of joining tables called 'cross apply' not sure if it is in MySQL but can be useful.

  10. #10
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194
    ok thanks Ajax,

    SO what i have done is set Scripts as a DAO recordset just to get the data FROM

    I then save back to the db using SQL strings.
    This has decreased time from 27 seconds down to 9 seconds.
    The main issue now is my form opening!!

    So it runs SQL updates on 4 records (3 seconds) and then opens a form with a subform... 6 seconds.

    The MAIN form is based on table [ScriptBatch]
    The subform is based on a multitable query with no WHERE functions. (ScrBatchID is in this table)

    THe subform is linked ot the main form via [scrbatchID]


    So i open the form with
    Code:
    docmd.openform "FrmScriptBatch",,"[scrbatchID] = " & SBID
    SB id is from the recordset before. It is an integer.

    it is bringing ALL the data across then filtering on my side???

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it is bringing ALL the data across then filtering on my side???
    it may be smart enough to just bring the resultant record(s) through, but it is filtering rather than using a criteria - if you have the navigation buttons showing on the frmScriptBatch, you will see the filter has been set - and if in layout view you look at the form data properties, you will see the filter there.

    Suggest temporarily modify your form recordsource to include the criteria as well and see if that makes a difference

  12. #12
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194
    yes i was thinking exactly that. Why does it do that as a filter not as a WHERE function!

    Ps. OMG i just noticed i literally have no indexes and no foriegn keys...

    pretty sure thats going to be an issue.

    This is all running via access relationships.!!!

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    thought you said it was all indexed

    with regards your form recordsource, to apply criteria instead of using the filter, instead of using a table use a query type something like

    SELECT * FROM myTable WHERE ID=forms!myform!SBID

  14. #14
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    194
    except my understanding is using form! etc in query sends to mysql and it doesnt understand location criteria so it sends all data and access ends up doing the query!?

  15. #15
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I've not heard that - I'm assuming you are linked to the MySQL tables.

    Either way, give it a try

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Speed/perfromance issues with Linked Tables.
    By Jamescdawson in forum Database Design
    Replies: 2
    Last Post: 05-12-2012, 04:04 AM
  2. Any ideas?
    By eripsni in forum Access
    Replies: 9
    Last Post: 08-25-2011, 08:33 AM
  3. Slow Runtimes any ideas to speed up process?
    By salisbut in forum Access
    Replies: 9
    Last Post: 09-16-2010, 12:14 PM
  4. Need for Speed
    By OceanaPolynom in forum Programming
    Replies: 2
    Last Post: 07-13-2010, 08:30 PM
  5. Query speed issues
    By thart21 in forum Queries
    Replies: 2
    Last Post: 04-07-2010, 05:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums