Need for Speed (not the white stuff) 1

[Vulton]

Ok I have read the FAQ's and searched I don't even know how many boards so now it's time to beg some help.

I have several functions that read/write to a backend SQL database. I have read that it is not good to leave open a record set and to let connection pooling do its job. I had the recorsets opening on form load and closing on form close but since the form stays open for several days I think this was messing up the SQL server.

So now I have this code that opens the record set and closes the record set for each call to the function. This is not working fast enough since this application is reading barcodes at a much faster rate than it is processing them.

ANY suggestions would be GREATLY appreciated. Please and Thanks.

Private Function CountMolding()

    MySQL = "SELECT * FROM TblItemTransactionTracking"
    Set MC_RS = MyDB.OpenRecordset(MySQL, dbOpenDynaset, dbSeeChanges)
    MySQL = "SELECT * FROM TblItemTransactionTrackingHistory"
    Set MCH_RS = MyDB.OpenRecordset(MySQL, dbOpenDynaset, dbSeeChanges)
    
    MCFC = MC_RS.Fields.Count

    MC_RS.FindFirst "[ITT_CJHLI] = '" & Right(Me.MM_BarCodeScanned, Len(Me.MM_BarCodeScanned) - 7) & "'"
    
    If MC_RS.NoMatch Then
        
        Me.MM_CurrentCount.Value = 1
        
        With MC_RS
            .AddNew
            .Fields("ITT_StationID").Value = Left(Me.MM_BarCodeScanned, 6)
            .Fields("ITT_Action").Value = "Count"
            .Fields("ITT_CJHLI").Value = Right(Me.MM_BarCodeScanned, Len(Me.MM_BarCodeScanned) - 7)
            .Fields("ITT_Location").Value = Me.MM_CurrentCount.Value
            .Fields("ITT_Inserter").Value = CurrentUser()
            .Fields("ITT_DateStamp").Value = Now()
            DoEvents
            .Update
        End With
        
    Else
        
        Me.MM_CurrentCount.Value = Val(MC_RS.Fields("ITT_Location").Value) + 1
        
        MCH_RS.AddNew
        
        For MCCount = 0 To MCFC - 1
            MCH_RS.Fields(MCCount + 1).Value = MC_RS.Fields(MCCount).Value
            DoEvents
        Next MCCount
            
            MCH_RS.Fields("ITTH_AppendedDate").Value = Now()
            MCH_RS.Fields("ITTH_AppendedBy").Value = CurrentUser()
            MCH_RS.Update
            
        With MC_RS
            .Edit
            .Fields("ITT_StationID").Value = Left(Me.MM_BarCodeScanned, 6)
            .Fields("ITT_Action").Value = "Count"
            .Fields("ITT_Location").Value = Me.MM_CurrentCount.Value
            .Fields("ITT_Inserter").Value = CurrentUser()
            .Fields("ITT_DateStamp").Value = Now()
            DoEvents
            .Update
        End With

    End If

    MC_RS.Close
    MCH_RS.Close
    
End Function

 

[fredericofonseca]

change your SQL to retrive only the records required instead of doing a retrieve of ALL records.

Use Stored Procedures to proceed with the insert/update of the records you need.

That should make it faster.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Vulton]

Thanks Frederico,

I am learning SQL/VB as I go and haven't gotten to stored Procedures yet but I did find DoCmd.RunSQL so now I have this...

Private Function CountMolding()

    MySQL = "SELECT * FROM TblItemTransactionTracking WHERE [ITT_CJHLI] = '" & Right(Me.MM_BarCodeScanned, Len(Me.MM_BarCodeScanned) - 7) & "'"
    Set MC_RS = MyDB.OpenRecordset(MySQL, dbOpenDynaset, dbSeeChanges)
    
    If MC_RS.RecordCount = 0 Then
    
        Me.MM_CurrentCount.Value = 1
        
        With MC_RS
            .AddNew
            .Fields("ITT_StationID").Value = Left(Me.MM_BarCodeScanned, 6)
            .Fields("ITT_Action").Value = "Count"
            .Fields("ITT_CJHLI").Value = Right(Me.MM_BarCodeScanned, Len(Me.MM_BarCodeScanned) - 7)
            .Fields("ITT_Location").Value = Me.MM_CurrentCount.Value
            .Fields("ITT_Inserter").Value = CurrentUser()
            .Fields("ITT_DateStamp").Value = Now()
            DoEvents
            .Update
        End With
        
    Else
    
        MySQL = "INSERT INTO TblItemTransactionTrackingHistory ( ITTH_StationID, ITTH_Action, ITTH_CJHLI, ITT_Location, ITTH_Inserter, ITTH_DateStamp, ITTH_Longitude, ITTH_Latitude, ITTH_AppendedDate, ITTH_AppendedBy )"
        MySQL = MySQL & " SELECT ITT_StationID, ITT_Action, ITT_CJHLI, TblItemTransactionTracking.ITT_Location, ITT_Inserter, ITT_DateStamp, ITT_Longitude, ITT_Latitude, Now(), CurrentUser() FROM TblItemTransactionTracking"
        MySQL = MySQL & " WHERE [ITT_CJHLI] = '" & Right(Me.MM_BarCodeScanned, Len(Me.MM_BarCodeScanned) - 7) & "'"
        
        DoCmd.SetWarnings False
        DoCmd.RunSQL (MySQL)
        DoCmd.SetWarnings True

        Me.MM_CurrentCount.Value = Val(MC_RS.Fields("ITT_Location").Value) + 1
        
            With MC_RS
                .Edit
                .Fields("ITT_StationID").Value = Left(Me.MM_BarCodeScanned, 6)
                .Fields("ITT_Action").Value = "Count"
                .Fields("ITT_Location").Value = Me.MM_CurrentCount.Value
                .Fields("ITT_Inserter").Value = CurrentUser()
                .Fields("ITT_DateStamp").Value = Now()
                DoEvents
                .Update
            End With

    End If

    MC_RS.Close
    
End Function

It is faster and will work for now untill I learn enough to do it "right".