Results 1 to 13 of 13

Thread: Undocumented code for faster data retrieval?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2006
    Posts
    367

    Undocumented code for faster data retrieval?

    I don't know if this is TRUE about being faster, but I know it works.

    .Collect

    I was told it is an undocumented VB coding. Don't quote me on this. It does work (I know I use it), but I'm trying to find out if it being faster is true.

    Basically you use it like this...
    VB Code:
    1. Text1.text = rs.Collect("fieldname")
    2.  
    3. Text1.text = rs.fields("fieldname")
    4. Text1.text = rs![fieldname]

    The .Collect is supposed to be 30% faster from what I heard, but I can't verify that. I was wondering if anyone else could verify or bring new light to this for me?

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,882

    Re: Undocumented code for faster data retrieval?

    Yeah I can GOOGLE for that and find posts about it.

    MSDN has nothing on it.

    My bible - Wrox ADO 2.6 Programmers Reference - does not mention it (this must mean it's a sin )

    Use it at your own risk obviously, since it's undocumented nature means it can go away or have behavior that you do not always expect.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,500

    Re: Undocumented code for faster data retrieval?

    Well I never knew about that!

    I am not in a position to do speed testing right now, but I can tell you (from a quick "Add Watch") that Collect is the equivalent of Field(?).Value, eg:
    VB Code:
    1. Text1.text = rs.fields("fieldname")[u].value[/u]
    ..which will probably be a little bit quicker, as you have specified which property you want. It doesn't matter in most situations tho, as reading the field values is probably the fastest part of the process.


    I would recommend avoiding Collect, as not only is it likely to fail (there is a reason for things being "undocumented" - usually because they have bugs), but it will also confuse anybody who reads the code. Just use Fields.value instead.

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2006
    Posts
    367

    Re: Undocumented code for faster data retrieval?

    From the coding that is documented which is the fastest for data retrieval?

    Why Text1.text = rs.fields("fieldname").value

    Why not Text1.text = rs.fields("fieldname")

    So if we use .value it is faster than not using it?

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,882

    Re: Undocumented code for faster data retrieval?

    Specifying the .Value is considered good form by most coders - as "default" properties are vague and subject to change or confusion.

    If you are truly looking for speed - like you are looping through thousands of rows and thousands of columns then test the speed and use whatever is required to get a few milliseconds.

    Personally, we always use rs(0).value to get the first column and rs(1).value to get the second column. But that is because our client code does not know the names of the columns - they are not fixed. Some people are very much against using ordinal position instead of field name, but I would be willing to guess there is more time lost on "name resolution" then on any of the other fine points you are attempting to measure.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,500

    Re: Undocumented code for faster data retrieval?

    I fully agree with that (apart from specifying by position - it's harder to read than by name [which will be a bit slower!], but is apt in the circumstances).

    Another point to mention is that not using .Value will be 'significantly' slower if you are putting it into a variant variable - as it can contain a Field object (so all properties are copied, rather than just the value).


    Overall tho, the time to get the data out of a recordset is probably negligible compared to what you are doing with it - if there are speed issues, it probably wont be caused by the way you get the values from the recordset.

  7. #7
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    11,044

    Re: Undocumented code for faster data retrieval?

    Quote Originally Posted by szlamany
    Specifying the .Value is considered good form by most coders - as "default" properties are vague and subject to change or confusion.

    If you are truly looking for speed - like you are looping through thousands of rows and thousands of columns then test the speed and use whatever is required to get a few milliseconds.

    Personally, we always use rs(0).value to get the first column and rs(1).value to get the second column. But that is because our client code does not know the names of the columns - they are not fixed. Some people are very much against using ordinal position instead of field name, but I would be willing to guess there is more time lost on "name resolution" then on any of the other fine points you are attempting to measure.
    I also agree that using the ordinal position is faster since computers are faster in processing numeric values than strings... Or something like that...
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  8. #8
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: Undocumented code for faster data retrieval?

    Using Field objects is suppose to be the fastest method. However, it requires more code to write, so I for one rarely use this method. Besides with today's computers the time difference between all methods would be hardly noticeable in most cases.

    VB Code:
    1. Dim fldId As ADODB.Field
    2.     Dim fldName As ADODB.Field
    3.     Dim fldActive As ADODB.Field
    4.     Dim fldStart As ADODB.Field
    5.    
    6.     Dim lngId As Long
    7.     Dim strName As String
    8.     Dim blnActive As Boolean
    9.     Dim dteStart As Date
    10.  
    11.     sngStart = Timer
    12.  
    13.     With Data
    14.         Set fldId = .Fields("CustomerId")
    15.         Set fldName = .Fields("CustomerName")
    16.         Set fldStart = .Fields("EffectiveDate")
    17.         Set fldActive = .Fields("IsActive")
    18.        
    19.         Do Until .EOF
    20.             lngId = fldId.Value
    21.             strName = fldName.Value
    22.             blnActive = fldActive.Value
    23.             dteStart = fldStart.Value
    24.             .MoveNext
    25.         Loop
    26.     End With
    27.  
    28.     sngEnd = Timer

    Here are some basic timings I did on a disconnected recordset with 20,000 records.

    Total Time using Ordinals 0.406
    Total Time using Names 0.516
    Total Time using Collect(with ordinals) 0.359
    Total Time using Collect(with strings) 0.359
    Total Time using Field objects 0.328

  9. #9
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,882

    Re: Undocumented code for faster data retrieval?

    Quote Originally Posted by brucevde
    Total Time using Ordinals 0.406
    Total Time using Names 0.516
    Total Time using Collect(with ordinals) 0.359
    Total Time using Collect(with strings) 0.359
    Total Time using Field objects 0.328
    Great analysis - thanks brucevde...

    From what I read while googling .Collect grabs the data without making a FIELD reference. This must be some kind of memory trick - don't actually create an object reference simply go after the data the object reference pointer points to. That's kind of apparent from the timing difference between the top two (.4 and .5) and the Collect (.3).

    Obviously the .Field object timings are based on the "field object reference" being already setup. This is apparent by the fact that they are so much faster then the top two timings (oridinal and names).

    Bottom line - having the field reference already created or using the collect property (not needing a field reference) is very fast.

    Collect is still dangerous as undocumented features are apt to be removed - are probably only in place to support internal MS uses.

    The big difference is these timings is that the NAMES method is over 25% slower then the ORDINAL method!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,500

    Re: Undocumented code for faster data retrieval?

    Ah yes, I'd forgotted about that method, thanks for the reminder Bruce.

    Quote Originally Posted by szlamany
    The big difference is these timings is that the NAMES method is over 25% slower then the ORDINAL method!
    Yep, but pretty negligible over 80k reads!

    As always how much you should optimise depends on the requirements of that part of your program (and number of records - if it is huge!), as does whether you have the ability to know the field names in advance. I will personally still stick to rs.fields("fieldname").value for the majority of cases for the sake of readability - but use the field objects if speed is required (or ordinal positions when names are unknown).


    Thanks for bringing up this subject lilmark, it got us all thinking!

  11. #11
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: Undocumented code for faster data retrieval?

    I will personally still stick to rs.fields("fieldname").value for the majority of cases for the sake of readability - but use the field objects if speed is required (or ordinal positions when names are unknown).
    Get the best of both worlds, Ordinal positions and Readability by using an enum. Also easier to maintain, add new fields, reorder fields etc.

    VB Code:
    1. Private Enum FieldOrdinals
    2.     fldCustomerId = 1
    3.     fldCompanyName = 2
    4.     '...
    5. End Enum
    6.  
    7. Text1.text = rs.fields(fldCustomerId).Value

    We need more threads like these...

  12. #12
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,500

    Re: Undocumented code for faster data retrieval?

    That's an interesting suggestion... but it assumes that either the fields will be in the same location in all recordsets, or you have an Enum for each recordset.

    It also means that the code for a recordset is effectively split between two locations - the usage and the declarations.

  13. #13
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,952

    Re: Undocumented code for faster data retrieval?

    We've had issues with using ordinal pos for fields.... namely because we'll recycle SPs in various parts of the system. If we make a change to it (say, adding a new field to the mix) then it throws off any place we've used the SP. We could have specified that you never recycle SPs and that you always create new ones, but that defeats the purpose of reuse. But any ways... ultimately, the fastest way is using the bang (!) method. The reason the names col (using fields collection) is slowest is because it has to resolve the field name in the collection (internaly it looks it up then uses the index to pass as the ordinal). Using the ord pos is faster because it doesn't need to resolve the name first and look up the index (you've already done that). Some how using the ! bypasses even that and access right into the value property. Is it possible that the ! and Collect method are linked?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

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



Click Here to Expand Forum to Full Width