Improving Database Speed

[pot2000]

database very slow when searching. Is there a way to speed it up

I have a database which tracks samples for storage one can delte and ad samples. When adding samples the database searches the first open spot to place a sample. But when I add samples it takes a lomg time before the search is completed.

This is how I programed the search.

Private Sub Form_AfterUpdate()
Dim rsSamples As DAO.Recordset
Dim rsLocaties As DAO.Recordset
Dim intTeller As Integer
Dim intGeplaatst As Integer
Dim strVoorwaarde As String

If Not Me.NewRecord Then Exit Sub

Set rsLocaties = CurrentDb.OpenRecordset("tblVatposities", dbOpenDynaset)
Set rsSamples = CurrentDb.OpenRecordset("samples per locatie", dbOpenDynaset)

If Not IsNumeric(Me.aantal_samples) Or Not Me.aantal_samples > 0 Then
MsgBox "Ongeldige waarde ingebracht voor aantal samples"
Me.aantal_samples.SetFocus
Cancel = True
Exit Sub
End If

For intTeller = 1 To Me.aantal_samples
Do Until rsLocaties.EOF
strVoorwaarde = "Locatie = " & rsLocaties![VatPositieId]

rsSamples.FindFirst strVoorwaarde
If rsSamples.NoMatch Then
rsSamples.AddNew
rsSamples![Patientennummer] = Me.donornr
rsSamples![Materiaal] = Me.matriaal
rsSamples![Locatie] = rsLocaties![VatPositieId]
rsSamples![datum afname] = Me.datum_afname
rsSamples![datum invoer] = Me.datum_invoer
rsSamples.Update
intGeplaatst = intGeplaatst + 1
Exit Do
End If

rsLocaties.MoveNext
Loop
rsLocaties.MoveFirst
Next intTeller

MsgBox "Er werden " & Me.aantal_samples & " samples geplaatst"
End Sub

tblvatposities contains 20000 positions.

Has anyone a way to speed things up

 

[Pat Hartman]

I would not use this method at all. I would use an autonumber. I don't see the benefit of searching for an unused number. Since the assignment seems to be arbitrary what difference does it make if you use an autonumber?

 

[pot2000]

Autonumber will not work because I have 20000 positions that I use. When I use a sample it is removed from the database so that it can be used again.

 

[Pat Hartman]

Are the positions physical positions so that you are identifying a storage location for a sample?

When you delete a sample, you can place the id number in an available table. Then when you add a sample, you can choose the first id from the available table.

 

[The_Doc_Man]

I'm agreeing with Pat but I always take a different view on things than most people do.

If your "slot number" is important to you because of your business model, then this slot number should appear as a distinct entity in your database.

Suppose, only for the sake of example, that the slot number corresponds to a shelf in an automated tape library. If that is the case, then you have TWO entities that should appear in your DB. One is the shelf, which might be a short table. The other is the tape that can occupy a shelf. You would, of course, have sequence numbers on both objects, but the one on the shelf would NOT be an autonumber. The one on the tape could be. Each record would point to its partner when a tape is on a shelf. If a tape is removed from its shelf, then the links get zeroed out.

The reason I am taking this treatment is that if the shelf exists independently of the the object that occupies it, you can almost trivially write a query for unoccupied shelves.

Now, if this concept makes sense to you and seems to apply, then the reason you were having trouble in the first place is that your database was not properly normalized. Having to search a table for a buried nugget of information like this is such a common problem in unnormalized tables. Split the slot identifier to a table of its own and suddenly the search problem becomes a LOT simpler.