Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185

    Help Optimising Form Code

    Hi All

    As some of you are well aware I am working on a large freight rate program database.

    I have a "bandaid" solution deployed now with users for testing, in the meantime (in other posts) I am working on a complete rework.




    However users are reporting back on the bandaid solution attached that the form "apply filter" command its very slow and hangs at times, since on this version all the data is in local tables (when used by the form) I don't really know why they are getting these performance issues.

    Any advice on how to get better performance / optimisation from the "apply filter" command.

    Code as below:
    Code:
    Private Sub cmdApply_Click()'Below the above element
    'Used to apply all filter fields
    
    
    'Runs the listbox update code
    SetListRS
    
    
    End Sub
    This command just runs the list8.rowsource update code as below, which is where the lag will come from:

    Code:
    Private Sub SetListRS()'Not an actual element on form
    'Used to apply all the filters (or not) above to generate query results for main listbox
    
    
    Dim strRS As String
    Dim strWhere As String
    Dim rs As String
    Dim strOrder As String
    
    
    'Sets the initial SQL query parts for further use
    strOrder = " ORDER BY Master_Data2.[Valid From], Master_Data2.[Valid To]"
    strRS = "SELECT DISTINCT Master_Data2.ID, Master_Data2.[POL Name], Master_Data2.[POD Name], Master_Data2.Carrier, Master_Data2.[Contract Type], Master_Data2.Contract, Master_Data2.[20GP All In], Master_Data2.[40GP All In], Master_Data2.[40HC All In], Master_Data2.[Valid From], Master_Data2.[Valid To], Master_Data2.Transit, Master_Data2.Direct FROM FRT_Table INNER JOIN Master_Data2 ON FRT_Table.ID = Master_Data2.ID"
    strWhere = ""
    
    
    'Checks if the various filter comboboxes have values, if yes adds them to the WHERE portion
    If POLCombo.Value <> "" Then
        strWhere = strWhere & " And Master_Data2.[POL Name] in(" & POLCombo.Value & ")"
    End If
    
    
    If PODCombo.Value <> "" Then
        strWhere = strWhere & " And Master_Data2.[POD Name] in(" & PODCombo.Value & ")"
    End If
    
    
    If CarrierCombo.Value <> "" Then
        strWhere = strWhere & " And Master_Data2.Carrier in(" & CarrierCombo.Value & ")"
    End If
    
    
    If CTypeCombo.Value <> "" Then
        strWhere = strWhere & " And Master_Data2.[Contract Type] in(" & CTypeCombo.Value & ")"
    End If
    
    
    'Checks if filters have been applied
    If Len(strWhere) <> 0 Then
        'Remove the leading " And "
        strWhere = Right(strWhere, Len(strWhere) - 4)
    Else
        'If no filters applied
        'Checks if expired rates are reqested
        If ExpiredCheck.Value = True Then
            List8.RowSource = "Form_ListBox_OldDates_Query"
            Exit Sub
        End If
        
        'Checks if a date based rates are requested
        If viewDate.Value <> "" Then
            List8.RowSource = "Form_ListBox_SingleDate_Query"
            Exit Sub
        End If
        
        'If none of the above, sets main listbox source to empty query (valid dates > todays date)
        List8.RowSource = "Form_ListBox_Query"
        Exit Sub
    End If
    
    
    'Checks if expired rates are requested
    If ExpiredCheck.Value = True Then
        'If yes, then generates the SQL query for the main listbox by combining the different portions into one string
        Me.List8.RowSource = strRS & " Where " & strWhere & strOrder
        Exit Sub
    Else
    End If
    
    
    'Checks if a date based rates are requested
    If viewDate.Value <> "" Then
        'If yes, then generates the SQL query for the main listbox by combining the different portions into one string
        strWhere = strWhere & " And (Master_Data2.[Valid From])<=Forms![Test Form]!viewDate And (Master_Data2.[Valid To])>=Forms![Test Form]!viewDate"
        Me.List8.RowSource = strRS & " Where " & strWhere & strOrder
        Exit Sub
    Else
    End If
    
    
    'If none of the above, then applies filters and generates the SQL query for the main listbox by combining the different portions into one string
    strWhere = strWhere & " And ((Master_Data2.[Valid To])>=Date())"
    Me.List8.RowSource = strRS & " Where " & strWhere & strOrder
    
    
    End Sub
    Attached is the full database file:

    Freight Rate Program v1.02.zip

    Not that I'm sure but maybe it has something to do with Master_Data2 not being "loaded" or something, not sure how Access SQL queries work.

    Also note sure why the:

    Code:
    strRS = "SELECT DISTINCT Master_Data2.ID, Master_Data2.[POL Name], Master_Data2.[POD Name], Master_Data2.Carrier, Master_Data2.[Contract Type], Master_Data2.Contract, Master_Data2.[20GP All In], Master_Data2.[40GP All In], Master_Data2.[40HC All In], Master_Data2.[Valid From], Master_Data2.[Valid To], Master_Data2.Transit, Master_Data2.Direct FROM FRT_Table INNER JOIN Master_Data2 ON FRT_Table.ID = Master_Data2.ID"
    Is "FROM FROM FRT_Table INNER JOIN Master_Data2 ON FRT_Table.ID = Master_Data2.ID" as if you run Master_Data2 it shows the info required? But I had help writing that part of the code.

    Any ideas?

  2. #2
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Might be helpful if you had some test data in the tables.

    Nice to see someone using my getLbx procedure
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  3. #3
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    There should be some test data in the tables, or maybe it isnt pulling it from Azure? You need the ODBC driver installed for the link to work.


    Haha it that yours? I probably got it off you last year some time (I've been working on this for awhile), cheers

  4. #4
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Hi Again.

    Here is a database version that doesnt use Azure and has a bunch of data in the local tables for testing.

    Freight Rate Program v1.02 Azure Disabled.zip

    I think its all about the Master_Data2 query, it just seems to hang on that all the time no matter what you do in it?

    The form just applies filters to that Master_Data2 query to show in the list8 box, is there some way to "load" the Master_Data2 on opening? As I think it because it has to run through the 2 or 3 steps to generate the Master_Data2 query each time you do something but that isnt really required as the Master_Data2 actual data only changes when a admin user does a upload, which isn't the problem currently, and admin users expect some uploading time so it doesnt bother them.

  5. #5
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Wow, took well over a minute just to load the form.

    I broke down your union query MasterData2 and found that this portion of it takes forever to load and my guess is that's your culprit. A further guess is that the triple right join has something to do with it.
    I'm not that good with that stuff so hopefully someone can spot it.


    Code:
    SELECT FRT_Table.ID, FRT_Table.[POL Name], FRT_Table.[POD Name], FRT_Table.Carrier, FRT_Table.[Contract Type], FRT_Table.Contract, FRT_Table.[20GP Cost], FRT_Table.[40GP Cost], FRT_Table.[40HC Cost], FRT_Table.[Valid From], FRT_Table.[Valid To], Transits_Table.Transit, Transits_Table.Direct, FRT_Table.Notes
    FROM Transits_Table RIGHT JOIN FRT_Table ON (Transits_Table.POLName = FRT_Table.[POL Name]) AND (Transits_Table.PODName = FRT_Table.[POD Name]) AND (Transits_Table.Carrier = FRT_Table.Carrier)
    WHERE (((FRT_Table.ID) Not In (SELECT Master_Data.ID FROM Master_Data) And (FRT_Table.ID) Not In (SELECT Master_Data_Offset.ID FROM Master_Data_Offset)));
    here's an easier version to read. Hopefully someone better with SQL can better spot the problem.

    Code:
    SELECT FRT_Table.ID
        ,FRT_Table.[POL Name]
        ,FRT_Table.[POD Name]
        ,FRT_Table.Carrier
        ,FRT_Table.[Contract Type]
        ,FRT_Table.Contract
        ,FRT_Table.[20GP Cost]
        ,FRT_Table.[40GP Cost]
        ,FRT_Table.[40HC Cost]
        ,FRT_Table.[Valid From]
        ,FRT_Table.[Valid To]
        ,Transits_Table.Transit
        ,Transits_Table.Direct
        ,FRT_Table.Notes
    FROM Transits_Table
    RIGHT JOIN FRT_Table ON (Transits_Table.POLName = FRT_Table.[POL Name])
        AND (Transits_Table.PODName = FRT_Table.[POD Name])
        AND (Transits_Table.Carrier = FRT_Table.Carrier)
    WHERE (
            (
                (FRT_Table.ID) NOT IN (
                    SELECT Master_Data.ID
                    FROM Master_Data
                    )
                AND (FRT_Table.ID) NOT IN (
                    SELECT Master_Data_Offset.ID
                    FROM Master_Data_Offset
                    )
                )
            );
    Heres the full union query
    Code:
    SELECT Master_Data.ID
        ,Master_Data.[POL Name]
        ,Master_Data.[POD Name]
        ,Master_Data.Carrier
        ,Master_Data.[Contract Type]
        ,Master_Data.Contract
        ,Master_Data.[20GP All In]
        ,Master_Data.[40GP All In]
        ,Master_Data.[40HC All In]
        ,Master_Data.[Valid From]
        ,Master_Data.[Valid To]
        ,Master_Data.Transit
        ,Master_Data.Direct
        ,Master_Data.Notes
    FROM Master_Data
    
    UNION
    
    SELECT FRT_Table.ID
        ,FRT_Table.[POL Name]
        ,FRT_Table.[POD Name]
        ,FRT_Table.Carrier
        ,FRT_Table.[Contract Type]
        ,FRT_Table.Contract
        ,FRT_Table.[20GP Cost]
        ,FRT_Table.[40GP Cost]
        ,FRT_Table.[40HC Cost]
        ,FRT_Table.[Valid From]
        ,FRT_Table.[Valid To]
        ,Transits_Table.Transit
        ,Transits_Table.Direct
        ,FRT_Table.Notes
    FROM Transits_Table
    RIGHT JOIN FRT_Table ON (Transits_Table.POLName = FRT_Table.[POL Name])
        AND (Transits_Table.PODName = FRT_Table.[POD Name])
        AND (Transits_Table.Carrier = FRT_Table.Carrier)
    WHERE (
            (
                (FRT_Table.ID) NOT IN (
                    SELECT Master_Data.ID
                    FROM Master_Data
                    )
                AND (FRT_Table.ID) NOT IN (
                    SELECT Master_Data_Offset.ID
                    FROM Master_Data_Offset
                    )
                )
            );
    
    UNION
    
    SELECT Master_Data_Offset.ID
        ,Master_Data_Offset.[POL Name]
        ,Master_Data_Offset.[POD Name]
        ,Master_Data_Offset.Carrier
        ,Master_Data_Offset.[Contract Type]
        ,Master_Data_Offset.Contract
        ,Master_Data_Offset.[20GP All In]
        ,Master_Data_Offset.[40GP All In]
        ,Master_Data_Offset.[40HC All In]
        ,Master_Data_Offset.[Valid From]
        ,Master_Data_Offset.[Valid To]
        ,Master_Data_Offset.Transit
        ,Master_Data_Offset.Direct
        ,Master_Data_Offset.Notes
    FROM Master_Data_Offset;
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Haven't downloaded but will offer a guess. Having to choose all records from one table and those that match another based on not one, but two subqueries would be my guess. I would try removing the WHERE clause and see what happens to the timing. If that helps, I'd look for ways to join SELECT queries that return either the NOT IN values or perhaps load those values into a staging table and use NOT IN. However, I would think that "WHERE something IN" would be faster as Access should look only for those values (sort of like being indexed). NOT IN should mean that every value has to be examined to see if should be excluded. Again, I don't know for sure - just surmising based on past reading.
    Last edited by Micron; 04-09-2021 at 10:36 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    @Micron
    I should have also mentioned that Master_Data and Master_Data_Offset are both complicated queries with sub queries. Master_Data_Offset is also a union query.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Yikes! If I was a betting person, I'd wager that something is wrong with the db schema. Getting at data should not be so convoluted.
    back.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Quote Originally Posted by Micron View Post
    Yikes! If I was a betting person, I'd wager that something is wrong with the db schema. Getting at data should not be so convoluted.
    back.
    It likely is, this version wasn't built well but I have had to push it into use, I am working on a complete rewrite based on Azure tables so hopefully should fix a lot of the issues.

    The main reason for the queries is that its adding values from two different tables together and matching up the valid from / valid to dates so that it only shows one line per date range (date range being a mix of both tables valid from / valid to if that makes sense).

    Any quick fix for this to improve performance? I'm thinking of having the Master_Data2 run at start and just dumping the completed data into a temp table, and have the form use the temp table instead, testing now and hopefully that works.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think that any analysis and resulting suggestions would require a view of the tables and relationships and likely an understanding of the business process at worst. At best, a view of the "raw" data and the desired result might suffice.

    Having data from one source in fieldA and data from another in fieldB is not so unusual IF they have a common identifier (e.g. PK field). The first stage would be an append, the 2nd an update. There probably are other ways, but without knowing the process there's not much point in speculating.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Quote Originally Posted by Micron View Post
    I think that any analysis and resulting suggestions would require a view of the tables and relationships and likely an understanding of the business process at worst. At best, a view of the "raw" data and the desired result might suffice.
    The raw data that makes up Master_Data2 is in the tables: FRT_Table, FRT_Additionals_Table & Transits_Table

    The reason behind the multiple complicated queries (to get to Master_Data2) is because the currency fields in FRT_Additionals_Table need to be added into the relevant currency fields in FRT_Table, that part it self is easy but the difficulty is the differing valid from / valid to dates.

    Master_Data2 needs to show each record (line) as one Valid From / Valid To date range, but only add together the correct currency fields for that corresponding range.

    So for example:

    FRT_Additionals_Table records ID 15 (ANL) is valid from 1/4/21 to 14/4/21
    FRT_Table records ID 13620 (Singapore / Fremantle / ANL) is also valid from 1/4/21 to 14/4/21

    So because the two date ranges are the same, through the multiple queries ending in Master_Data2 those two are combined (with relevant Transits_Table info) into 1 record, with the currency fields added together to form a "All In" cost.

    If all records were like that there wouldnt be an issue, but they are often not, so if we had the below example:

    FRT_Additionals_Table records ID 15 (ANL) is valid from 1/4/21 to 10/4/21
    FRT_Additionals_Table records ID 16 (ANL) is valid from 11/4/21 to 14/4/21 - New row that doesnt actually exist as yet but could in real data.
    FRT_Table records ID 13620 (Singapore / Fremantle / ANL) is also valid from 1/4/21 to 14/4/21

    The result in Master_Data2 needs to be 2 records as below:

    FRT_Additionals_Table records ID 15 currency fields add to FRT_Table records ID 13620 currency fields - Valid From 1/4/21 to 10/4/21
    FRT_Additionals_Table records ID 16 currency fields add to FRT_Table records ID 13620 currency fields - Valid From 11/4/21 to 14/4/21


    And that is not the only example, either FRT_Table or FRT_Additionals_Table valid form / to dates could be any range at all, so sometimes 1 FRT_Table record would be made into 3 or more depending on FRT_Additionals_Table records.


    Hope that makes sense?



    EDIT: I have duplicated the columns from Master_Data2 into a Master_Data2_Temp table, and on program load / upload I run a insert from Master_Data2 into the new table. All form elements now point to this new Master_Data2_Temp table and it runs very smoothly, so short term fix workings so far (just adds to opening and updating processing time).

    But still need a long term fix.

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The reason behind the multiple complicated queries (to get to Master_Data2) is because the currency fields in FRT_Additionals_Table need to be added into the relevant currency fields in FRT_Table,
    That indicates a design issue right there. Should be one currency field where you list the currency type, which probably should be the PK value from a table of currency types. As a result, I confess I only glossed over the rest of your post because it's not really an explanation of the business, it's really an explanation of how you're using the db with some clues about being improperly designed. Just clues, mind you, no hard evidence because the relationships were not posted as suggested. Perhaps you should review db normalization if you are not real familiar with the concepts and see if you think you designed properly. Your long term fix just might mean starting over, otherwise you will only continue to have issues until you develop a workaround for every problem that the design will throw at you.

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.com...ng-part-i.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Thanks for the links I'll add that to my research list.

    In regards to normalization and starting from scratch that is what I intend to do and have this thread here discussing table design.

    https://www.accessforums.net/showthread.php?t=83340

    If you have any ideas to add to that thread would be greatly appreciated. That thread also explains in great detail the how and what this project needs to achieve.


    I was hoping however that something could be done to the current version code above in the short term, I have currently pushed the processing of Master_Data2 to application start which has made the bosses happy for now, but I suspect as more data gets added the worse it will become.

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I see that you already have many good suggestions from knowledgeable people there so your next step might be to sketch out a design. Paper is actually a good place to start, believe it or not. I often use large sheets like those used to wrap items when you're moving.
    - or use flip chart paper or perhaps a large whiteboard
    - or you could design actual tables in Access and set relationships and take a screen dump of that
    - or use a free tool for developing db schemas (Visio also has a db schema template IIRC but it's not free. Perhaps you have access to it.)
    - or use Excel. I developed a template for table design but I lost it long ago. I'd fill out table/field names and spec all properties in it. If I couldn't recall what type a parent field was, or if it was indexed, I didn't have to open a table to look - the info was in the sheet. But I'd say it's not something you might want to do for a one-off project.

    BTW, I downloaded and opened your posted db today. 109 seconds to open form, 99 seconds to apply 2 filter items. It is as I suspected - you designed your tables like spreadsheets. It is by far the main reason for the lengthy times that form operations take. Don't be surprised or discouraged if your first one (or few) attempts at design are not optimal.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    How would I know my design attempts are not optimal?

    The issue I have is that all data needs to be able to be pushed in by excel templates (as that is how we recieve the data to begin with).

    I'm very good with excel vba so I can make that work I believe but a little bit worried about how to actually push into Access (and then through to Azure SQL).


    My second concern is the valid dates, that is what all the master_data stuff is about as it needs to combine various currency values to get the correct "All In" value to show on the form, how can I easily test if my design is ok for that side of things?

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

Similar Threads

  1. Replies: 8
    Last Post: 07-03-2015, 05:03 PM
  2. Replies: 3
    Last Post: 10-16-2014, 08:49 AM
  3. Replies: 0
    Last Post: 02-15-2013, 07:55 AM
  4. Replies: 6
    Last Post: 09-02-2012, 04:30 PM
  5. Optimising lookups in a large fixed table
    By u38cg in forum Queries
    Replies: 4
    Last Post: 06-22-2011, 08:21 AM

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