Re: Can I append an ADO recordset to an Access table in one go?




"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:u5uM9GkGIHA.700@xxxxxxxxxxxxxxxxxxxxxxx
Mark J. McGinty wrote:
This is not a trival undertaking. It is neither supported nor
documented. The Microsoft-annointed flock will tell you, "not a best
practice" (apparently, the practice of making things more functional
than they are out of the box is somehow other than "best".)

Large numbers of rows in batch updates tend to perform poorly, you're
better off to chunk them up, no more than a few hundred rows per
batch.
Constraint/integrity violations caused by any rows inserted are
supposed to fail the whole batch, but it doesn't always happen like
that.
SQL Profiler shows that under the hood, ADO is builds a parameterized
INSERT statement for each row. Since you could do that yourself, and
skip the overhead of manipulating the XML, it's worth consideration.

I believe you have touched on the real reasons why I would label this a
less-than-optimal practice rather than the putdown in the initial
paragraph.

The reason for my negativity is the tendency to discount/reject very
powerful techniques, that most definitely do have constructive uses, merely
because they've been designated "not best". The OP asks, "is there a
way...?" The answer is yes, but only by changing the construction of the
recordset to suit your needs, which is very much possible to do, in its
persisted form.

I'm sure MS' motivation to discourage developers from foraging into this
realm involve the difficulty in supporting those who attempt to undertake it
without sufficiently considering all the ramifications -- all part of a
trend towards making thing's "easier" at the expense of exposing truly
powerful constructs. Does that mean capable developers should sacrifice
functionality in favor of "best practice"? IMHO, the answer is not just no,
but hell no.

There are some of us for whom that "ease > power" mindset is a hinderance,
not a help. For example (not that it strictly pertains here, but just to
describe my mindset) I consider the premise of Windows API being called
deprecated to be obscene and insulting. It logically follows that I have
great difficulty accepting, "oh don't do that, it's not best, just be happy
with what they gave you."

In the case of this OP, it's the classic "what you want is likely not what
you need" scenario, so I pointed that out. The reason what he wanted to do
is likely not the best choice is that other options may be better suited.
But there have been times that my suggestions were contradicted for the sole
reason "not a best practice" and if you hadn't already guessed, that rubs me
the wrong way. So I thought I'd try to preempt that.

Apologies if you objected to the "Microsoft-annointed flock" reference, it
was aimed at Microsoft MVP as an institution, not you specifically. If
you'd care to suggest an alternate term that does not infer undue esteem,
I'll consider using it in the future.

-Mark

btw, one aside about using batch updates to insert rows: given that ADO
implements this by constructing a parameterized INSERT statement and
executing it for each row, what is the advantage of doing the same thing in,
say, script under ASP? Like script code could bind parameters to a
command/execute it in a loop, more efficiently than ADO can in a DLL?

I get the reasons some don't like updating data via UpdateBatch, ADO's
tendency to send unchanged rows over the wire is needless and annoying -- I
never allow it to do that, though. The thing that no one mentions when they
state a preference for constructing UPDATE statements is that practice
completely ignores concurrency (beyond ordinary SQL locking, of course.)
There are many, many scenarios where pushing an update over top of other
changes that may have been made sinc