I have a SQL SELECT statement that loads a recordset (rsL) with data from an MSSQL Server database using a LEFT JOIN on two different tables.
Dim rsL As RecordSet = db.SQLSelect("SELECT Quotes.Proposal, Lineups.Lineup, Lineups.Department, Lineups.Description, Lineups.CaseNum, Lineups.CaseType, Lineups.CaseModel, Lineups.FrontType, Lineups.BaseFrame, Lineups.SpecialInstructions, Lineups.DeliveryDate, Lineups.pKey FROM Lineups LEFT JOIN Quotes ON Lineups.ProposalID = Quotes.pKey WHERE (ProposalID='" + CurrentQuoteID + "') ORDER BY Proposal, Lineup ASC") // list of existing lineups for the current proposal
Sans the subsequent error checking statements, I then put the data into a listbox (lbxLineups) as follows:
While NOT rsL.EOF lbxLineups.AddRow(rsL.IdxField(1).StringValue, rsL.IdxField(2).StringValue, rsL.IdxField(3).StringValue, rsL.IdxField(4).StringValue, rsL.IdxField(5).StringValue, rsL.IdxField(6).StringValue, rsL.IdxField(7).StringValue, rsL.IdxField(8).StringValue, rsL.IdxField(9).StringValue, rsL.IdxField(10).StringValue, rsL.IdxField(11).StringValue) rsL.MoveNext Wend rsL.Close // close the record set
There are only 4 items (records) returned from the SELECT statement in this scenario so the WHILE/WEND only iterates through the loop 4 times. Yet it takes 30 - 40 seconds to execute!!! If I simply replace the lbxLineups.AddRow statement with the following:
lbxLineups.AddRow("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11")
... it executes IMMEDIATELY!
Now, I realize the obvious difference is that the 1st case uses values from a recordset while the 2nd case uses string constants instead ... but I don't think that explains the vast difference in execution time. So, my two questions are:
- Why is this happening?
- How do I get around it?
A last note of interest ... I have this very same code elsewhere in the program that DOES NOT exhibit this issue, BUT in those instances the recordset values are expressed in terms of Field Names ... e.g., rsL.Field("LUnum").StringValue, etc. as opposed to rsL.IdxField(1).StringValue, etc.