Results 1 to 12 of 12

Thread: Very long open of recordset

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2017
    Posts
    2

    Very long open of recordset

    Good day.
    I have a vb program that inserts data into a table called 'readings'. Currently this table has 1.4 billion records.

    Now, each day when I have to do a load, it takes approximately 8 hours to open the recordset being used for the insert. Below is the code.
    Can this code be optimized to prevent such a long time to open the recordset?

    Code:
    Set conn = New ADODB.Connection
        conn.open "Provider=SQLOLEDB.1;Password=admin;Persist Security Info=True;User ID=john;Initial Catalog=databasetable;Data Source=server"
     
        Set rs = New ADODB.Recordset
        With rs
                .CursorLocation = adUseClient
                .LockType = adLockBatchOptimistic
                .Properties("append-only rowset") = True
                .open "Select * from readings", conn
                .ActiveConnection = Nothing
        End With

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Very long open of recordset

    1) Don't load 1.4 billion rows. That's just insane. Only load the fields and rows you plan to work with.
    2) You're opening a client side cursor... so yeah, when you download 1.4 Billion records, that's going to take some time. See suggestion #1


    There is almost never any reason to Select * on any table... especially on one with that many records. Rarely does anyone need all of the fields either. Even if you do, list them.
    But for god's sake put a filter on that query.

    -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??? *

  3. #3
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    Re: Very long open of recordset

    You said that you are opening the recordset to do the insert - do you really need all the records and columns for that? If not, you can execute an INSERT SQL statement and skip the opening of the recordset entirely.

    If you need only a subset of the records for your insert, then you should use a WHERE clause in your SELECT statement to limit the result set to just the records you need (which could save a significant amount of time if for example you only need data from the previously inserted record).

  4. #4
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: Very long open of recordset

    Try putting a WHERE clause like this
    Code:
    .open "Select * from readings where 0=1", conn
    . . . if you are going to append only.

    cheers,
    </wqw>

  5. #5
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: Very long open of recordset

    I'm not sure because I seldom used ADO.
    But can't you open the whole table like this?

    Code:
    Set conn = New ADODB.Connection
        conn.open "Provider=SQLOLEDB.1;Password=admin;Persist Security Info=True;User ID=john;Initial Catalog=databasetable;Data Source=server"
     
        Set rs = New ADODB.Recordset
        With rs
                .CursorLocation = adUseClient
                .LockType = adLockBatchOptimistic
                .Properties("append-only rowset") = True
                .open "readings", conn
                .ActiveConnection = Nothing
        End With
    I don't know how faster this will be anyway.

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Very long open of recordset

    For 1.4 BILLION records... no, that will not be any faster than a SELECT * FROM ... since it is essentially the same thing. But 1) you shouldn't use select *, and 2) you certainly shouldn't be selecting that many records w/o a where clause in the first place.

    -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??? *

  7. #7
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Very long open of recordset

    @tg.

    Code:
    .Properties("append-only rowset") = True
    works with some OLEDB providers like Jet.
    actually last I checked that didn't work with SQL Server, but who knows given his connection string...

    edit:I guess that's the real problem. He's expecting a DBPROP_APPENDONLY rowset...
    but that only works with MS Access/Jet
    Last edited by DEXWERX; Apr 18th, 2017 at 12:05 PM.

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Very long open of recordset

    @Dex - I wondered what he's connecting to... if there are truly 1.4Billion rows... I'm pretty sure it is NOT access... good gawd I hope it isn't. If it is, that might be another bottleneck as well. If he is connecting to SQL Server, then that property is happily ignored and ... well, he ends up loading every row.

    -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??? *

  9. #9
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: Very long open of recordset

    Quote Originally Posted by techgnome View Post
    For 1.4 BILLION records... no, that will not be any faster than a SELECT * FROM ... since it is essentially the same thing. But 1) you shouldn't use select *, and 2) you certainly shouldn't be selecting that many records w/o a where clause in the first place.

    -tg
    Well, I have almost no experience with ADO, but if it worked like DAO (that I do know) it wouldn't be the same thing at all.
    I mean, to open a table type recorset is much faster than SELECT * FROM.
    But if you say so...

  10. #10

    Thread Starter
    New Member
    Join Date
    Apr 2017
    Posts
    2

    Re: Very long open of recordset

    Quote Originally Posted by wqweto View Post
    Try putting a WHERE clause like this
    Code:
    .open "Select * from readings where 0=1", conn
    . . . if you are going to append only.

    cheers,
    </wqw>
    this looks like a good idea to me, with not much changing to my program.

    do you think this would work Techgnome?

    yep guys this is a SQL Server database.

  11. #11
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: Very long open of recordset

    Have you considered the suggestion in post #3? There is no need to open the table at all, just use an action query like INSERT e.g. INSERT INTO table1 (column1, column2) VALUES(data1, data2).
    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

  12. #12
    gibra
    Guest

    Re: Very long open of recordset

    Quote Originally Posted by metal_navin View Post
    this looks like a good idea to me, with not much changing to my program.

    do you think this would work Techgnome?

    yep guys this is a SQL Server database.
    Yes, but:
    1) don't use [SELECT * ...], use [SELECT field1, field2, ... ] instead
    2) don't use ORDER BY (eventually use Sort property of the Recordset opened)
    3) For INSERT use: WHERE 1=0
    4) for UPDATE use: WHERE <primarykey> = ID

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