Re: ADO Performance question

> I would say it depends what you're trying to do, although I wouldn't
expect
> there to be much difference performance-wise. The difference comes from
ease
> of use.
>
> I would prefer the mechanism of using a recordset rather than constructing
> sql to use in the command text for the following reasons:
> 1) A recordset is typed so the data you are entering is validated before
you
> perform a round-trip to the database
> 2) Your updates are encapsulated in a recordset.
> 3) If you are constructing SQL on the fly (concatenation of strings) there
> is the chance of a syntactic error which will only be found at run-time.
> 4) The SQL should be placed inside a transaction.

We have been using this approach for the past 5 years with ADO and SQL
Server as the back end.
Basically we construct an empty Recordset (using SELECT
field1,field2,..,fieldN FROM mytable WHERE 0=1), proceed to populate it
using RecordBinding and then UpdateBatch(). All records are INSERTed. It
works but it is not wonderfully fast.

But I have asked myself, "Is this as fast as it gets?" and never been
satisfied.
And also I know that the Recordset has certain overheads.
It records each field 3 times (OriginalValue, UnderlyingValue and Value).
I want to d

I have tried Stored Procedures and also SQL INSERT statements via Command
object.
But this turned out to be slower.

But after reading a few messages by William Vaughn, I realised how it can be
improved.
Where the Stored Procedures/SQL INSERT speed goes is the round trip to the
server of an individual row. The SQL Server provider allows multiple
statements. You want to batch them up. So essentially you want to multiple
INSERTs - same as what UpdateBatch() does on watching the profiler without
the Recordset overhead.

So my guess would be for max ADO performance (without going DTS/BulkCopy
approach) in pseudocode

IF NRowsToInsert < SomeADOTheshold
' Do Connection Execute with individual Rows
ELSE
'Construct Command Object with say 32 Rows and (32*NFields ?) as temp
procedure
'Load, execute NRowsToInsert / 32 times
'Construct Command Object with NRowsToInsert remainder 32 and
(32*NFields ?) as temp
'Load, execute NRowsToInsert remainder 32
END IF

Stephen Howe








.

Relevant Pages

  • Re: Emailing a Report
    ... the reason Debug.Print was done was to test the SQL of what is being generated. ... If you will add and change data here, you need to set this up as a main/subform -- or just a main form that DISPLAYS data from Users and allows modifications to Agreements. ... If you are just going to send Agreements, then AgrID should be added to the form RecordSet so you can capture it. ... maybe also some kind of category for this followup type ...
    (microsoft.public.access.modulesdaovba)
  • Re: What is the advantage of Event programming?
    ... >> dice what lumps of data you need. ... which wouldn't work if the recordset has more than ... >My knowledge on using SQL statements is somewhat limited. ... Requesting a limited number of records at a time, was useful in the old days ...
    (microsoft.public.vb.general.discussion)
  • Re: REPOST: One Web Service updates SQL, the other cant
    ... insert is executed a reference to rs.eof is invalid and the program bombs. ... > get recordset back and ADO could generate error here. ... Another instance of the same Web Service code, ... >> Watching both the debugger and the trace, SQL is receiving what I send ...
    (microsoft.public.vb.database.ado)
  • Re: using Command to set Parameters and Recordset to retrive the Query
    ... doesn't the rsData will be interpretate as an input parameter in the SP? ... >> Query and retrive the Recordset so I can use the Paging property ... > Even if this technique of using the parameters in the ORDER BY does work for> you, I suspect that this will defeat your objective of preventing sql> injection. ... See below for a more efficient solution> using a stored procedure. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Help please on Record sets
    ... I tried running it from the query builder after deleting all the ... Just because your "original SQL string was generated in a working ... I had to add a quote to your first Formated Date and Change the quotes to ... recordset or would that lead to more problems? ...
    (microsoft.public.access.modulesdaovba)