What version of the ODBC Drivers are you using? I had a VB app using the 3.5x versions of the ODBC driver manipulating very small tables and it was absolutley painful to run.
I stepped back to the version 2.x drivers (2.9, I think - anyhow, the driver information is available on the web site) drivers and the thing screamed. So, before looking for SQL based solutions, try using the 2.x drivers first, if you can. Regards, Ken Hylton Programmer Analyst IV LEC Systems & Programming Billing Concepts, Inc. 7411 John Smith Drive San Antonio, Texas 78229-4898 (210) 949-7261 -----Original Message----- From: Alexander Shaw [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 2:28 PM To: MySQL List Subject: Any ideas how to 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 --- 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 --------------------------------------------------------------------- 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
