Speed up the reading time ?

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.

What is your opinion ?

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.

my opinon

WHILE NOT RS.EOF
....do stuff....
RS.MOVENEXT
WEND

as opposed to

FOR I = 1 to RS.RECORDCOUNT
....do stuff
RS.MOVENEXT
NEXT i

While vs For…
When I am there in writing code, I have the tendency to use a For Next loop, but fortunately I strongly repress it.

It may be a natural idea (to use For against While or other kinds of loops). We may compare that to a delete loop:

Coders may start to write the standard For Next loop until they realize their loop fail. Then, after some search, they use the reverse way: Down To…

Edit:

One thing I do not like is: While Not textInput.EOF (the NOT word there).

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:

I just used Do …/… Loop Until db.EOF:

I love it !

Thanks for the tip !

@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 :slight_smile:
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 :slight_smile:
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

  1. checks at the top
Do Until condition
  statements
  [Continue]
  [Exit]
  statements
Loop 
  1. checks at the bottom
Do 
  statements
  [Continue]
  [Exit]
  statements
Loop Until condition
  1. 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

Which is why I said Do Until rs.eof, so the test is at the beginning.

However I see Norman has corrected this misconception.

What if Until rs.EOF is at the End ?

If the RecordSet is empty, it does not enter the loop.

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.

If the RecordSet is empty, I will get an error at RS creation tim.

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.

OK Wayne, I restate the idea with code:

[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.

Am I right ?

Emile

Your code is flawed. The code you have:

[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.”)

Return False

End If
[/code]
Does that make it clearer?

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).

Thank you Simon.

[quote=329102:@Emile Schwarz]

Do // Code removed Loop Until SQLite_RS.EOF[/quote]

I use

Do Until SQLite_RS.EOF // Code removed Loop

So if the recordset is empty the loop is never run and no need to test beforehand.

oddly I almost always use WHILE

when I’m feeling particularly lazy and spinning out a tiny example to test something I’ll use something like

[code]while rs <>nil and rs.eof <> true // * and yes I know this has issues about operator_compare Kem :stuck_out_tongue:

wend[/code]

the equivalent is “do until” just reads awkward

Do Until (SQLite_RS is nil) or (SQLite_RS.EOF)
// Code removed
Loop