Why Is This Statement SOOOO SSSLLLOOOWWW???

  1. 3 weeks ago

    Don L

    Mar 7 Pre-Release Testers, Xojo Pro Florence, South Carolina USA
    Edited 3 weeks ago

    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:

    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.

    Well, after two days of bludgeoning myself to near death with education (Debugger, SQL Diagnostics, etc. etc. etc.), it has finally all paid off. As it turns out, in the end, both "IdxField" and "Listbox.AddRow" were the proverbial "red herrings". Although playing around with both by either commenting each out or replacing the returned SQL SELECT results with fixed string constants "appeared to make the problem go away", it was an illusion because it only "masked" the real problem.

    When I finally figured out how to use the Debugger intelligently (well, somewhat, at least), I methodically checked the execution of the code and after some long hours spent stepping my way through hundreds of lines of code, I found it! The whole issue was caused by the following line of code:

    LineupSetup(oContainers(kdx)).lbxLineups.ListIndex = LineupSetup(oContainers(kdx)).lbxLineups.LastIndex  // Position pointer by selecting newly added lineup (last row added to the Lineups listbox)

    The execution of that line caused the CHANGE event of the listbox "lbxLineups" to fire. The code in that CHANGE event is hundreds of lines long as well. Some ways down the chain, there was a line of code that ended up firing the CHANGE event of yet another listbox "lbxCases" ... which in turn made the program erroneously attempt to "save" the entire contents ... amounting to a loop doing approximately 3000 INSERT actions to the external database with AutoCommit turned ON ... thus resulting in the extra time.

    I have redesigned the algorithm to prevent that sequence of events from occurring mistakenly.

    I want to sincerely thank ALL OF YOU who took your valuable time to assist me with your ideas and suggestions ... I think I learned something from every one of you (and I DO mean EVERY last one of you, believe me!) that I can carry forward and use in the future. Most of all, you "encouraged" me into stepping back for a moment from the race to finish my program and instead invest some quality time "furthering my education" ... which can only pay dividends going forward!

  2. Louis D

    Mar 7 Pre-Release Testers, Xojo Pro Montreal, QC, Canada

    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.

  3. Don L

    Mar 7 Pre-Release Testers, Xojo Pro Florence, South Carolina USA
    Edited 3 weeks ago

    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.

  4. Valdemar D

    Mar 7 Pre-Release Testers, Xojo Pro Europe ( France, Paris )

    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

  5. Don L

    Mar 7 Pre-Release Testers, Xojo Pro Florence, South Carolina USA

    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.

  6. Don L

    Mar 7 Pre-Release Testers, Xojo Pro Florence, South Carolina USA

    @Don L 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

  7. Kem T

    Mar 7 Pre-Release Testers, Xojo Pro, XDC Speakers New York

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

  8. Don L

    Mar 7 Pre-Release Testers, Xojo Pro Florence, South Carolina USA

    @Kem T Have you tried tracing through the code

    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.

  9. Valdemar D

    Mar 7 Pre-Release Testers, Xojo Pro Europe ( France, Paris )

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

  10. Don L

    Mar 7 Pre-Release Testers, Xojo Pro Florence, South Carolina USA

    @Valdemar Dnbsp;SOUSA Have you take a look to the kind of fields you use

    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

  11. Louis D

    Mar 7 Pre-Release Testers, Xojo Pro Montreal, QC, Canada

    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.

  12. Jeff T

    Mar 7 Midlands of England, Europe

    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)
  13. Don L

    Mar 7 Pre-Release Testers, Xojo Pro Florence, South Carolina USA

    @Jeff T 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...

    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.

  14. Don L

    Mar 7 Pre-Release Testers, Xojo Pro Florence, South Carolina USA

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

    I'll give that a go this evening, Louis ... thanks again!

  15. Tim P

    Mar 7 Pre-Release Testers feedback://46303

    @Don L Trying to figure out right now just why it's coming back NIL.

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

  16. Ulrich B

    Mar 7 Pre-Release Testers, Xojo Pro Europe (Germany, Berlin) · xo...

    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.

  17. Don L

    Mar 7 Pre-Release Testers, Xojo Pro Florence, South Carolina USA

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

    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.

  18. Don L

    Mar 7 Pre-Release Testers, Xojo Pro Florence, South Carolina USA

    @Ulrich B What happens if you address the fields of your recordset the usual way, via their name?

    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
  19. Tim H

    Mar 7 Pre-Release Testers Portland, OR USA
    Edited 3 weeks ago

    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.

  20. Don L

    Mar 7 Pre-Release Testers, Xojo Pro Florence, South Carolina USA

    @Tim H 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.

    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.

        // 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
  21. Newer ›

or Sign Up to reply!