I know that transactions are ideal for making sure that a group of inserts, updates, or
deletes is handled as a whole.  For instance, when posting an order to make sure that all
related tables are updated together.

But suppose I'm importing 50,000 records.  Can I use a single transaction to handle the
entire import?  (ie: either import them all, or if there's a problem, import none of them).

Thanks,

Dale

Dale,

<< But suppose I'm importing 50,000 records.  Can I use a single transaction
to handle the entire import?  (ie: either import them all, or if there's a
problem, import none of them). >>

For 50,000 rows, yes.  For 1,000,000 rows, you'll probably want to break
things up a bit.  I would say that the cut-off point for the largest
transaction is around 200,000 rows.  Anything larger than that, and you'll
want to investigate using multiple transactions.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
> Dale,
>
> << But suppose I'm importing 50,000 records.  Can I use a single transaction
> to handle the entire import?  (ie: either import them all, or if there's a
> problem, import none of them). >>
>
> For 50,000 rows, yes.  For 1,000,000 rows, you'll probably want to break
> things up a bit.  I would say that the cut-off point for the largest
> transaction is around 200,000 rows.  Anything larger than that, and you'll
> want to investigate using multiple transactions.
>


Tim,

What is it that becomes an issue after around 200,000 rows.  In
otherwords, how will I know when I've hit the limit?

Thanks,

Dale

Dale,

<< What is it that becomes an issue after around 200,000 rows.  In
otherwords, how will I know when I've hit the  limit? >>

You start to hit a point of diminishing returns where the buffering overhead
costs more and more processing time.

--
Tim Young
Elevate Software
www.elevatesoft.com