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. ;_;
Also, try profiling your code to make sure you spent the time where you think you do.
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
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
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 dont 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.
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.
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.
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!
Nope … the listbox involved uses 5 events, none of which are involved in cell painting:
- CellClick
- CellKeyDown
- CellLostFocus
- Change
- Open
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.
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).
CHAR() works on SQL server
Concatenation is back to + rather than ||
SELECT Quotes.Proposal + char(9) + Lineups.Lineup+ char(9) … etc
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. 
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.
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
[quote=427546:@Don Lyttle]
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?.[/quote]
Yes for a developer it has all you need. Indeed it is usually enough for most DBAs as well.
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.
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()?”
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!