Talking about speed (Listbox, another conversation).
I usually start to write pig code in Methods to read RecordSet, then when something is working, I begin to make best, then better code.
In SQLite, when it is time to get RecordSet contents, I wrote one line for each Field.
In my last project I do the same, until I wanted to speed it up a bit.
So, I replaced the piggies (fake code):
Source_RS.Field("Field_Name_1").StringValue
Source_RS.Field("Field_Name_2").StringValue
Source_RS.Field("Field_Name_3").StringValue
// etc.
using a For Next loop.
Then (for once, since the export time including file creation and file data write is less than one second), I added a For Next loop. And I wanted to check the process time (for 2,000 Rows, 11 Fields).
The result for so few rows/fields was insignificant (IMHO).
The time wasted for the code change was also insignificant.
The new code looks nicer. (but a bit darker: IdxField instead of Field)
I prefer the use of a For Next loop (and I will try to skip the pigstep for the next time.
Properly designing the database in the first place is essential in my opinion. Following normalization rules does help creating efficient databases. When the data is efficiently designed, we have less update or deletion anomalies to contend with. Also, to most of us not expert DBA’s, normalization will help designing more efficient databases, although normalization is not per se a performance enhancement tool. Using properly designed indices on very large, normalized, tables is a good idea. Performance is not just dependent on tweaking Xojo code. It is first and foremost data architecture and application architecture and secondly, tweaking code.
There is discussion on whether normalization rules help or hamper performance (Here is one example). Here is an interesting discussion on de-normalization. I like to think that normalization will generally help. In some cases, breaking the rules may be preferrable. Before doing so, immediate gains must be weighed against maintainability, expandability, etc. of the applicaiton and database.
That said, I like to work with unbound fields. This means that I will do one select statement to read or write from/to the database, only the records and specific fields that I require. I read with one select statement the appropriate records, then use a loop to populate controls with the data. I am in a habit to use do-loops more than for-next loops. It is an old habit carried over from VB6 days. I don’t know whether do-loops are as much faster in Xojo as they were in VB6 however.
I tend to use Do Until rs.EOF rather than While Not rs.EOF because I was taught to do the positive first. It is all just Symantecs, but Do Until seems more like I want to do something until it’s done rather than I’m only doing this until it’s done. I never use a for/next loop with a record count because some (most) db plugins don’t support a record count.
@Wayne Golding:
With Do… Loop Until, you must execute a test before Do or the code between Do and Loop lines will be executed once, even if no record were selected.
jjc_Mtl
[quote=328986:@Jean-Jacques Chailloux]@Wayne Golding:
With Do… Loop Until, you must execute a test before Do or the code between Do and Loop lines will be executed once, even if no record were selected.
jjc_Mtl[/quote]
which is why I use WHILE/WEND in this circumstance
as it works if there is no records returned… while DO/UNTIL “assumes” at least one record
[quote=328992:@Dave S]which is why I use WHILE/WEND in this circumstance
as it works if there is no records returned… while DO/UNTIL “assumes” at least one record[/quote]
Not always true
DO…LOOP has several forms
checks at the top
Do Until condition
statements
[Continue]
[Exit]
statements
Loop
checks at the bottom
Do
statements
[Continue]
[Exit]
statements
Loop Until condition
checks at the top & bottom (yes that is legal syntax)
Do Until condition
statements
[Continue]
[Exit]
statements
Loop Until condition
I’m curious to know the difference between the 2 object codes generated by the compiler
for the Do Until condition…Loop AND While condition…Wend
Should be the same
May be the parser branches to the same line
jjc_Mtl
You can test at the beginning of the loop or at the end of the loop. Depends on circumstances, as a general rule though testing at the beginning is the way I use it.
Testing at the end will require a single run through the loop, not good if the recordset is empty already.
Um, no an empty recordset is not an error, it is to be tested for. An error implies an invalid sql statement not a valid statement that returns no records.
[code] SQLite_RS = SQLite_DB.SQLSelect(“SELECT * FROM Caritas ORDER BY Nom COLLATE NOCASE”)
If SQLite_DB.Error Then
MsgBox(“Reading Caritas TABLE” + EndOfLine + EndOfLine +_
"Error " + Str(SQLite_DB.ErrorCode) + ": " + SQLite_DB.ErrorMessage)
Return False
End If
If SQLite_RS = Nil Then
MsgBox(“Error Reading the Caritas TABLE” + EndOfLine + EndOfLine +_
“Sorry: the Caritas TABLE is empty.”)
Return False
End If
Do
// Code removed
Loop Until SQLite_RS.EOF[/code]
As far as I understand my (own) code, if there is an error or the RecordSet is empty (Nil), the code exits before the loop.
[code]If SQLite_RS = Nil Then
MsgBox(“Error Reading the Caritas TABLE” + EndOfLine + EndOfLine +_
“Sorry: the Caritas TABLE is empty.”)
Return False
End If
[/code]
will never be run. If the program did not fail on the previous step then the RS object will NOT be nil, so the above statement will not execute.
If the RS is not nil it will fail in your:
Do
// Code removed
Loop Until SQLite_RS.EOF
which will fail if there are no records in the RS (that is it is an Empty table).
Your code:
[code]If SQLite_RS = Nil Then
MsgBox(“Error Reading the Caritas TABLE” + EndOfLine + EndOfLine +_
“Sorry: the Caritas TABLE is empty.”)
Return False
End If
[/code]
should actually be:
[code]If SQLite_RS.EOF Then
MsgBox(“Sorry: the Caritas TABLE is empty.”)
No, but it may be because I need to eat (I am 2 hours late on that).
My understanding was:
I always read the RS from the first Record (of the RecordSet)
RS is Nil when in fact it is empty
DB.Error appears when there is an error (the error is in CodeError / CodeMessage)
When the first line in the loop block is executed, EOF and DB.Error cannot happens.
I will read your answer after the lunch (and try to avoid the afternoon nap).