Results 1 to 3 of 3
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Better performance processing large rowsets? (ADO)

    Through a series of links and searches I came upon a tip for replacing this syntax:
    <UL>[code used in loop to write out data]
    rngBody.InsertAfter rst.Fields("MyField").Value[/list]with this:
    <UL>[one-time set-up]
    dim fldMyField as ADODB.Field
    Set fldMyField = rst.Fields("MyField")

    [code used in loop to write out data]
    rngBody.InsertAfter fldMyField[/list]According to ADO Performance Best Practices, "You'll incur some overhead in creating individual Field objects, but you have to weigh this one-time penalty against its benefit in dramatically improved performance."

    Well, this is wonderful, except that I found the performance differences not to be very dramatic in my test, using Word VBA as the host (hence the rngBody.InsertAfter method in the example above). For my application's typical recordset of 270 rows of 14 fields, the first method required 2.863 seconds to lay out a table, while the second method required 2.847 seconds. For a rather large recordset of 714 rows of 14 fields, the first method required 12.078 seconds to lay out a table, while the second method required 11.556 seconds. While it's nice to save half a second in such a case, that's only about 4%. I want more!

    Can anyone else vouch for this technique? Am I missing something? I have some ASP pages whose performance I'd love to dramatically improve, so it would be great to make this work. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Better performance processing large rowsets? (ADO)

    In my experience, the improvement is not "dramatic" either. But if you are processing hundreds of thousands of records, with lots of calculations involving field values, even shaving 4% off will be welcome.

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Better performance processing large rowsets? (

    Oh well, it could be a few years before I regain all the seconds I spent rewriting my code. I guess we'll call it a "learning experience." <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •