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

---

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

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).

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>

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.

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

@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

@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

Quote:

---

Originally Posted by techgnome

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...

Quote:

---

Originally Posted by wqweto

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.

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).

Quote:

---

Originally Posted by metal_navin

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