Why Is This Statement SOOOO SSSLLLOOOWWW???

  1. ‹ Older
  2. 3 weeks ago

    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.

  3. 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
  4. 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.

  5. 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
  6. Don L

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

    I definitely need to take Kem Tekinay's advice and see what light the Debugger might shine on the execution of the Listbox.AddRow statement. But first, I need to educate myself better on using the Debugger, so here goes a bunch more reading tonight. ;_;

  7. Maximilian T

    Mar 7 Pre-Release Testers, Xojo Pro Europe, Germany, Berlin

    Also, try profiling your code to make sure you spent the time where you think you do.

  8. Jeff T

    Mar 7 Midlands of England, Europe

    Morning all.

    I see that SQLLite uses Char() not Chr()
    And it uses the Oracle style || instead of + to do concatenation

    So the select statement would be more like this:

    SELECT Quotes.Proposal || char(9) || Lineups.Lineup || char(9) || .... etc

  9. Jeff T

    Mar 7 Midlands of England, Europe

    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.

    That would suggest that the recordset is somehow running SQL queries every time you ask for the value of an indexed field.
    Which makes no sense.

    But another thing you can try is to create a temp table, and query that instead.

    So use your query to INSERT into a table defined with these fields (after emptying it)
    Then select from the table using a query that doesnt need joins

  10. 2 weeks ago

    Ulrich B

    Mar 8 Pre-Release Testers, Xojo Pro Europe (Germany, Berlin) · xo...
    Edited 2 weeks ago

    @Don L Again, if I comment out the Listbox.AddRow statement (near the bottom), execution is instantaneous.

    If that is so, your select statement cannot be the cause. If changing the code to use the field names like in your other listboxes did not change anything, the reason stays mysterious.
    Do you have customized cell paint event handlers in this listbox that others don’t have?

    To avoid long datasource fill times of db-connected list boxes, I usually do an empty addrow for each record and attach the data to the row- or celltag. The output is then done in the cellTextPaint event handler.

  11. Don L

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

    @Jeff T I see that SQLLite uses Char() not Chr()

    Morning back atcha, Jeff! The database I'm working with here is MSSQL Server 2014 ... would that be consistent with SQLLite in use of Char() and the Oracle style concatenation?

    Like I mentioned somewhere above in this thread ... the thing that blows my mind is I can replace the current AddRow code line with

    AddRow("1", "2", "3", ... )

    ... or with just about any other string expressions and the method runs instantaneously as well as it does when commenting out the line. It's just when I try to use the returned SELECT recordset that I get the lengthy delay.

    @MaximilianTyrtania Also, try profiling your code to make sure you spent the time where you think you do.

    Thanks for your response, Maximillian! I'm going to look at that when I get into using the Debugger this morning. There's got to be a logical reason why this is happening.

  12. Don L

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

    @Ulrich B If that is so, your select statement cannot be the cause. If changing the code to use the field names like in your other listboxes did not change anything, the reason stays mysterious.

    I'm beginning to wonder if it's a "recordset thing" since I can replace the contents of the AddRow code line with any other String expressions except the recordset results and it works flawlessly!

    @Ulrich B Do you have customized cell paint event handlers in this listbox that others don’t have?

    Nope ... the listbox involved uses 5 events, none of which are involved in cell painting:

    • CellClick
    • CellKeyDown
    • CellLostFocus
    • Change
    • Open
  13. James D

    Mar 8 Pre-Release Testers Europe (Switzerland)
    Edited 2 weeks ago

    @Don L I'm beginning to wonder if it's a "recordset thing" since I can replace the contents of the AddRow code line with any other String expressions except the recordset results and it works flawlessly!

    Stop guessing and put some time into learn how to use the SQL Server tools. Thus you will be able to answer questions like:

    • When is the connection to the database made?
    • Is the connection begin dropped correctly?
    • Are there dead connections hanging around
    • Is the connection being dropped during the execution of your query and a reconnection performed.
    • What is the actual query or even queries sent to the database
    • When is the query sent to the database
    • When is the query actually executed
    • How does the optimiser actual set the query up to be executed
    • What is the load on the database when your query is being executed

    and so on.

    Don't assume for an instance that the third party code between your app and the database is just pass through, they almost certainly are not. MS put a lot of caching stuff in their libraries, Oracle puts a lot of their internationalisation support in theirs and so on.

    To identify where the problem is you need to be able to seen the execution end to end and right now you are lacking that ability.

  14. Don L

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

    @James D Stop guessing and put some time into learn how to use the SQL Server tools.

    You're right, James ... I know. I'm madly trying to educate myself here this morning on several fronts. Although I've used Xojo for some years now and developed a few rather sophisticated programs for clients of mine, I've effectively stayed away from using the Debugger except for the most trivial of needs. Most debugging efforts were solved using MsgBox to look at variables at various points or just plain empirically finding the issue by commenting/replacing stuff until I found the culprit. I'm afraid that has finally caught up with me here on this one.

    The most sophisticated tool I currently have for working with MSSQL is SQL Server Management Studio 2014. Is that a good place for me to start, in your estimation, to accomplish the list of diagnostic items you made in your response? Or is there something else I should get to do the task. Wow! This is going to be a long day of "larning" (as we say here in South Carolina ... you can "learn" something or you can "larn" it ... which means you retain what you take in).

  15. Jeff T

    Mar 8 Midlands of England, Europe

    CHAR() works on SQL server
    Concatenation is back to + rather than ||

    SELECT Quotes.Proposal + char(9) + Lineups.Lineup+ char(9) .... etc

    The most sophisticated tool I currently have for working with MSSQL is SQL Server Management Studio 2014. Is that a good place for me to start,

    You need to know your SQL will work before you try using it in a statement in Xojo.
    Learning SQL is one task.
    Learning Xojo is another.

    So yes, trial the SQL statements in SQL Server Studio.
    When you know they work, run them using Xojo

    I know your original query worked, and arguably there should be no need for this concatenation thing, Im just trying to hand you (what I had hoped would be ) a quick win. :)

  16. Andy M

    Mar 8 Pre-Release Testers, Xojo Pro Europe (UK, Kent)

    Always remember that if you are concatinating strings together in MSSql then always use ISNULL or COALESCE around the fields in the select otherwise if any of the fields are null the string returned will be NULL. e.g. ISULL(field, '') or COALESCE(fied1, '') will convert the null into an empty string.

  17. Edited 2 weeks ago

    It's a strange problem indeed :-(

    If you're using a recent version of MS Sql Server I believe you can request a recordset as a JSON object by adding:

    FOR JSON AUTO, Include_Null_Values

    to the end of your Select statement. Then you get a result in the format:
    [
    {"fieldName1": "value1", "fieldName2": "value2"},
    etc...
    ]
    That way you'd only have to read one value from your RecordSet, parse it to a JSON object and then populate the listbox by looping over the JSON array.

    Edit:
    There's a tutorial here

  18. James D

    Mar 8 Pre-Release Testers Europe (Switzerland)

    @Don L The most sophisticated tool I currently have for working with MSSQL is SQL Server Management Studio 2014. Is that a good place for me to start, in your estimation, to accomplish the list of diagnostic items you made in your response?.

    Yes for a developer it has all you need. Indeed it is usually enough for most DBAs as well.

  19. Jay M

    Mar 8 Pre-Release Testers, Xojo Pro NC, USA
    Edited 2 weeks ago

    Since most of your data comes from the Lineups table, what about just trying a query that only involves the Lineups table - no Quotes table or join. That way you could eliminate the Join as the culprit as you keep hinting at. This might prevent you from spending a lot of time trying to fix a problem that doesn't exist.

  20. Tim H

    Mar 8 Pre-Release Testers Portland, OR USA

    Another thing to try is to eliminate the AddRow and just pull the values into local string variables. That way you're separating the recordset from the listbox. The question being addressed is, "Is the slowdown in rsL.Field() or in lbxLineups.AddRow()?"

  21. Don L

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

    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!

or Sign Up to reply!