Why Is This Statement SOOOO SSSLLLOOOWWW???

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:

  1. Why is this happening?
  2. 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.

Are the fields in your join either part of the keys of the tables, or indexed fields? Even if the number of returned records is small, joining on non-indexed fields often results in a sequential read of the whole table. On a large table, that will cause the query to be very slow.

Thanks for your response, Louis. Both tables are set up indexed where “pKey” is the indexed primary key. So, I guess the answer to your questions would be “Yes” (it is indexed) and “Yes” (LEFT JOIN Quotes ON Lineups.ProposalID = Quotes.pKey) means the primary key of one table is involved in the JOIN.

HOWEVER, there are only 4 records in the one table (“Lineups”) and 1 record in the other (“Quotes”), so even recursive “complete reads” should not take any measurable amount of time. Also, it’s not the SELECT statement that’s taking the time … that same SELECT statement is in the code when I replace the lbxLineups.AddRow with string constants (instead of the recordset IdxFields) and it executes immediately.

I will amend my statement about the “exact same code but using Field Names instead of IdxFields not having the issue elsewhere” by saying that those “other” places DO NOT use a JOIN in the SELECT statement.

Have you try to load the fields, put then into variables and then add them to the listbox.addrow :

While NOT rsL.EOF
dim v1, v2, v3 as String
v1 = rsL.IdxField(1).StringValue
v2 = rsL.IdxField(2).StringValue
v3 = rsL.IdxField(3).StringValue
lbxLineups.AddRow(v1, v2, V3)

rsL.MoveNext

Wend

Thanks for your response, Valdemar … I did try something real similar by creating a string array with the IdxField values and then using lbxLineups.AddRow(stringArray) … but the 30 - 40 second execution time remained the same. I’ll go back and break the array out into individual variables and give it a go just to make sure I don’t leave any stone un-turned.

Just tried that … still took approx. 40 seconds

But once again, if I take all the

vx = rsL.IdxField(x).StringValue … where “x” is 1, 2, 3 …
and replace them with

vx = "x"

… it executes instantaneously!

It sure appears to have something to do with using IdxField

Have you tried tracing through the code to make sure it’s not doing something you didn’t expect?

Thanks, Kem! Not yet … more so because I’m not very good at doing that and was saving it as a last resort. Looks like I’ll have to do some reading to brush up on the Debugger usage. I was hoping it was something that I was staring in the face … but there isn’t much there (code-wise) that could go awry.

Have you take a look to the kind of fields you use (string, date, bool). because you only use string in you add row.

If I understand your question correctly, Valdemar, all of the fields involved in the transaction are “String” and should be “String”. There are no other (Boolean, Date, etc.) data types involved in the affected method.

Thanks again for trying to come up with possible ideas! I’ve got a headache now from staring at this thing all afternoon but must figure it out … my customer will NEVER accept the kind of “wait times” this is causing. O_o

What if you create a view in SQL server corresponding to your query, and select * from the view where ProposalID= CurrentQuoteID?

If somehow the connector does not like something in your sql statement, this should work around the issue.

Im pretty sure (untested recently) that you can hit all columns of a listbox at once by doing Addrow with a tab-delimited string.
If that is the case, you can make the query do the work

SELECT Quotes.Proposal + chr(9) + Lineups.Lineup + chr(9) +  Lineups.Department + chr(9) +  Lineups.Description + chr(9) + Lineups.CaseNum + chr(9) +  Lineups.CaseType + chr(9) +  Lineups.CaseModel + chr(9) + Lineups.FrontType + chr(9) +  Lineups.BaseFrame + chr(9) +  Lineups.SpecialInstructions + chr(9) +  Lineups.DeliveryDate + chr(9) +  Lineups.pKey   from...

That gets you one string field per recordset row

Then you just

lbxLineups.AddRow  rsL.IdxField(1)

Thanks a bunch for jumping in, Jeff. I got all excited when I saw what you were doing, but unfortunately, the SELECT statement returns a NIL recordset. Trying to figure out right now just why it’s coming back NIL.

I’ll give that a go this evening, Louis … thanks again!

SQLSelect will return a nil recordset if the SQL query is bad. Check for Database.Error when that happens.

What happens if you address the fields of your recordset the usual way, via their name?
I wonder if idxfield could be a very time-consuming operation. Theoretically, if Xojo does not cache the field names, this could be an explanation as each call to Idxfield could trigger an examination of the table structure.

