-
Apr 18th, 2017, 10:37 AM
#1
Thread Starter
New Member
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
-
Apr 18th, 2017, 10:45 AM
#2
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
-
Apr 18th, 2017, 10:45 AM
#3
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).
-
Apr 18th, 2017, 10:59 AM
#4
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>
-
Apr 18th, 2017, 11:11 AM
#5
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.
-
Apr 18th, 2017, 11:45 AM
#6
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
-
Apr 18th, 2017, 11:57 AM
#7
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.
-
Apr 18th, 2017, 12:08 PM
#8
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
-
Apr 18th, 2017, 12:24 PM
#9
Re: Very long open of recordset
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...
-
Apr 18th, 2017, 12:25 PM
#10
Thread Starter
New Member
Re: Very long open of recordset
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.
-
Apr 24th, 2017, 03:18 AM
#11
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).
-
Apr 24th, 2017, 04:55 AM
#12
Re: Very long open of recordset
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|