My reserve numbre database is too slow!

[donnydooner]

I have a form with a procedure to reserve drawing numbers. The user is allowed to reserve up to 50 numbers at a time. As the database has gotten bigger it now takes about 5 minutes to reserve 50 numbers, whereas it used to be almost instantaneous. My procedure looks at the drawing number table to determine the next available number. How can I speed this up? I have compacted and repaired the database.

 

[jrbarnett]

Hi,

How is the database structured (tables/queries used)? What code if any is used in it to check?

John

 

[donnydooner]

I have a the following tables. DwgNumbers, Employees, Customers. There are no queries being used. Following is the code associated with the on-click command button on the Reserve Numbers form.

Private Sub ReserveNumbersCmd_Click()
On Error GoTo ReserveNumbersCmd_Click_Err

Dim rstDwgNumbers As Recordset
Dim dbsDwgNumbers As Database
Dim strMessage As String
Dim intOptions As Integer
Dim bytChoice As Byte

Set dbsDwgNumbers = CurrentDb
Set rstDwgNumbers = dbsDwgNumbers.OpenRecordset("tblDwgNumbers", dbOpenDynaset)

If Not IsNull(Me.Qty) Then 'Qty is filled in
strMessage = "You are about to reserve " & Me.Qty & " numbers." _
& " Do you want to continue?"
intOptions = vbYesNo
bytChoice = MsgBox(strMessage, intOptions)

