"ML" wrote:

> Is there no possibility of executing those procedures *before* you begin the
> transaction?
>
> It seems this procedure of yours is a busy one. It does many things. Is
> breaking them up into several transactions also not possible?
>
>
> ML

"MedioYMedio" <MedioYMe***@discussions.microsoft.com> wrote in message
news:4D3565A7-E117-4849-8EC6-843180FD43C0@microsoft.com...
> I'm experimenting performance problems in a process that manage 1M rows
and
> updates several times some columns.
>
> The issue is that the whole process is under a transaction (must be) but
> there are some queries within the transaction that i don't need to keep
> logged, meaning that if the transaction fails, it isn't importante the
state
> of those queries.
>
> As i'm trying to improve performance, my question is:
>
> Can i exclude a query from a transaction on purpose?
>
> Example:
> begin transaction
>   exec sp_logged_one
>   exec sp_logged_one
>
>   exec sp_not_logged_one    -- this sp should not be included in the
> transaction
>   exec sp_not_logged_two    -- this sp should not be included in the
> transaction
>
>   exec sp_logged_nine
>   exec sp_logged_ten
> commit transaction
>
> if i exclude the meaningless sps from the transaction i'm sure i will
> improve the performance.
>
> (i've read some "solutions" that opens another process to avoid
transaction
> inclusion buy i would prefer a more "elegant" solution like a hint, etc.)
>
> Regards,

"Brian Selzer" wrote:

> My suggestion is to combine the SPs into a single one.  Use optimistic
> concurrency with roversioning if you can (If you have a rowversion column on
> each table, you can simply store the maximum rowversion for the affected
> rows in each table used to calculate the results and any table that is the
> target of the update to guarantee consistency.)  Perform all reads during
> the calculation phase using WITH(NOLOCK) and cache the results in table
> variables.  Once all of the time-consuming actions have been completed,
> you're ready to start updating, so start a transaction, re-read the maximum
> rowversion for each set of affected rows from each table used to calculate
> the results WITH(REPEATABLEREAD) to determine whether some other user
> changed a row and to ensure that no other user can change the values once
> you start updating.  Re-read the maximum rowversion for each  from the rows
> in the tables that you're going to update WITH(UPDLOCK) to ensure that no
> other user can change one of the rows that you're going to update.  If none
> of the rowversions have changed, then that means that the calculations are
> correct and you can perform the updates.  If on the other hand, any
> rowversion is different, roll back the transaction to release the locks and
> start over.  While it is possible for a collision to occur, their frequency
> is usually low, so the overall performance hit is negligible.  The benefit
> of this approach is that at the end, only set-based operations are performed
> to update base tables--one for each type of update for each table, DELETE,
> UPDATE, INSERT, in that order.  This minimizes the interval during which the
> rows are locked, which maximizes concurrency.  It also means that triggers
> only fire once.  You may find that even with a collision, this approach
> operates much faster than holding the locks throughout all of the
> subordinate procedures.
>
> If you don't have rowversion columns on your tables and you can't add one,
> your only option is to read out the affected rows into table variables,
> perform the calculations using the table variables as the source (DON'T
> UPDATE THEM!)  Perform the calculations as above caching the results in
> separate set of results table variables.  When you're ready to update
> compare the rows in the table variables that were read out to perform the
> calculations and the rows that are expected to be updated with the rows in
> the base tables to find out if someone changed a row while you were
> calculating.  Again REPEATABLEREAD, and UPDLOCK as above.
>
> "MedioYMedio" <MedioYMe***@discussions.microsoft.com> wrote in message
> news:4D3565A7-E117-4849-8EC6-843180FD43C0@microsoft.com...
> > I'm experimenting performance problems in a process that manage 1M rows
> and
> > updates several times some columns.
> >
> > The issue is that the whole process is under a transaction (must be) but
> > there are some queries within the transaction that i don't need to keep
> > logged, meaning that if the transaction fails, it isn't importante the
> state
> > of those queries.
> >
> > As i'm trying to improve performance, my question is:
> >
> > Can i exclude a query from a transaction on purpose?
> >
> > Example:
> > begin transaction
> >   exec sp_logged_one
> >   exec sp_logged_one
> >
> >   exec sp_not_logged_one    -- this sp should not be included in the
> > transaction
> >   exec sp_not_logged_two    -- this sp should not be included in the
> > transaction
> >
> >   exec sp_logged_nine
> >   exec sp_logged_ten
> > commit transaction
> >
> > if i exclude the meaningless sps from the transaction i'm sure i will
> > improve the performance.
> >
> > (i've read some "solutions" that opens another process to avoid
> transaction
> > inclusion buy i would prefer a more "elegant" solution like a hint, etc.)
> >
> > Regards,
>
>
>

