Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

rst.MoveLast taking forever!

Avatar of ltdanp22
ltdanp22Flag for United States of America asked on
DB2SQL
4 Comments1 Solution683 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Avatar of harfang
harfangFlag of Switzerland image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answers