Slow ADO...is there a faster way? 1

[bluenoser337]

My Access 2000 DB has data being written to a single table from a VB6 application (ADO). At around 170,000 records, the time required to store a record can be as long as 10 seconds. At zero records, I can store more than 10 per second. What causes the slowdown...and is there a way around it? With each record insertion I open and then close the connection.
connection.open
set rst = new adodb recordset
rst.open
rst.addnew
rst!"fields"
etc
etc
rst.update
rst.close
connection.close

Of course, not all the code is shown here. Can I perform the same "single record" insertion without opening the entire table each time? Many thanks!!

 

[williamu]

Hi, Well start by keeping the connection open, and use a stored procedure to insert the data. Once the SQL is prepared it's a simple case of binding the parameter data.

If you do it this way then you should not slow down to much. The only real time this would happen is when a new page (space on disk) is allocated.

HTH


William
Software Engineer
ICQ No. 56047340

 

[bluenoser337]

williamu...you make it sound so easy. Unfortunately...I'm a bit green on this stuff, so I don't know what code (SQL or VB) to use to insert one record (with about 8 fields) into the DB the way you mention. Some research is needed. Many thanks!

 

[TomKane]

I have an example of an update statement - don't really use access anymore so the example is using dao:

strdbPath = App.Path & "\lottery.mdb"
Set dbs = OpenDatabase(strdbPath)

dbs.Execute "UPDATE Total_Profit SET Tickets_Sold = " & lnNumberofTickets & ", Total_Revenue = " & lnTotalRevenue & ",Total_Profit = " & lnTotalProfit & ",Charity_Payments=" & lnCharity & ",Customer_Commission=" & lnCustomerComm & " where Date = now();"
dbs.Close

 

[williamu]

Have a look at this thread

http://www.tek-tips.com/viewthread.cfm?SQID=865963&SPID=222&page=1

Is shows you how to use SP and Parameters.

And this is an example of an Access (2000) Stored Procedure (Query)

PARAMETERS pID Number, pName Text ( 64 ), pAddr1 Text ( 50 ), pAddr2 Text ( 50 ), pAddr3 Text ( 50 ), pTown Text ( 50 ), pPostcode Text ( 10 ), pTelNo Text ( 24 ), pFaxNo Text ( 24 ), pVatNo Text ( 24 );
INSERT INTO Company ( Name, Addr1, Addr2, Addr3, Town, Postcode, TelNo, FaxNo, VatNo )
VALUES (pName, pAddr1, pAddr2, pAddr3, pTown, pPostcode, pTelNo, pFaxNo, pVatNo);


HTH


William
Software Engineer
ICQ No. 56047340

 

[strongm]

>use a stored procedure

In Access?

 

[KyleS]

Yeah, you can use Stored Proceedures in Access, it's just that very few people know of them, or use them...

The other quick option with ADO would be to just send the SQL commands, like TomKane suggested, but with ADO object so you don't have to change any of your connections, etc.

Something like:

[COLOR=blue]Dim[/color] strSQL [COLOR=blue]As String[/color]

strSQL = "" [COLOR=green]'Write your Query here[/color]

connection.Execute strSQL, , adExecuteNoRecords

Kyle

 

[RiverGuy]

Never rule out the possibility that your table might be too indexed.

 

[williamu]

RiverGuy has a point there. If you have a lot of fields indexed either singley or compound then these will require processing as well. If this is the case then you may want to look at batch processing options for your Object.

Or if you're in the fortunate position of being able to dump the indicies you should do this then rebuild them after your insertions have complete.

As far as sending raw SQL to the DB I would say that this would be wrong for this instance. Using SPs is by far the fastest and most elegant way of doing this (or any DB actions). If you're not doing so you may also want to use the OLEDB driver as well since these (OLEDB) drivers have the least amount of layers of abstraction down to the Driver.



William
Software Engineer
ICQ No. 56047340

 

[strongm]

Oh, hang on. I have to say that the queries you can store in an Access mdb (essentially what DAO used to call QueryDefs) are hardly the equivalent of Oracle or SQL Server Stored Procedures

 

[williamu]

