Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2013
    Posts
    2

    Unanswered: Slow down with multiple users

    I currently have a database that handles inventory control with consignment and billing that when 2 users run their front end files one of them slows down a second to easily 10-15 seconds depending on the data being returned. The database is instantaneous when only one user is accessing the file.

    Running office 2007 on all the pcs with them all being up to date with microsoft updates.
    Currently the database is split with each using having their own front end accde file.
    I also have a persistent connection setup on my main menu in the record source. The main menu cannot be closed without completely terminating access.
    Currently all users access the file via a VPN but I have tried putting it on the local network on server grade hardware and is still slows down with 2 people accessing the file.

    The big issue I have run into is combo box that is used to select items is extremely slow with multiple users. This combo box returns currently 1321 records. I have the combo box set as a snapshot. The bound column is set to 3. I have an index setup on the Subcategory and Product columns in the table.

    The query for the combo box is:
    Code:
    SELECT [Subcategory] & "-" & [Productname] AS Product, tblProductCategory.Category, tblProduct.ProductID, tblProduct.ListPrice, tblProduct.PackagedBy, AvailableTotal([ProductID]) AS [Qty Available]
    FROM tblProductSubCategory INNER JOIN (tblProductCategory INNER JOIN tblProduct ON tblProductCategory.CategoryID = tblProduct.CategoryID) ON tblProductSubCategory.SubcategoryID = tblProduct.SubcategoryID
    WHERE (((tblProduct.Discontinued)=0)) OR ((([Forms]![frmOrder].[txtStatusID])>=4))
    ORDER BY tblProductSubCategory.Subcategory, tblProduct.ProductName;
    I have an index setup on all the dates columns in the tables. Below code is modified from allenbrowne's inventory calculation from here Allen Browne - Inventory Control: Quantity on Hand

    The module code to generate the Qty Available is
    Code:
    Option Compare Database
    Option Explicit
    
    Function AvailableTotal(vProductID As Variant, Optional vAsOfDate As Variant) As Long
    'Purpose:   Return the quantity-on-hand for a product.
    'Arguments: vProductID = the product to report on.
    '           vAsOfDate  = the date at which quantity is to be calculated.
    '                           If missing, all transactions are included.
    'Return:    Quantity on hand. Zero on error.
        Dim db As DAO.Database          'CurrentDb()
        Dim rs As DAO.Recordset         'Various recordsets.
        Dim lngProduct As Long          'vProductID as a long.
        Dim strAsOf As String           'vAsOfDate as a string.
        Dim strSTDateLast As String     'Last Stock Take Date as a string.
        Dim strDateClause As String     'Date clause to use in SQL statement.
        Dim strSQL As String            'SQL statement.
        Dim lngQtyLast As Long          'Quantity at last stocktake.
        Dim lngQtyAcq As Long           'Quantity acquired since stocktake.
        Dim lngQtyUsed As Long          'Quantity used since stocktake.
        Dim lngQtyRet As Long           'Quantity returned since stocktake
        Dim lngQtySmallUsed As Long     'Quantity used for small orders since stocktake
        Dim lngQtyOrder As Long         'Quantity on order since stockdate
    
        If Not IsNull(vProductID) Then
            'Initialize: Validate and convert parameters.
            Set db = CurrentDb()
            lngProduct = vProductID
            If IsDate(vAsOfDate) Then
                strAsOf = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#"
            End If
    
            'Get the last stocktake date and quantity for this product.
            If Len(strAsOf) > 0 Then
                strDateClause = " AND (StockTakeDate <= " & strAsOf & ")"
            End If
            strSQL = "SELECT TOP 1 StockTakeDate, Quantity FROM tblProductStock " & _
                     "WHERE ((ProductID = " & lngProduct & ")" & strDateClause & _
                     ") ORDER BY StockTakeDate DESC;"
    
            Set rs = db.OpenRecordset(strSQL)
            With rs
                If .RecordCount > 0 Then
                    strSTDateLast = "#" & Format$(!StockTakeDate, "mm\/dd\/yyyy") & "#"
                    lngQtyLast = Nz(!Quantity, 0)
                End If
            End With
            rs.Close
    
            'Build the Date clause
            If Len(strSTDateLast) > 0 Then
                If Len(strAsOf) > 0 Then
                    strDateClause = " Between " & strSTDateLast & " And " & strAsOf
                Else
                    strDateClause = " >= " & strSTDateLast
                End If
            Else
                If Len(strAsOf) > 0 Then
                    strDateClause = " <= " & strAsOf
                Else
                    strDateClause = vbNullString
                End If
            End If
    
            'Get the quantity acquired since then.
            strSQL = "SELECT Sum(tblVendorOrderDetail.QuantityReceived) AS QuantityAcq " & _
                     "FROM tblVendorOrder INNER JOIN tblVendorOrderDetail ON tblVendorOrder.OrderID = tblVendorOrderDetail.OrderID " & _
                     "WHERE ((tblVendorOrderDetail.ProductID = " & lngProduct & ")) AND ((tblVendorOrderDetail.PostInventory) = -1"
            If Len(strDateClause) = 0 Then
                strSQL = strSQL & ");"
            Else
                strSQL = strSQL & " AND (tblVendorOrderDetail.DateReceived " & strDateClause & "));"
            End If
    
            Set rs = db.OpenRecordset(strSQL)
            If rs.RecordCount > 0 Then
                lngQtyAcq = Nz(rs!QuantityAcq, 0)
            End If
            rs.Close
    
            'Get the quantity Ordered since then.
            strSQL = "SELECT Sum(tblVendorOrderDetail.QuantityOrdered) AS QuantityOrder " & _
                     "FROM tblVendorOrder INNER JOIN tblVendorOrderDetail ON tblVendorOrder.OrderID = tblVendorOrderDetail.OrderID " & _
                     "WHERE ((tblVendorOrderDetail.ProductID = " & lngProduct & ")) AND ((tblVendorOrderDetail.PostInventory) = 0"
            If Len(strDateClause) = 0 Then
                strSQL = strSQL & ");"
            Else
                strSQL = strSQL & " AND (tblVendorOrder.OrderedDate " & strDateClause & "));"
            End If
    
            Set rs = db.OpenRecordset(strSQL)
            If rs.RecordCount > 0 Then
                lngQtyOrder = Nz(rs!QuantityOrder, 0)
            End If
            rs.Close
    
            'Get the quantity returned since then
            strSQL = "SELECT Sum(tblReturnDetail.QuantityReturned) AS QuantityRet " & _
                     "FROM tblReturn INNER JOIN tblReturnDetail ON tblReturn.ReturnID = tblReturnDetail.ReturnID " & _
                     "WHERE (tblReturnDetail.ProductID = " & lngProduct & ") AND ((tblReturnDetail.QuantityReturned) Is Not Null"
            If Len(strDateClause) = 0 Then
                strSQL = strSQL & ");"
            Else
                strSQL = strSQL & " AND (tblReturn.ReturnDate " & strDateClause & "));"
            End If
    
            Set rs = db.OpenRecordset(strSQL)
            If rs.RecordCount > 0 Then
                lngQtyRet = Nz(rs!QuantityRet, 0)
            End If
            rs.Close
    
            'Get the quantity used in orders since then.
            strSQL = "SELECT Sum(tblOrderDetail.Quantity) AS QuantityUsed " & _
                     "FROM tblOrder INNER JOIN tblOrderDetail ON " & _
                     "tblOrder.OrderID = tblOrderDetail.OrderID " & _
                     "WHERE ((tblOrderDetail.ProductID = " & lngProduct & ")"
            If Len(strDateClause) = 0 Then
                strSQL = strSQL & ");"
            Else
                strSQL = strSQL & " AND (tblOrder.OrderDate " & strDateClause & "));"
            End If
    
            Set rs = db.OpenRecordset(strSQL)
            If rs.RecordCount > 0 Then
                lngQtyUsed = Nz(rs!QuantityUsed, 0)
            End If
            rs.Close
    
            'Get the quantity used in small orders since then.
            strSQL = "SELECT Sum(tblOrderSmallDetail.Quantity) AS QuantitySmallUsed " & _
                     "FROM tblOrderSmall INNER JOIN tblOrderSmallDetail ON " & _
                     "tblOrderSmall.SmallOrderID = tblOrderSmallDetail.SmallOrderID " & _
                     "WHERE ((tblOrderSmallDetail.ProductID = " & lngProduct & ")"
            If Len(strDateClause) = 0 Then
                strSQL = strSQL & ");"
            Else
                strSQL = strSQL & " AND (tblOrderSmall.OrderDate " & strDateClause & "));"
            End If
            Set rs = db.OpenRecordset(strSQL)
            If rs.RecordCount > 0 Then
                lngQtySmallUsed = Nz(rs!QuantitySmallUsed, 0)
            End If
            rs.Close
    
            'Assign the return value
            AvailableTotal = lngQtyLast + lngQtyAcq + lngQtyRet + lngQtyOrder - lngQtyUsed - lngQtySmallUsed
        End If
    
        Set rs = Nothing
        Set db = Nothing
        Exit Function
    End Function
    Does anyone have any idea on how I can improve on these queries or improve the database overall. All my research so far shows that I have done everything I can to optimize these queries but still is extremely slow with just 2 users.

  2. #2
    Join Date
    Oct 2010
    Posts
    9
    I do capturing of medicines and also use a "Live stock on hand" textbox and mine with 5 people and 100 000 entries is instant.
    My sql programming is not at your standard so i decided to approach it a bit differently.

    I have created a queries for the last stocktake quantity and the date that item was counted as well as the incoming and outgoing items. The stock that has gone out or came in after the last stocktake is then called up on my order form using dlookup after filtering it with >=Nz([forms]![OrderF]![maxdate]) which is on my capturing form to filter. I add and subtract then on the capturing form in the background to display On hand total.

    i use [MaxDate: Stocktakedate] function in my query and it's set to "last". The qty is also set to "last".
    example to filter data:
    =DLookUp("[SumOfStockOut]","qrySOHOut","[item_lookup] =" & nz([Forms]![OrderF]![sfOrderICN]![item_lookup],0)) - this is how i individually filter the in and out and stocktake item from the combobox where it is captured.
    Hope i did not confuse you to much but it works great for me.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what actually is the sql used for
    are you using all those columns in the combo box
    do you need the join onthe catergory AND the sub category

    im not to sure what place ' OR ((([Forms]![frmOrder].[txtStatusID])>=4)) has in this query

    opening database connections is always expensive its rare you actually need to open one, usuall you can use the currentdb object

    do you know where the time is going
    what metrics have you got?
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2013
    Posts
    2
    Quote Originally Posted by healdem View Post
    what actually is the sql used for
    are you using all those columns in the combo box
    do you need the join onthe catergory AND the sub category
    Unfortunately I need this information to be displayed. However the queries used by the combo box itself loads quick. I find the big slow down comes from AvailableTotal. All the other data is already showing when you click the dropdown but the AvailableTotal is still loading.
    im not to sure what place ' OR ((([Forms]![frmOrder].[txtStatusID])>=4)) has in this query
    I have discontinued items in my drop down but the issue I found is that if an item is discontinued and you go to view an old order the combobox is blank. An order with a status of 4 or higher is a completed order and we want discontinued items to still display but the order is locked from editing.
    opening database connections is always expensive its rare you actually need to open one, usuall you can use the currentdb object

    do you know where the time is going
    what metrics have you got?
    Currentdb is used in the module. I am unsure what you were trying to say here?

    Most of the time goes into the processing the qty available. I do not have metrics on this. I could time it but the difference between 1 and 2 users logged in is visually noticeable. With one user on you can type into the combo box and get the record you want very quickly. If 2 users are on the combo box goes to a crawl with the Qty Available and you have to sit there and wait for the combo box to move.

    I currently have the combobox set to return only 10 records at a time to decrease the amount of records it queries the Qty Available for at a time but it is not helping enough.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if calculating the stock is consuming the time, then I'd strongly recommend that you don't do it when filling the combo box. you are doing a lot of uneccessary calculations for the 1/1300 products you actually want. I'd calculate the stock once a user has selected the product

    id want to use the query planner to try and make certain the query plan is reasonable,. or at least get a better grip of where the time is going
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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