If bytChoice = vbYes Then 'User wants to reserve numbers.
If Not IsNull(Me.MultDate) And Not IsNull(Me.Engineer) Then 'Reqd
'fields are full
If IsNull(Me.DwgNumber) Then 'DwgNumber is empty
'Flow Chart #2
For x = 1 To (Me.Qty)
DwgNumber = DMax("DwgNumber", "tblDwgNumbers", "tblDwgNumbers.DwgNumber&quot _
+ 1
With rstDwgNumbers
.AddNew
!DwgNumber = Me.DwgNumber
!DateAssigned = Me.MultDate
!EmployeeID = Me.Engineer
.Update
End With
Forms!frmReserveDwgNumbers.SetFocus
Next x
DoCmd.Close
DisplayMessage "You have successfully reserved " & (x - 1) & " numbers." _
& " Click 'Assign Reserved Drawing Numbers' on Main Switchboard to" _
& " assign these drawings." 'Don't need to exit sub because form closed.
Else 'DwgNumber is populated
DisplayMessage "Click 'Reserve Single Number' tab and make sure the 'Drawing " _
& "Number' box is empty then come back a try again."
End If
Else 'Reqd fields are not full
DisplayMessage "You must enter data in all required fields"
DoCmd.CancelEvent
End If
Else 'User doesn't want to reserve
DoCmd.CancelEvent
End If
Else 'Qty is not filled in
DisplayMessage "Enter the quantity of numbers you wish to reserve."
DoCmd.CancelEvent
End If

Exit_ReserveNumbersCmd_Click:
Exit Sub

ReserveNumbersCmd_Click_Err:
MsgBox Err.Description
Resume Exit_ReserveNumbersCmd_Click
End Sub

 

[jrbarnett]

Hi,

You mention that it worked like a rocket at first and slowed down. My guess is that this is due to the increasing amount of data in the system. Looking at the code, the first thing you can do is add an index to the field "dwgNumber" in the table "tblDwgNumbers" if it isn't the primary key in the table or already indexed (a primary key is already a type of index).

I have also rewritten slightly the code to be a little faster than your equivalent - things like using an insert query rather than a recordset to add the records to your table, only one reading the maximum value from the table and converting the quantity field to numeric rather than continually referring to the control on the form.

Private Sub ReserveNumbersCmd_Click()
On Error GoTo ReserveNumbersCmd_Click_Err
    
    Dim dbsDwgNumbers As DAO.Database
    Dim strMessage As String
    Dim qdfQuery As QueryDef
    Dim x As Integer
    Dim intLoopMax As Integer

    Set dbsDwgNumbers = CurrentDb

    If Not IsNull(Me.Qty) Then 'Qty is filled in
        If Not IsNumeric (Me.Qty) Then
            MsgBox "Error: Quantity field is not numeric", vbOkOnly+vbCritical
            DoCmd.CancelEvent
        End If    
        
        If MsgBox ("You are about to reserve " & Me.Qty & "  numbers." 
        & "  Do you want to continue?", vbYesNo+vbQuestion) = vbYes Then 'User wants to reserve numbers.
            If Not IsNull(Me.MultDate) And Not IsNull(Me.Engineer) Then 'Reqd
            'fields are full
                If IsNull(Me.DwgNumber) Then 'DwgNumber is empty
                   intLoopmax = Val (Me.Qty)
                   'Flow Chart #2
                    DwgNumber = DMax("DwgNumber", "tblDwgNumbers")
                    For x = 1 To intLoopmax
                        DwgNumber = DwgNumber + 1
                        Set dbsDWGNumber = qdfQuery.CreateQuerydef ("", "Insert Into tblDwgNumbers (DwgNumber, DateAssigned, EmployeeID) Values (" & Me.DwgNumber & ",#" & Me.multdate & "#," & Me.Engineer & ")")
                        qdfQuery.Execute
                    Next x
                    Forms!frmReserveDwgNumbers.SetFocus
                    DoCmd.Close acForm, Me.Name
                    DisplayMessage "You have successfully reserved " & (x - 1) & " numbers." _
                    & "  Click 'Assign Reserved Drawing Numbers' on Main Switchboard to" _
                    & " assign these drawings." 'Don't need to exit sub because form closed.
                Else 'DwgNumber is populated
                    DisplayMessage "Click 'Reserve Single Number' tab and make sure the 'Drawing " _
                    & "Number' box is empty then come back a try again."
                End If
            Else 'Reqd fields are not full
                DisplayMessage "You must enter data in all required fields"
                DoCmd.CancelEvent
            End If
        Else 'User doesn't want to reserve

            DoCmd.CancelEvent
        End If
    Else 'Qty is not filled in
        DisplayMessage "Enter the quantity of numbers you wish to reserve."
        DoCmd.CancelEvent
    End If

Exit_ReserveNumbersCmd_Click:
    dbsDWGNumbers.Close
    Exit Sub

ReserveNumbersCmd_Click_Err:
    MsgBox Err.Description
    Resume Exit_ReserveNumbersCmd_Click
End Sub

John

 

[MichaelRed]

An item in the process is troublesome. If the process is 'multiuser' (we ARE in Ms Access?) then it is easily possible for two or MORE users to be in the routine simultaneously - and this can (WILL) create some duplicatees, which I assume would be a problem. In the general schema, the only way I know to keep this from occuring is to open the recordset (table) for exclusive access. Adding 50 records using the looping process would keep the recordset "locked" for a long time, and have a greater probability of causing a second user to be denied access to the recordset.

While the original post states that the [/b]" ... user is allowed to reserve up to 50 numbers ..."[/b], there does not appear to be any constraint in the code. Of course, it is possible that this is in the form controls' events (After Update?), but that would only assure that the value for the single execution was limited, not the actual number of 'consecutative' drawing numbers, as at least at some times a user could simply repeat the procedure without any interveening execution by other users.

In the overall design of the process, it may be necessary to reserve a set of consecutative values. This , hoever is not the norm for databases, as the record sequence for relational databases is not generally considered as even useful, much less necessary. In that vein, it may be useful to at least ask the question(s): WHY is the organization doing this? Is some alternative acceptable? Like changing the drawing number formulation such that a "project" identifier is included, making the drawing "number" assignment somewhat more amenable to automatic assignment. What is the "track record" of using the drawing numbers assigned? e.g. If the sequence is for a specific "project", did that project use EXACTLY the reserved number set? If it used less than the number assigned, what weree the "unsed" number used for? How did this affect the 'tracking'?

Unless the answers to this are more-or-less conclusively in favor of the reservation system, the 'block' assignment should be 'reconsidered' -perhaps- in favor of a more standard assign as necessary )one at a time) approach.

If the system is simply a legacy aproach from days of old (manual drafting), it should really really REALLY be reconsidered.

If, on the other hand, the process truly has some merit (other than 'comfort zone' / always doone it that way / ...), there should be ansome addition to the process to at least avoid the possability of duplication.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[DougP]

This may be out of context, but why do they need to reserve 50 numbers.
Seems this method is left over from a non database method.
If you use Access to keep track of only the numbers used when they are used then you will have a better grasp of what is actually going on.

Otherwise you need to give everyone a P4 running XP Pro on a 100 base-T network


DougP, MCP