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:
[code]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[/code]
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.