Recordset.idxField vs Recordset.Field

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?

10 thoughts on “Recordset.idxField vs Recordset.Field

  1. I have always used IdxField as the examples I started with used them. And it works so I stuck with it. I do agree if you change your fields in the select it is painful to go back and update the idxfield references. I will have to investigate in using field in future projects.

  2. Even faster than idxField is to cache the field object. Like this:
    dim FirstnameField as Databasefield = rec.Field(“Firstname”)
    while not rec.eof
    listbox1.addrow FirstnameField.stringField
    And that’s better than working with numbers for Bob. The actual difference between caching field and idxfield vs. field is how long your string comparison takes which may be related to the length of your column names.

  3. @Christian Schmitz
    But I think the take away from my experiment is that idxField isn’t ‘a lot’ faster as you suggested in the previous post. I think it has just as many drawbacks as advantages.

    I’ve heard others say the same thing about idxfield and have always suspected they weren’t entirely right. And now we now.

  4. Well, in the apps I tried it, it was faster and that is what I remember. Also sqlite may have been optimized or Real Studio improved string comparison. Whatever, maybe the way with cached fields is the best. Speed and readability.

  5. I prefer Recordset.Field from the simple point of code maintainance – it’s a lot easier to read. On the same basis as using constants for the idx numbers, it also occurs that using constants for the field names could avoid recordset errors due to typos.

    To date I’ve only really used idxField for generic routines where the name of the field isn’t known in advance, and the speed difference you are showing wouldn’t tempt me to change.

    @Christian, good tip about the field object, though more useful for a small number of fields being used repeatedly e.g. to fill a listbox per your example that to use a lot of fields on a small number of records e.g. to fill an object with data.

  6. I don’t use .IdxField for the reason Bob exposes but .IdxField is clearly mandatory when you write some generic code that deals with databases. I haven’t felt that big difference in term of performance, and using .IdxField everywhere adds some pain when you come to a tricky debugging session. I vote for using .Field!

  7. @S-Copinger
    In either case, the IDE does NOTHING to help you eliminate errors like that. It’s one of the reasons why we use ActiveRecord on a lot of projects because once it’s all set up it’s uses dot notation and autocomplete works for Table and field names. So absolutely no issues with typos or using the wrong data type.

  8. Thanks for the speed testing! I flip back-and-forth depending on my mood – having started with .idxfield from the early examples. Now I have a reason for choosing one or the other.

  9. I never have a need for speed in database client applications, the number of transactions per second is usually less than 1 🙂 so I use Field rather than IdxField unless there’s an absolute reason not to (e.g. a generic support function where the column names my not be known in advance). Incidentally I did some work on an OPC project where the developer had used nested loops to cycle through two large recordsets, perform comparisons then update one of the recordsets… speed could have been an issue here … except that the whole thing could easily have been accomplished with a single sql statement executed on the server …

Comments are closed.