"MedioYMedio" <MedioYMe***@discussions.microsoft.com> wrote in message
news:2985F2A2-AED7-4C54-ABDF-4894C01A9E42@microsoft.com...
> First of all, i appreciate your quick and complete response.
> I'm still trying to understand your solution, but with a first sight i can
> tell that the overhead produced by this rearrangement won't be acceptable.
>
> The concurrency is not a problem, this process will rule the server while
> executing. The problem is the atomicy of the transaction and, of course,
> performance.
>
> Regards,
>
> "Brian Selzer" wrote:
>
> > My suggestion is to combine the SPs into a single one.  Use optimistic
> > concurrency with roversioning if you can (If you have a rowversion
column on
> > each table, you can simply store the maximum rowversion for the affected
> > rows in each table used to calculate the results and any table that is
the
> > target of the update to guarantee consistency.)  Perform all reads
during
> > the calculation phase using WITH(NOLOCK) and cache the results in table
> > variables.  Once all of the time-consuming actions have been completed,
> > you're ready to start updating, so start a transaction, re-read the
maximum
> > rowversion for each set of affected rows from each table used to
calculate
> > the results WITH(REPEATABLEREAD) to determine whether some other user
> > changed a row and to ensure that no other user can change the values
once
> > you start updating.  Re-read the maximum rowversion for each  from the
rows
> > in the tables that you're going to update WITH(UPDLOCK) to ensure that
no
> > other user can change one of the rows that you're going to update.  If
none
> > of the rowversions have changed, then that means that the calculations
are
> > correct and you can perform the updates.  If on the other hand, any
> > rowversion is different, roll back the transaction to release the locks
and
> > start over.  While it is possible for a collision to occur, their
frequency
> > is usually low, so the overall performance hit is negligible.  The
benefit
> > of this approach is that at the end, only set-based operations are
performed
> > to update base tables--one for each type of update for each table,
DELETE,
> > UPDATE, INSERT, in that order.  This minimizes the interval during which
the
> > rows are locked, which maximizes concurrency.  It also means that
triggers
> > only fire once.  You may find that even with a collision, this approach
> > operates much faster than holding the locks throughout all of the
> > subordinate procedures.
> >
> > If you don't have rowversion columns on your tables and you can't add
one,
> > your only option is to read out the affected rows into table variables,
> > perform the calculations using the table variables as the source (DON'T
> > UPDATE THEM!)  Perform the calculations as above caching the results in
> > separate set of results table variables.  When you're ready to update
> > compare the rows in the table variables that were read out to perform
the
> > calculations and the rows that are expected to be updated with the rows
in
> > the base tables to find out if someone changed a row while you were
> > calculating.  Again REPEATABLEREAD, and UPDLOCK as above.
> >
> > "MedioYMedio" <MedioYMe***@discussions.microsoft.com> wrote in message
> > news:4D3565A7-E117-4849-8EC6-843180FD43C0@microsoft.com...
> > > I'm experimenting performance problems in a process that manage 1M
rows
> > and
> > > updates several times some columns.
> > >
> > > The issue is that the whole process is under a transaction (must be)
but
> > > there are some queries within the transaction that i don't need to
keep
> > > logged, meaning that if the transaction fails, it isn't importante the
> > state
> > > of those queries.
> > >
> > > As i'm trying to improve performance, my question is:
> > >
> > > Can i exclude a query from a transaction on purpose?
> > >
> > > Example:
> > > begin transaction
> > >   exec sp_logged_one
> > >   exec sp_logged_one
> > >
> > >   exec sp_not_logged_one    -- this sp should not be included in the
> > > transaction
> > >   exec sp_not_logged_two    -- this sp should not be included in the
> > > transaction
> > >
> > >   exec sp_logged_nine
> > >   exec sp_logged_ten
> > > commit transaction
> > >
> > > if i exclude the meaningless sps from the transaction i'm sure i will
> > > improve the performance.
> > >
> > > (i've read some "solutions" that opens another process to avoid
> > transaction
> > > inclusion buy i would prefer a more "elegant" solution like a hint,
etc.)
> > >
> > > Regards,
> >
> >
> >

"Brian Selzer" wrote:

