Results 1 to 8 of 8
  1. #1
    SSSlippy is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Posts
    5

    Need help optimizing queries


    Background:
    Our orders go through a set of statuses based on where they are in the process.
    The statuses used in this query are the following:
    0 = no stock
    1 = back order
    2 = in stock
    3 = allocated

    I have a function written that gets currently how many we have onhand and I have a function that tells me how many we have availabletotal which includes product still coming from our vendors on top of what we have physically in stock.

    Issue:
    We must have an allocated status due to we are a consignment business and orders are done months out. The allocation query I run takes a long time for an order of 200+ items. I am looking to see if anyone can recommend an optimization for my queries or should I even be going at this another way completely?

    Code:
            'Set status to allocated if we have enough on hand         
             strSQL = "UPDATE tblOrderDetail SET tblOrderDetail.StatusID = 3 " & _
                       "WHERE (((tblOrderDetail.OrderID)=" & txtOrderID & ") AND ((tblOrderDetail.StatusID)<=2) AND ((tblOrderDetail.Quantity)<=OnHand([ProductID])));"
             CurrentDb.Execute strSQL, dbFailOnError
             'Set status to back order if we have product coming in from vendors
             strSQL = "UPDATE tblOrderDetail SET tblOrderDetail.StatusID = 1 " & _
                       "WHERE (((tblOrderDetail.OrderID)=" & txtOrderID & ") AND ((tblOrderDetail.StatusID)=0 Or (tblOrderDetail.StatusID)=2) AND ((tblOrderDetail.Quantity)>=AvailableTotal([ProductID])));"
             CurrentDb.Execute strSQL, dbFailOnError
             'Set status to no stock if we do not have enough on hand
             strSQL = "UPDATE tblOrderDetail SET tblOrderDetail.StatusID = 0 " & _
                       "WHERE (((tblOrderDetail.OrderID)=" & txtOrderID & ") AND ((tblOrderDetail.StatusID) Between 1 And 2) AND ((tblOrderDetail.Quantity)>=OnHand([ProductID])));"
             CurrentDb.Execute strSQL, dbFailOnError

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Well, the slowdown is probably due to the custom functions.

    Saving calculated data can be bad idea as the saved value can become 'out of sync' with raw data.

    Instead of saving this calculated status value to table, just calculate it when needed. If it can be calculated for UPDATE it can be calculated when needed.

    Design one function to return correct value based in input arguments.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly is
    a long time for an order of 200+ items
    ?
    How often do you run these queries?

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Did you step through the code to see how long each execution takes? You may need to only consider optimizing 1 of the 3. At first I thought you're running all 3 in succession but now I think that's not the case - you've omitted part of the code in between those statements. What I don't see is how the value is returned that decides which of those to run. Is that from a DLookup you're not showing?

    While likely not a huge contributor, it's better to Dim db As CurrentDb and use db.Execute. Each time you invoke CurrentDb you are creating a new database object in memory. Probably no longer a problem with today's processors, but it's what I'd do. However, that would only apply to when you're running several Executes in succession. You seem to be doing only one of the three.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    SSSlippy is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Posts
    5
    Quote Originally Posted by June7 View Post
    Well, the slowdown is probably due to the custom functions.

    Saving calculated data can be bad idea as the saved value can become 'out of sync' with raw data.

    Instead of saving this calculated status value to table, just calculate it when needed. If it can be calculated for UPDATE it can be calculated when needed.

    Design one function to return correct value based in input arguments.
    Each item has its own individual status due to the way the business functions. It is consignment base and available inventory is calculated every time something is loaded. However we need to store the status of the item. The function I call is based on allen brownes inventory that calculates on the fly. Reference URL. http://allenbrowne.com/AppInventory.html


    Quote Originally Posted by orange View Post
    What exactly is ?
    How often do you run these queries?
    I need to optimize these somehow. This is run once per order and it changes the status of each item on the order.

    Quote Originally Posted by Micron View Post
    Did you step through the code to see how long each execution takes? You may need to only consider optimizing 1 of the 3. At first I thought you're running all 3 in succession but now I think that's not the case - you've omitted part of the code in between those statements. What I don't see is how the value is returned that decides which of those to run. Is that from a DLookup you're not showing?

    While likely not a huge contributor, it's better to Dim db As CurrentDb and use db.Execute. Each time you invoke CurrentDb you are creating a new database object in memory. Probably no longer a problem with today's processors, but it's what I'd do. However, that would only apply to when you're running several Executes in succession. You seem to be doing only one of the three.
    I am running all 3 in succession. This changes the status of each item on the order. Due to it being consignment each item must be tracked on the order. This is exactly the code. The other code which I did not include just checks to make sure the fields are filled in. The onhand and quantityavailable use http://allenbrowne.com/AppInventory.html as their code. Can you provide an example of how to use db.execute as this is why I am here to try and optimize these queries to shed time from them running.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    You can do this with one execution, not sure if it would be significantly quicker, because of the functions you would have to try it and see
    Code:
    strSQL = "UPDATE tblOrderDetail SET" & _
    " StatusID = choose(StatusID<=2 and Quantity<=OnHand(ProductID),3,StatusID= in (0,2) AND Quantity>=AvailableTotal(ProductID),1,StatusID in (1,2) AND Quantity>=OnHand(ProductID),0)" & _
    " WHERE OrderID=" & txtOrderID
    however it may be that the second condition which might change the status to 1 then affects the 3rd condition.

    If we knew the function code you are actually using (not the generic one provided by Allen) might be able to do all of this in sql which will be significantly faster

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    You're having to calculate OnHand twice, 1st and 3rd query.
    Why not have a new initial query that simply calculates OnHand and AvailableTotal for the pertinent ProductIDs, then join that to your existing queries?

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Can you provide an example of how to use db.execute
    Dim db As DAO.Database
    ...
    Set db = CurrentDb
    ...
    db.Execute

    One function seems to be running 2x. If the parameter value doesn't change between calls, run it once before the executions and pass it to a variable, then use the variable instead. However, I'm not following the logic.
    Say status is <=2:

    Do this
    SET tblOrderDetail.StatusID = 3
    WHERE this
    tblOrderDetail.StatusID)<=2
    And this
    tblOrderDetail.Quantity)<=OnHand([ProductID]

    AFAIK, this sql statement will create an execution plan even if not this -> tblOrderDetail.StatusID)<=2
    because it will need to resolve this -> OnHand([ProductID]

    so you run the function anyway, which is a waste of time and may be part of the problem, and in the end, the plan doesn't get executed. I think you need to figure out how to create the condition that runs only one of them (or at least only the ones you really need). Perhaps DLookup tblOrderDetail.StatusID as well as run the function only once and for both, use variables in the sql statement. Then use an IF block to form only one plan and execute it. I'd also again recommend stepping through to see how long things take as well as alter the CurrentDb thing.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Optimizing form's RecordSource
    By ArviLaanemets in forum Access
    Replies: 7
    Last Post: 10-06-2021, 03:53 AM
  2. Replies: 19
    Last Post: 04-13-2020, 03:15 PM
  3. Optimizing MS Access Query
    By mariost in forum Queries
    Replies: 3
    Last Post: 11-27-2015, 04:13 AM
  4. Help optimizing a query
    By mkallover in forum Queries
    Replies: 0
    Last Post: 03-01-2012, 09:13 AM
  5. Help optimizing an ugly query
    By kman42 in forum Queries
    Replies: 2
    Last Post: 07-26-2011, 07:37 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