Anyway to speed this up.....

[krymat]

Function EfficParm(ParmType As String, processname As String, MachineType As String, Machine As String, proddate As Date)
Dim dbs As DATABASE, rst As Recordset, RecCount As Integer, PType As Integer

Set dbs = CurrentDb

Select Case ParmType
Case "upt"
PType = 4
Case "utilhrs"
PType = 5
Case "unitcount"
PType = 6
Case "goal"
PType = 7
Case Else
PType = 0
Exit Function
End Select

Select Case MachineType
Case "none"
strSQL = "SELECT * FROM tblAccounting_EfficParms WHERE [processname] = '" & processname & "' And [machine] = '" & Machine & "' ORDER BY [effec_date]"
Case Else
strSQL = "SELECT * FROM tblAccounting_EfficParms WHERE [processname] = '" & processname & "' And [machine] = '" & Machine & "' And [machinetype] = '" & MachineType & "' ORDER BY [effec_date]"
End Select
Set rst = dbs.OpenRecordset(strSQL)
RecCount = rst.RecordCount
Select Case RecCount
Case 0
rst.Close
Case 1
EfficParm = rst.Fields(PType).Value
Case Else
rst.MoveLast
If rst.Fields(9).Value < proddate Then
EfficParm = rst.Fields(PType).Value
rst.Close
Exit Function
Else
rst.MoveFirst
End If
Do While Not rst.EOF
If rst.Fields(9).Value = proddate Then
EfficParm = rst.Fields(PType).Value
rst.Close
Exit Function
ElseIf rst.Fields(9).Value < proddate Then
rst.MoveNext
ElseIf rst.Fields(9).Value > proddate Then
rst.MovePrevious
EfficParm = rst.Fields(PType).Value
rst.Close
Exit Function
End If
Loop
End Select

End Function

This is what I came up with to deal with an effective date in dealing with machine efficiencies. Is what I'm doing here look correct, or could there be a way to make it better?

 

[ghubbell]

I can compile this but of course not run it so be careful, it may not behave as you expect. You might just want to read through the differences I think I see that would improve your speed:

Function EfficParm(ParmType As String, processname As String, Machinetype As String, Machine As String, proddate As Date)
Dim dbs As Database, rst As Recordset, PType As Integer, strSQL As String
Set dbs = CurrentDb
Select Case ParmType
Case &quot;upt&quot;
PType = 4
Case &quot;utilhrs&quot;
PType = 5
Case &quot;unitcount&quot;
PType = 6
Case &quot;goal&quot;
PType = 7
Case Else
Exit Function
End Select
If Machinetype = &quot;none&quot; Then
strSQL = &quot;SELECT * FROM tblAccounting_EfficParms WHERE [processname] = '&quot; & processname & &quot;' And [machine] = '&quot; & Machine & &quot;' ORDER BY [effec_date]&quot;
Else
strSQL = &quot;SELECT * FROM tblAccounting_EfficParms WHERE [processname] = '&quot; & processname & &quot;' And [machine] = '&quot; & Machine & &quot;' And [machinetype] = '&quot; & Machinetype & &quot;' ORDER BY [effec_date]&quot;
End If
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.RecordCount = 0 Then
rst.Close
Exit Function
End If
EfficParm = rst.Fields(PType).Value
Do Until rst.EOF
If rst.Fields(9).Value = proddate Then
EfficParm = rst.Fields(PType).Value
rst.Close
Exit Function
ElseIf rst.Fields(9).Value < proddate Then
rst.MoveNext
ElseIf rst.Fields(9).Value > proddate Then
rst.MovePrevious
EfficParm = rst.Fields(PType).Value
rst.Close
Exit Function
End If
Loop
End Function

Loose select statements if you at all can. As the code runs every select case is checked.
Use the Recordsets' recordcount property directly.
Your code:

Select Case RecCount
Case 0
rst.Close
Case 1
EfficParm = rst.Fields(PType).Value
Case Else
rst.MoveLast
If rst.Fields(9).Value < proddate Then
EfficParm = rst.Fields(PType).Value
rst.Close
Exit Function
Else
rst.MoveFirst
End If

Unless you move the recordset will either return 0 or 1 as it sits on the first record. Therefore, if I read it correctly, can go replaced by a check for 0.
I called the recordset of a specific type just cause I'm used to do so. I don't know if there would be an advantage.

Give it a try on some sample data. If it explodes, Sorry. If it improves your performance, Thanks. Good Luck,


Gord
ghubbell@total.net

 

[CPUburn]

Hi,

I cannot know the length of your recordset, but there is a way to speed up just the loop over the rst. I found it once in a review:


The following is a code example that does not iterate efficiently:

Sub Slow()
Dim d As Database
Dim r As Recordset
Set d = CurrentDB()
Set r = d.OpenRecordset(&quot;Order Details&quot
While Not r.EOF
r.Edit
r.Fields(&quot;Price&quot = r.Fields(&quot;Qty&quot * r.Fields(&quot;UnitCost&quot
r.Update
r.MoveNext
Wend
r.Close
End Sub
In the sample code above, the field variable &quot;lookup&quot; (that is, where the Visual Basic function equates variable names with database fields) for the three field variables Price, Qty, and UnitCost is performed in the same While loop in which the calculations are performed. In this configuration, both calculations and lookups must be performed inside the While loop, which is not an efficient design.

The following sample code is more efficient:
Sub Faster()
Dim d As Database
Dim r As Recordset
Dim Price As Field, Qty As Field, UnitCost As Field
Set d = CurrentDB()
Set r = d.OpenRecordset(&quot;Order Detail&quot
Set Price = r.Fields(&quot;Price&quot
Set Qty = r.Fields(&quot;Qty&quot
Set UnitCost = r.Fields(&quot;UnitCost&quot
While Not r.EOF
r.Edit
Price = Qty * UnitCost
r.Update
r.MoveNext
Wend
r.Close
End Sub
This example runs faster because Visual Basic performs the field lookup only once for each field and completes it before executing the loop. A direct reference to each of the three fields is then stored in the three field variables: Price, Qty, and UnitCost. As a result, no lookup is required in the While loop, and the field values are accessed and manipulated directly.

grtns

the kid

 

[RickSpr]

If your recordset is large, using a &quot;transaction&quot; should help. To use a transaction, insert this before you begin updating:
DBEngine.Workspaces(0).BeginTrans
Then, after you've completed updating, insert this:
DBEngine.Workspaces(0).CommitTrans

You must ensure that your BeginTrans call is balanced by a CommitTrans (or Rollback) call before you exit the procedure.

Using a transaction helps because some of the database updating is deferred. Table pages are only written when necessary, rather than for each record. But keep in mind that all the records you update will remain locked until the CommitTrans call, so this isn't a good thing to do when the database is being used by other users. (But then, this kind of global updating shouldn't be done in that situation, either.)

If possible, open your database in exclusive mode before running this process. In exclusive mode, you only have one lock--for the whole database--rather than one for each record updated.

Also if possible, run the code on the computer that houses the database, to avoid data transfer over the network. That can help more than anything else. Rick Sprague