If using the names should increase the performance noticeably, you might want to use a custom caching mechanism for the field names if your setup requires such an approach.

Yep … Jeff’s suggested SELECT statement throws the following error:

‘chr’ is not a recognized built-in function name.

But he did say “Buyer Beware” (e.g., “Untested”) … and I’m grateful he threw a suggestion out there to begin with. I do like his idea of trying to get SQL to do the work for me. Now, all I have to do is figure out just how I can make that happen.

How do I do that, Ulrich? I know how to get the Field (column) Name from an IdxField like so:

rsL.IdxField(1).Name

But since I’m using the LEFT JOIN, I thought I had to use IdxField to access the results. Could you give me a hint on how to use the “names” with the SELECT statement I’m using:

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 Quotes.Proposal, Lineups.Lineup ASC")   // list of existing lineups for the current proposal

You can examine the name of each field via rsL.IdxField(n).Name, but they should be coming back as Proposal, Lineup, Department, Description, CaseNum, CaseType, CaseModel, FrontType, BaseFrame, SpecialInstructions, DeleveryDate, and pKey. Joins do not affect the ability to use the column name to access the field, unless you have columns with the same name in each table. Then you can either use IdxField or specify an alias for each field in your select statement.

At various times over the years, IdxField and Field have varied in terms of performance, sometimes one being slower and sometimes the other. But never by that much. I’ve rewritten my database access framework several times over the years to get the best possible performance.

Thanks so much, Tim! That’s the piece I didn’t know. I just went ahead and changed my SELECT statement to use the field names, and alas, you’re 100% right … it had negligible improvement on the performance.

The showstopper is still the Listbox.AddRow command. When I comment it out, the method runs instantaneously. Looking at the other spots in the program (completely different methods) that exhibit this same “turtle pace performance”, they all have only two things in common; 1) the source of the data feeding the AddRow to the listbox was coming from a SELECT statement using a LEFT JOIN, and 2) The associated recordset used IdxField. I think it’s safe to say I’ve ruled out the IdxField as the cause and now am focusing on the “LEFT JOIN”. There are a dozen or more places in the program that use essentially the exact same code except the SELECT statement used to fetch the needed data has NO Joins … and those place ALL have NO problem.

More specifically, here’s the method that takes 30 - 40 seconds. Again, if I comment out the Listbox.AddRow statement (near the bottom), execution is instantaneous.

[code] // get Lineup data
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 Quotes.Proposal, Lineups.Lineup ASC”) // list of existing lineups for the current proposal

if db.Error then
  // open Modal MsgBox window
  taMessage = "SELECT DB Error: " + db.ErrorMessage
  wndModalMsgBox.ShowModal
end if


for kdx As Integer = 0 to Ubound(oContainerIndex)
  
  if oContainerIndex(kdx) = "LUsetup" then  // disable the Add Lineup button
    
    LineupSetup(oContainers(kdx)).pbtAddLineup.Enabled = false
    
  end if
  
next


If rsL <> Nil Then
  
  // delete all current lineup content in Lineups container
  for kdx As Integer = 0 to Ubound(oContainerIndex)
    
    if oContainerIndex(kdx) = "LUsetup" then
      
      LineupSetup(oContainers(kdx)).lbxLineups.DeleteAllRows
      
    end if
    
  next
  
  // Place lineup data in listbox rows
  for kdx As Integer = 0 to Ubound(oContainerIndex)
    
    if oContainerIndex(kdx) = "LUsetup" then
      
      While NOT rsL.EOF
        
        LineupSetup(oContainers(kdx)).lbxLineups.AddRow(rsL.Field("Proposal").StringValue, rsL.Field("Lineup").StringValue, rsL.Field("Department").StringValue, _
        rsL.Field("Description").StringValue, rsL.Field("CaseNum").StringValue, rsL.Field("CaseType").StringValue, rsL.Field("CaseModel").StringValue, _
        rsL.Field("FrontType").StringValue, rsL.Field("BaseFrame").StringValue, rsL.Field("SpecialInstructions").StringValue, rsL.Field("DeliveryDate").StringValue.Left(10))
        
        // capture current Case Type and LineupID of newly added Lineup
        CurrentLUCaseType = rsL.Field("CaseType").StringValue
        CurrentLineupID = rsL.Field("pKey").StringValue
        
        rsL.MoveNext
        
      Wend
      
      rsL.Close  // close the record set
      
    end if
    
  next
  
end If

end if[/code]