> You could find that the overhead might be offset by the faster reads and
> calculations.  You won't know until you try.
>
> I tuned a system that was designed to process 14,000 transactions per hour
> so that it could process over 200,000 transactions per hour.  Optimistic
> concurrency with rowversioning.  Close attention to execution plans:
> eliminating reads and writes within cursors,  eliminating correlated
> subqueries by flattening them out into joins by using a set of cursors.
> Waiting 'til the last possible moment to start a transaction--sometimes
> jumping through hoops to make it possible.  Each of these bought me
> measurable performance improvement.
>
> "MedioYMedio" <MedioYMe***@discussions.microsoft.com> wrote in message
> news:2985F2A2-AED7-4C54-ABDF-4894C01A9E42@microsoft.com...
> > First of all, i appreciate your quick and complete response.
> > I'm still trying to understand your solution, but with a first sight i can
> > tell that the overhead produced by this rearrangement won't be acceptable.
> >
> > The concurrency is not a problem, this process will rule the server while
> > executing. The problem is the atomicy of the transaction and, of course,
> > performance.
> >
> > Regards,
> >
> > "Brian Selzer" wrote:
> >
> > > My suggestion is to combine the SPs into a single one.  Use optimistic
> > > concurrency with roversioning if you can (If you have a rowversion
> column on
> > > each table, you can simply store the maximum rowversion for the affected
> > > rows in each table used to calculate the results and any table that is
> the
> > > target of the update to guarantee consistency.)  Perform all reads
> during
> > > the calculation phase using WITH(NOLOCK) and cache the results in table
> > > variables.  Once all of the time-consuming actions have been completed,
> > > you're ready to start updating, so start a transaction, re-read the
> maximum
> > > rowversion for each set of affected rows from each table used to
> calculate
> > > the results WITH(REPEATABLEREAD) to determine whether some other user
> > > changed a row and to ensure that no other user can change the values
> once
> > > you start updating.  Re-read the maximum rowversion for each  from the
> rows
> > > in the tables that you're going to update WITH(UPDLOCK) to ensure that
> no
> > > other user can change one of the rows that you're going to update.  If
> none
> > > of the rowversions have changed, then that means that the calculations
> are
> > > correct and you can perform the updates.  If on the other hand, any
> > > rowversion is different, roll back the transaction to release the locks
> and
> > > start over.  While it is possible for a collision to occur, their
> frequency
> > > is usually low, so the overall performance hit is negligible.  The
> benefit
> > > of this approach is that at the end, only set-based operations are
> performed
> > > to update base tables--one for each type of update for each table,
> DELETE,
> > > UPDATE, INSERT, in that order.  This minimizes the interval during which
> the
> > > rows are locked, which maximizes concurrency.  It also means that
> triggers
> > > only fire once.  You may find that even with a collision, this approach
> > > operates much faster than holding the locks throughout all of the
> > > subordinate procedures.
> > >
> > > If you don't have rowversion columns on your tables and you can't add
> one,
> > > your only option is to read out the affected rows into table variables,
> > > perform the calculations using the table variables as the source (DON'T
> > > UPDATE THEM!)  Perform the calculations as above caching the results in
> > > separate set of results table variables.  When you're ready to update
> > > compare the rows in the table variables that were read out to perform
> the
> > > calculations and the rows that are expected to be updated with the rows
> in
> > > the base tables to find out if someone changed a row while you were
> > > calculating.  Again REPEATABLEREAD, and UPDLOCK as above.
> > >
> > > "MedioYMedio" <MedioYMe***@discussions.microsoft.com> wrote in message
> > > news:4D3565A7-E117-4849-8EC6-843180FD43C0@microsoft.com...
> > > > I'm experimenting performance problems in a process that manage 1M
> rows
> > > and
> > > > updates several times some columns.
> > > >
> > > > The issue is that the whole process is under a transaction (must be)
> but
> > > > there are some queries within the transaction that i don't need to
> keep
> > > > logged, meaning that if the transaction fails, it isn't importante the
> > > state
> > > > of those queries.
> > > >
> > > > As i'm trying to improve performance, my question is:
> > > >
> > > > Can i exclude a query from a transaction on purpose?
> > > >
> > > > Example:
> > > > begin transaction
> > > >   exec sp_logged_one
> > > >   exec sp_logged_one
> > > >
> > > >   exec sp_not_logged_one    -- this sp should not be included in the
> > > > transaction
> > > >   exec sp_not_logged_two    -- this sp should not be included in the
> > > > transaction
> > > >
> > > >   exec sp_logged_nine
> > > >   exec sp_logged_ten
> > > > commit transaction
> > > >
> > > > if i exclude the meaningless sps from the transaction i'm sure i will
> > > > improve the performance.
> > > >
> > > > (i've read some "solutions" that opens another process to avoid
> > > transaction
> > > > inclusion buy i would prefer a more "elegant" solution like a hint,
> etc.)
> > > >
> > > > Regards,
> > >
> > >
> > >
>
>
>