MySQL Forums
Forum List  »  Newbie

Slow subsequent SELECTS within a DO WHILE NOT EOF loop
Posted by: Jim K
Date: May 24, 2023 09:36PM

Attempting to move
- from Classic ASP connecting to Microsoft Access
- to Classic ASP connecting to MySQL (8.0.33 InnoDB)

Have hit a performance issue if we do a database lookup while a previous lookup is still open.
I cant think of a way to word the issue in a google search or forum search to find anything relevant.


The easiest way to show the issue is:

Set RS1 = Conn.Execute("SELECT AField1 FROM ATable WHERE AField2 > 10 and AField2 < 20")
Set RS2 = Conn.Execute("SELECT BField1 FROM BTable WHERE BField2 = 99")
Set RS3 = Conn.Execute("SELECT CField1 FROM CTable")
RS1.Close
Set RS1 = Nothing
RS2.Close
Set RS2 = Nothing
RS3.Close
Set RS3 = Nothing

RS1 select is instant, RS2 & RS3 take around 0.016 seconds.
However, if we do:

Set RS1 = Conn.Execute("SELECT AField1 FROM ATable WHERE AField2 > 10 and AField2 < 20")
RS1.Close
Set RS1 = Nothing

Set RS2 = Conn.Execute("SELECT BField1 FROM BTable WHERE BField2 = 99")
RS2.Close
Set RS2 = Nothing

Set RS3 = Conn.Execute("SELECT CField1 FROM CTable")
RS3.Close
Set RS3 = Nothing

All 3 selects are instant.


This means any lookup within a loop take fractions of a second compared to being instant (like in Access, or if test running the individual SQLs commands on their own).
This time difference meant a report that takes 40minutes to run in Access took over 12hours in MySQL.
So, in the following loop example all RS2/RS3 lookups quickly add up the execution time.


Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=localhost;DATABASE=xxxxx;USER=yyyyy;PASSWORD=zzzzz;FOUND_ROWS=1;"
Set RS1 = Conn.Execute("SELECT AField1, AField2, AField3 FROM ATable WHERE AField4 > 99 and AField4 < 1001")
Do While Not RS1.EOF
Set RS2 = Conn.Execute("Select BField1 From BTable WHERE BField2 = " & RS1("AField2"))
if not RS2.EOF then
Set RS3 = Conn.Execute("Select CField1 From CTable WHERE CField2 = 'something'")
RS3.Close
Set RS3 = Nothing
end if
RS2.Close
Set RS2 = Nothing
RS1.MoveNext
Loop
RS1.Close
Set RS1 = Nothing


I have set up ConnectionPooling on the MySQL ODBC, but this made no difference (I suspect because we are really only making a single connection with the Conn.Open).
Note: The above SQL selects are just irrelevant examples I've typed to demonstrate the issue, however the connection method is what we are using.

Any ideas or suggestions of things to look at, or of how I should word a google search would be appreciated.

Thanks for your time.

Options: ReplyQuote


Subject
Written By
Posted
Slow subsequent SELECTS within a DO WHILE NOT EOF loop
May 24, 2023 09:36PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.