RE: How can I speed this up? - jbon...@sola.com.au - com.mysql.lists.win32

4 messages in com.mysql.lists.win32RE: How can I speed this up?

From	Sent On	Attachments
Alexander Shaw	02 May 2002 14:53
jbon...@sola.com.au	05 May 2002 17:16
Alexander Shaw	06 May 2002 01:48
jbon...@sola.com.au	06 May 2002 17:39

Subject:	RE: How can I speed this up?
From:	jbon...@sola.com.au (jbon...@sola.com.au)
Date:	05/05/2002 05:16:33 PM
List:	com.mysql.lists.win32

Yes, I would expect what you have done to be hideously slow. If you want speed then bypass Access wherever possible using pass-thru queries. I don't exactly follow what you are doing but isn't "Set rstCurrentSimilars = Recordset" invalid? (You should have a "New" in there). I'm not sure which one is this temporary table but it looks like it is in the Access database. If you want speed, it must be in the MySQL database. For speed all data and data manipulation must happen in the MySQL database and Access just manages the GUI plus the ODBC calls. Access will make it work with attached tables but it may not do it very efficiently.

The approach I always use is to use ADO and set up a connection to MySQL then have a routine which fires SQL statements at MySQL directly, even avoiding the pass-thru query mechanism. For efficiency, cut out the middleman!

HTH

John Bonnett

-----Original Message----- From: Alexander Shaw [mailto:bree...@btinternet.com] Sent: Friday, 3 May 2002 7:24 AM To: MySQL Win 32 List Subject: How can I speed this up?

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