rst.MoveLast method takes hours.

ltdanp22
ltdanp22 used Ask the Experts™
on
I have a recordset based on a simple query that contains several million records...

("SELECT T024_ITM_NBR, T063_LCT_NBR FROM Demand_Check_C;")

When the code kicks off the rst.MoveLast method, it takes hours to run that one line. Is this normal or am I doing something wrong? It seems like such a simple thing to just move to the last record in a recordset.

If this is normal, is there a way to figure out the number of records in a recordset more quickly (I'm calling the MoveLast method in a block of code that determines the number of records in a recordset)?

The complete function is available as an attachment.
'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®
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Instead of moving to last then back to first then iterating again, try this

    If rst.EOF Then
        lRecordCount = 0
    Else
        lRecordCount = DCount("T024_ITM_NBR", "Demand_Check_C", "")
    End If

    rst.MoveFirst
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<  lRecordCount = DCount("T024_ITM_NBR", "Demand_Check_C", "")>>
  Great idea!  But I would amend that to:
  lRecordCount = DCount("*", "Demand_Check_C")
  The * will include Nulls, but it's highly optimized and will return the count faster.
JimD.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
" several million records..."

How many million to be exact ?
What is the Compacted size of the MDB?

Is Demand_Check_C a local table? If so, the fastest way to get the record count is:

Dim x as Long
x = CurrentDb.TableDefs("Demand_Check_C").RecordCount

mx
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Top Expert 2009

Commented:
This piece of code just needs one line removed:
    If rst.EOF Then
        lRecordCount = 0
    Else
        rst.MoveLast
        lRecordCount = rst.RecordCount
    End If

Make it:
    If rst.EOF Then
        lRecordCount = 0
    Else
        lRecordCount = rst.RecordCount
    End If
 
You don't have to move to the last record to get the record count.
Top Expert 2009

Commented:
Opps, I mis-spoke. Change:
    Set rst = dbs.OpenRecordset("SELECT T024_ITM_NBR, T063_LCT_NBR, COUNT (*) AS [RecCnt] FROM Demand_Check_C;")

lRecordCount = rst!RecCnt
 

Commented:
why not use the property:

RecCnt = currentdb.openrecordset("Demand_Check_C").RecordCount

If you already have the recordset open as rst then

RecCnt = rst.RecordCount
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"You don't have to move to the last record to get the record count."
Actually you do!

mx
Top Expert 2009

Commented:
Yeah, i realized that after I posted, DatabaseMX, that's why I corrected myself with a different method. But thanks for the head-up! Good catch.

Commented:
I know I posted late, but I am able to get the record count by using the RecordCount property of either a TableDef or a Recordset - mx - what do you mean "Actually you do!"
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
np.  And I should note ... that applies when going the rst route ...

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"mx - what do you mean "Actually you do!""

Referring to http:#a33114589  ... not your post.

mx

Commented:
Well, if you use the recordset approach, as long as you specify as a type - dbOpenTable (or leave the type blank as dbOpenTable is the default), you do not have to rst.MoveLast.  Specifying dbOpenSnapshot, dbOpenDynaSet or dbForwardOnly will give you a value of 1 unless you rst.Movelast.  If you use the tabledef approach, the RecordCount property is always accurate.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
"If you use the tabledef approach, the RecordCount property is always accurate."

Which unfortunately does not work on linked tables - the TableDef approach.

From Help:

"Use the RecordCount property to find out how many records in a Recordset or TableDef object have been accessed. ******>>> The RecordCount property doesn't indicate how many records are contained in a dynaset-, snapshot-, or forward-only–type Recordset object until all records have been accessed. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast method on the Recordset object. You can also use an SQL Count function to determine the approximate number of records your query will return."

Commented:
So I see,  thanks Joe.  
Top Expert 2009

Commented:

Author

Commented:
Problem was that I joined a local table to a DB2 table in a query called by the Demand_Check_C query. After fixing this I tried DCount and the original code and both are almost instantaneous with 7M records.

Commented:
Interesting how often we are the victims of our own invention;-)  Good work sorting it out.

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