rst.MoveLast taking forever!

ltdanp22
ltdanp22 used Ask the Experts™
on
Hello all,

I was under the impression that recordsets were supposed to be a fast way to move around within data. The attached function is taking forever to run. It's stuck on rst.MoveLast. The table Demand_Check_C has several million records but it's taking an hour to get to the 2Mth record. Is this normal?
'Speed up qry10002 with literals
Public Function GetDemand()
    Dim dbs As Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim sInsert As String, sSelect As String, sFrom As String, sWhere As String, sSQL As String
    Dim lRecordCount As Long: lRecordCount = 0
    Dim lCounter As Long
    
    DoCmd.SetWarnings False
    
    DoCmd.RunSQL "DELETE * FROM tblDemand"

    'sInsert = "INSERT INTO tblDemand"
    sSelect = " SELECT LOWES.T2398_IFM_FRC_VAL.T024_ITM_NBR, LOWES.T2398_IFM_FRC_VAL.T063_LCT_NBR, CASE WHEN SEN_BGN_WK_NBR<=SEN_END_WK_NBR THEN SEN_END_WK_NBR-SEN_BGN_WK_NBR+1 ELSE 52-SEN_BGN_WK_NBR+SEN_END_WK_NBR+1 END AS ""Selling Weeks"", " & _
    " LOWES.T2355_SEN_PRL.WK1_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK2_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK3_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK4_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK5_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK6_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK7_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK8_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK9_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK10_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK11_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK12_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK13_BAS_IND_FCT AS ""Q1 Sum of BIs""," & _
    " LOWES.T2355_SEN_PRL.WK14_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK15_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK16_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK17_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK18_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK19_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK20_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK21_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK22_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK23_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK24_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK25_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK26_BAS_IND_FCT AS ""Q2 Sum of BIs""," & _
    " LOWES.T2355_SEN_PRL.WK27_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK28_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK29_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK30_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK31_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK32_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK33_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK34_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK35_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK36_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK37_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK38_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK39_BAS_IND_FCT AS ""Q3 Sum of BIs""," & _
    " LOWES.T2355_SEN_PRL.WK40_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK41_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK42_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK43_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK44_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK45_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK46_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK47_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK48_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK49_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK50_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK51_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK52_BAS_IND_FCT AS ""Q4 Sum of BIs"""
    sFrom = " FROM (LOWES.T2354_ITM_LCT_PRL INNER JOIN LOWES.T2355_SEN_PRL ON LOWES.T2354_ITM_LCT_PRL.T2355_PRL_TAG_ID = LOWES.T2355_SEN_PRL.T2355_PRL_TAG_ID) INNER JOIN LOWES.T2398_IFM_FRC_VAL ON (LOWES.T2354_ITM_LCT_PRL.T063_LCT_NBR = LOWES.T2398_IFM_FRC_VAL.T063_LCT_NBR) AND (LOWES.T2354_ITM_LCT_PRL.T024_ITM_NBR = LOWES.T2398_IFM_FRC_VAL.T024_ITM_NBR)"
    sWhere = " WHERE (LOWES.T2398_IFM_FRC_VAL.T024_ITM_NBR = -1 AND LOWES.T2398_IFM_FRC_VAL.T063_LCT_NBR = -1)"

    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("SELECT T024_ITM_NBR, T063_LCT_NBR FROM Demand_Check_C;")

    If rst.EOF Then
        lRecordCount = 0
    Else
        rst.MoveLast
        lRecordCount = rst.RecordCount
    End If

    rst.MoveFirst

    Do While Not rst.EOF

        lCounter = lCounter + 1
        sWhere = sWhere & " OR (LOWES.T2398_IFM_FRC_VAL.T024_ITM_NBR = " & rst("T024_ITM_NBR") & " AND LOWES.T2398_IFM_FRC_VAL.T063_LCT_NBR = " & rst("T063_LCT_NBR") & ")"

        If lCounter > 60 Or rst.AbsolutePosition = lRecordCount - 1 Then
            sSQL = sInsert & sSelect & sFrom & sWhere ' & " WITH UR"
            Set qdf = CurrentDb().QueryDefs("qryPassThrough")
            qdf.SQL = sSQL

            DoCmd.OpenQuery "qryDemand"

            'DoCmd.RunSQL sSQL
            lCounter = 0
            sWhere = " WHERE (LOWES.T2398_IFM_FRC_VAL.T024_ITM_NBR = -1 AND LOWES.T2398_IFM_FRC_VAL.T063_LCT_NBR = -1)" '-1 = dummy # to deal with 1st OR
        End If

        rst.MoveNext

    Loop
    
    DoCmd.SetWarnings True
    
    rst.Close

End Function

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hamed NasrRetired IT Professional

Commented:
Is the issue with this function, or with moveLast method?
Is this the first time you encouter this issue?

Author

Commented:
The issue is with the MoveLast method. The function runs but the rst.MoveLast method takes forever. It seems so simple to just move to the last record in a query that it shouldn't take hours.
Yes, this seems normal.

It isn't moving to the last record, it's populating all records to the last, in a dynamic recordset (you can potentially edit any record and it should reflect any editing taking place dynamically!). It's not simple, but it's useless.

If Demand_Check_C is an Access table, you can open it in table mode and you can read the last record instantly (but a table doesn't have a record cont). If it isn't, you can use dbForwardOnly to speed things up, but, as the name implies, you cannot move back.

Basically, your function might very well run for a dozen hours, It's not clear what it does, but if you are in the process of normalising a table structure, some operations can take that sort of time. But you don't need the record count.

Use a table-type or a forward only recordset, use the 60 records chunks (still yielding in the order of 100'000 non-trivial insert queries to analyse, optimise, and run), and copy the inner section outside of the loop to get the less-than-60 records from the end.

Do not use the total record count or absolute positions.

(°v°)
as i understand from your code, the only reason you perform the move last is to count the records in the table,
instead, just issue this query
select count(*) from Demand_Check_C

if the table has an index created on it (any index), this will run much faster

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial