Hi,
I'm currently developing an application with an Access 2000 front end and
MySQL back end. I have coded in updates to a table (based on selections in a
list box) with a work around for the lack of support for sub selects using a
temporary table.
The problem is that already with quite small amounts of data in the tables
(already indexed) things are pretty sluggish and there is huge amounts of
reading and writing to the database.
Does anyone have suggestions of ways I could speed this up please? I have
included the code from the after update event of the list box in question.
Alex
Private Sub lstSimilarFrameNo_AfterUpdate()
Dim strSQL As String
Dim rstCurrentSimilars As DAO.Recordset
Set rstCurrentSimilars = Recordset
strSQL = "SELECT FilmID,FrameID FROM Frames WHERE FilmID = " &
cboSimilarFilmNumber & " "
Set rstCurrentSimilars = CurrentDb.OpenRecordset(strSQL)
Do
If rstCurrentSimilars.BOF = True Then Exit Do
CurrentDb.Execute "INSERT INTO CurrentSimilars (FrameID, FilmID,
SimilarID) VALUES (" & txtFrameID & ", " & rstCurrentSimilars!FilmID & ", "
& rstCurrentSimilars!FrameID & ")", dbFailOnError
rstCurrentSimilars.MoveNext
Loop Until rstCurrentSimilars.EOF = True
rstCurrentSimilars.Close
Set rstCurrentSimilars = Nothing
CurrentDb.Execute "DELETE SimilarsForFrames.* FROM SimilarsForFrames LEFT
JOIN CurrentSimilars ON
SimilarsForFrames.SimilarID=CurrentSimilars.SimilarID WHERE
SimilarsForFrames.FrameID=" & txtFrameID & " AND
SimilarsForFrames.SimilarID=CurrentSimilars.SimilarID", dbFailOnError
CurrentDb.Execute "DELETE CurrentSimilars.* FROM CurrentSimilars WHERE
(CurrentSimilars.FilmID=" & cboSimilarFilmNumber & ") AND
(CurrentSimilars.FrameID=" & txtFrameID & ")"
Me.Dirty = False
Dim i As Integer
For i = 0 To lstSimilarFrameNo.ListCount - 1
If lstSimilarFrameNo.Selected(i) = True Then
CurrentDb.Execute "INSERT INTO SimilarsForFrames
(SimilarID,FrameID) VALUES (" & lstSimilarFrameNo.ItemData(i) & "," &
Me!FrameID & ")"
End If
Next
End Sub
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.346 / Virus Database: 194 - Release Date: 10/04/2002
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
