BKeeney Briefs

Yesterday I was complaining about my OPC project and I’ve added a few more bullet points in the comments section.  This morning I added a comment about how the idiot developer used Recordset.idxField(index) rather than Recordset.Field(“FieldName”).  I hate it when developers use idxField because it makes it tough to read and I constantly have to go back and look at the query and count fields.  In the short term I modify the query string in the IDE and put index comments in the in string.  Something like this:

s = “Select field1, ” _ //1

+ “field2, ” _ //2

and so on but it’s still a pain.

Anyway, Christian S. posted a comment that got me thinking.  He said:

idxField is good. It can speedup applications a lot.

Is that really true?  I have never tested it so I created a little test application using an actual query from the project I’m working on.  I run the same query twice.  The first time I loop through the recordset using nothing but idxField and the second time I use nothing but field which uses the field name.  The results are interesting and confirmed my suspicions.

If I run my two functions just once and bring back only 1 record and do one read in the function the idx field is indeed faster by about 50%.  However my testing differences range from 6 to 300 microseconds faster and while I didn’t average out all the results, I’d say most times it was around 60 microseconds faster.  I ran this test a lot to see if I would see a wider variation but did not.  My 300 microseconds difference happened just once out of about 25 runs.

I changed my query so that instead of bringing back 1 record it brings back over 700 and I loop through the recordset and then do the same same test.  IdxField comes back about 15% faster.  When I loop through the same row 10,000 times there is no statistical difference which means that while Field is slower it’s a one time hit since my tests show that even doing the same pull 10,000 times the total difference between the two functions is still only about a 10-15% difference in overall speed.

My conclusion is that yes, idxField IS faster than using Field but to say it speeds up an application “a lot” is an exaggeration.  In 10 years of Real Studio consulting work I have rarely done an application that requires that much speed from the queries.  If you have any sort of graphical user interface I think it safe to say that updates to the controls on screen are significantly slower than pulling data out of the recordset.  That’s NOT to say that the query itself is fast because often times that is the most limiting factor.

I won’t discourage you from using idxField.  If you do, though, I really encourage you to use constants named after your fields.  So perhaps you create a constant named kField1 and the value is 1 for the first field in your recordset.  The only drawback to this approach is that you (or the developer after you) will change the SQL query and all the code using idxfield no longer works because your field order is different.

I much prefer the explicitness of using Field because I never have to worry if I change my field order.  It’s very readable.  It’s not perfect and there are plenty of reasons why it’s not the best (there’s a reason why we use ActiveRecord in a lot of projects).  Use what’s best for you and what’s best for the five years from now.

What say you, RB developers?

Join me at the Real Studio Database Days training in Frankfurt, Germany on November 3rd and 4th.  I am looking forward to talking to the group.  Speakers include:

• Stéphane Pinel from Real Software
• Geoff Perlman from Real Software (via video chat)
• Jens Boschulte from DynaForms GmbH
• Simon Larkin from QiSQL
• Thomas Tempelmann
• Christian Schmitz from Monkeybread Software

If you’d like to learn more about Real Studio and how it integrates with your favorite database there is an event coming up that might be of interest to you.  MonkeyBread Software is hosting the Real Studio Database Days, November 3, and 4, 2011 in Frankfurt, Germany.

Currently there are over 30 people registered from eight countries including Germany, Austria, Belgium, France, United Kingdom, Netherlands, and the USA.  This is a good time of the year to get away.

More info can be found at http://www.monkeybreadsoftware.de/realbasic/events/#database days

After checking flight costs there’s a possibility I’ll be there.  If I do, I’ll do a short presentation on our REALbasic implementation of ActiveRecord.  If you’ve not used it, ActiveRecord lets you use auto-complete of your tables/fields in the Real Studio IDE, takes advantage of the strong type-casting in Real Studio and, takes advantage of REALbasic events.

This one comes up on the RB Forums a lot.  The post generally goes something like this:  “I have a database and everything works fine until here <insert code snippet> and I get a Nil Object Exception because rs is nil.”

If everything else is working fine then it must be the recordset.  If you’re not checking the database for an error you’ll never know!  The db.error property tells you that you had an error and the db.errormessage property does a pretty good job of telling you what the error is.

dim rs as recordset
rs = db.SQLSelect(“SELECT * FROM sometable WHERE somefield=avalue”)
if db.error then
msgbox “Error!” + db.ErrorMessage
return
end

//do something with the recordset here.

I find this happens a lot with developers that came from Visual Basic 6 where database errors throw an exception.  REALbasic doesn’t work the same way.  Get over it and start checking for database errors.

If you want to capture database errors you can do something like what we’ve done:

if gDB.Error then Raise new BK_Debug.BK_DatabaseException(gDB.ErrorMessage, s)

Where BK_DatabaseException is a RuntimeException subclass defined like this:

Sub Constructor(sMessage as string, sSQL as string)
If sMessage <> “” Then
Me.Message = sMessage
End If

If sSQL <> “” Then
Me.Message = Me.Message + EndOfLine +  EndOfLine + sSQL
End
End Sub

This can be useful if you have automated error handling and can receive this information.  Of course, if you’re creating a runtime exception you had better be handling them in the app.unhandledexception event otherwise your app will shutdown and you’ll have pissed off users.