"flatliner60" <flatline***@discussions.microsoft.com> wrote in message
news:5A2CBDCD-9385-4645-B03C-A2615E3B72C1@microsoft.com...
> We have a business Logic component which imports into multiple tables from
> a
> variety of flat files.  To reduce the length of a lock being held on the
> tables we were considering that each dependancy tree be first attempted in
> a
> single transaction and rolled back. Once all have been through the paces
> and
> rolled back, we then go through and commit each separately. What would be
> the
> impact of an implementation like this on both Read/Write performance and
> Automatic Recovery?  Are we barking up the wrong tree, should we just put
> this whole thing in a Single Transaction?
>
> Regards

"Andrew J. Kelly" wrote:

> I fail to see the logic in this.  Maybe I am missing something but you will
> essentially do triple the work and hold locks open for a longer period of
> time not less.  If you simply issued the statements and committed the
> transaction it would take x amount of time.  The way you are looking at
> doing it you would have that time plus the time it takes to do each part and
> then the time to roll each part back.  What you should concentrate on is
> making sure the transactions have as little between the Begin and commit as
> possible.  Do not place anything in side that can be done outside before you
> issue the Begin tran. Also make sure the code is optimized as much as
> possible and you have the proper indexes to support row level locking.  That
> will give you the biggest bang for the buck.
>
> --
> Andrew J. Kelly  SQL MVP
>
>
> "flatliner60" <flatline***@discussions.microsoft.com> wrote in message
> news:5A2CBDCD-9385-4645-B03C-A2615E3B72C1@microsoft.com...
> > We have a business Logic component which imports into multiple tables from
> > a
> > variety of flat files.  To reduce the length of a lock being held on the
> > tables we were considering that each dependancy tree be first attempted in
> > a
> > single transaction and rolled back. Once all have been through the paces
> > and
> > rolled back, we then go through and commit each separately. What would be
> > the
> > impact of an implementation like this on both Read/Write performance and
> > Automatic Recovery?  Are we barking up the wrong tree, should we just put
> > this whole thing in a Single Transaction?
> >
> > Regards
>
>
>

"flatliner60" <flatline***@discussions.microsoft.com> wrote in message
news:0CF007DE-E2D9-4B45-8377-52724E62E7FB@microsoft.com...
> Thanks for your response Andrew,
>   Yes in normal circumstances this wouldn't even be a question: less
> transactions is better. But the reason we are questioning this is that we
> have one transaction that could potentially long running (i.e. 20-30
> seconds). This being on tables that have a high amount of activity by
> other
> users on the system. If this transaction is running while another user is
> attempting to make a change, they could be waiting this period for the
> lock
> to be released. If, however, we break the transaction up into smaller
> portions, attempt an execution to see if any failures occur, rollback and
> then if all are successful, run the small transactions again, committing.
> It's not particularly foolproof, and perhaps it's just better to use a
> ReadUncommitted transaction and be done with it.
>
> "Andrew J. Kelly" wrote:
>
>> I fail to see the logic in this.  Maybe I am missing something but you
>> will
>> essentially do triple the work and hold locks open for a longer period of
>> time not less.  If you simply issued the statements and committed the
>> transaction it would take x amount of time.  The way you are looking at
>> doing it you would have that time plus the time it takes to do each part
>> and
>> then the time to roll each part back.  What you should concentrate on is
>> making sure the transactions have as little between the Begin and commit
>> as
>> possible.  Do not place anything in side that can be done outside before
>> you
>> issue the Begin tran. Also make sure the code is optimized as much as
>> possible and you have the proper indexes to support row level locking.
>> That
>> will give you the biggest bang for the buck.
>>
>> --
>> Andrew J. Kelly  SQL MVP
>>
>>
>> "flatliner60" <flatline***@discussions.microsoft.com> wrote in message
>> news:5A2CBDCD-9385-4645-B03C-A2615E3B72C1@microsoft.com...
>> > We have a business Logic component which imports into multiple tables
>> > from
>> > a
>> > variety of flat files.  To reduce the length of a lock being held on
>> > the
>> > tables we were considering that each dependancy tree be first attempted
>> > in
>> > a
>> > single transaction and rolled back. Once all have been through the
>> > paces
>> > and
>> > rolled back, we then go through and commit each separately. What would
>> > be
>> > the
>> > impact of an implementation like this on both Read/Write performance
>> > and
>> > Automatic Recovery?  Are we barking up the wrong tree, should we just
>> > put
>> > this whole thing in a Single Transaction?
>> >
>> > Regards
>>
>>
>>