Granted strongm, they're not as powerful as full blown SP in the likes of PostgreSQL, Oracle etc., but they do offer access to the VBA runtime which does give them a modicum of flexability.



William
Software Engineer
ICQ No. 56047340

 

[strongm]

Sure, but what I meant was that Access stored queries are not precompiled, optimised, cached after first use, or anything else like that. Nor do they run on an optimised backend server. So, given that we are talking about speed isues here, is there really ny advantage to using them?

I have to say that I've not ever done any timing exercises against Access stored queries, so I don't know the answer to this; it may well be that there is a speed up.

 

[williamu]

Well the queries are precompiled if you use the Prepare() method, so after first use they do not need to get reparsed. Whether any optimisation or caching is done by the driver I'm not in a position to answer that.

But I'm willing to bet that on a raw SQL -v- Prepared Query, that the PQ will show very favourable results, given multiple INSERTS to work on. I know this for sure which is why I recommended it.

;-)


William
Software Engineer
ICQ No. 56047340

 

[strongm]

And here is someone who has done some (informal) benchmarking that seems to confirm this:

http://www.15seconds.com/issue/020919.htm

 

[williamu]

Thanks strongm, I appreciate the vindication. Mind you there are other benefits in using Stored Queries, as they reduce the chance of being exploited using SQL injection techniques on websites etc.

Of course like most things, the performance gained (if any) will be relative to the composition of the query. If you write a query without the correct use of the () in the clause predicates for example, then you still have a duff query. And duff query can bring a DB server to its knees.


William
Software Engineer
ICQ No. 56047340

 

[bluenoser337]

I think I can do what I need with KyleS's example...
Dim strSQL As String
strSQL = "" 'Write your Query here
connection.Execute strSQL, , adExecuteNoRecords
except I don't know how to access the VB variables in the SQL "INSERT INTO mytable (field1, field2) values (?variable1?,?variable2?)"
I need to know what combination of ', &, ", or whatever must surround the variable names in the SQL. Remember, this is Access2000. Many thanks!

 

[KyleS]

Your SQL should look something like this:

strSQL = "INSERT INTO MyTable (field1, field2, field3) VALUES (" & VAR1 & ", " & VAR2 & ", " & VAR3)"

That's if they are Numbers.

For strings, you would want to put a (' before and after the Quotes, like so

"VALUES = ([b][COLOR=red]'[/color][/b]" & VAR1 & "[b][COLOR=red]'[/color][/b]"

For dates, you would want to put a (# before and after the Quotes, like so

"VALUES = ([b][COLOR=red]#[/color][/b]" & VAR1 & "[b][COLOR=red]#[/color][/b]"

Kyle

 

[bluenoser337]

KyleS...works great! Thanks!!

 

[dilettante]

Use Cursor-Based Updating Only If Needed

Try to avoid the use of cursor-based updating. Although using an SQL statement to update data is not feasible in many scenarios, you should use it where possible. Although updating data through the Recordset object is often more convenient, it is also much more expensive. Despite being cumbersome to use, updating data through an SQL statement is well worth the trouble. The routine that uses SQL will update several dozen records in a 5,000 record table about 30 times faster than the routine that uses a cursor.

... from: Improving MDAC Application Performance

This lends further credence to the idea of using SQL or a stored procedure. Of course it really talks about multi-record updates. Since you are doing single updates other overhead should wash out any advantages of further optimization. But I assume that by this point you have stopped the repeated opens/closes of the database for every record insertion.

Did you ever consider ADO batch updating?

It still isn't clear to me what the profile of your application is. One run, one insertion? User-driven one-by-one insertions from user input? Multi-record insertions for each run of the program?


Some performance loss may be due to internal fragmentation, both in the data pages themselves and perhaps particularly within index tables. Can you implment a regimen of scheduled database compaction on this MDB?

Have you tried measuring performance at 170,000 records, then compacting the database and measuring again?

 

[bluenoser337]

dilettante...my VB application is automatically collecting production data for each part manufactured on an assembly line. As each part is manufactured, its measurements and other details make up a single record (about 1 per second) which is written to Access with each part. I am now using the SQL method and, under test, have no speed problems even at >3 million records! Don't know what ADO batch updating is...or if it would benefit me here. Thanks for the post.