SQL Server Performance

Question about one of the tips...

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by bruce_ray, Oct 22, 2004.

  1. bruce_ray New Member

    The following is from the "tips" page:

    quote:When looping through recordsets, be sure you bind columns to field objects before the looping begins. Don't use the Fields collection of the Recordset object to assign values for fields in a Recordset within each loop, as it incurs much more overhead. [6.5, 7.0, 2000] Updated 7-15-2004

    I'm not sure I follow...can someone give me a simple example (VB is fine) of "binding columns to field objects" before the loop?

    Thanks!

    (moved from Performance Tuning for DBAs section)
  2. satya Moderator

    For the sake of application performance that is the good approach it was defined.

    If you want to traverse the recordset then you should obtain references to the field objects at the beginning and then simply use those references when looping through the records.

    If you don't take the reference and use fields then it incurs the overhead of looking up the fields in the Recordset::Fields collection for each record in the recordset. For a large recordset, this task can get quite expensive and incurs timeout against database server.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. bruce_ray New Member

    OK - I understand. Thanks.
    (Sorry for posting to the wrong forum, I couldn't decide which was which!)

Share This Page