I'm working with a process that is initially invoked from VB, but runs
through a set of stored procedures. The first four levels of the calls are
simply gathering data that the other levels below will need and aggregating
some of it. This data is used to determine if further processing is needed or
not, and if so, calls the next level. W/o going into too much detail, since
the upper levels are simply data gathering, I've been rather liberal in my
application of WITH (NOLOCK) on virtually every table that is used in the
look up.
Here's the reason why, initially the process took two hours to run and it
didn't fully complete. Since my users are not in a Snickers commercial, I can
hardly expect them to wait that long for this process. So I need to make it
as fast as possible. I've gone through and changed all my CURSORS to select
loops (mucho improvement) and also dumped #tempTables in favor of table
variables (more improvement)... and then I added WITH (NOLOCK) on every
normal (non temp or derrived - ie inner selects) table in all queries... I
notice more improvement again. I also rearranged a few queries where some
calculations were done breaking out the data into two parts and changed a
LEFT JOIN to an INNER JOIN....
IS the ANY thing else I can do to squeeze some performance out of this
monster? I haven't run it through profiler yet, but that's the next obvious
choice.
The other half of the query is: is it possible to go too far with WITH
(NOLOCK)? Or is what I've done reasonable?
=chrisOn Tue, 28 Feb 2006 13:16:26 -0800, CAnderson wrote:
>The other half of the query is: is it possible to go too far with WITH
>(NOLOCK)? Or is what I've done reasonable?
Hi Chris,
I'll start here.
If performance gain is your sole target, you can use this hint freely.
But if you want correct reports, beware. As another poster in this
groups once said it: WITH (NOLOCK) can give you incorrect results at a
blinding speed.
SQL Server will normally lock data that has been changed but not yet
committed. Other queries have to wait for this lock to be released
before they can access the data. With WITH (NOLOCK), you ignore the
lock, which means that you'll read the uncommitted data. This saves lots
of time if there are locks, and it even saves some time if there are no
locks since you bypass the overhead of checking for locks.
But the downside is that you can read uncommitted data. Suppose that I
update a column to one billion dollars negative. Some sanity check in a
trigger will probably catch this and rollback my transaction - but if
your report runs in the periode between my submitting the update and the
trigger rolling it back, your report will be off by a billion dollars.
Another example - suppose a transaction is debiting your account and
crediting mine. Your report runs before my account is credited, but
after yours is debited. Now, the totals on the left-hand side of your
report won't match those on the right-hand side and all bookkeepers,
accountants and controllers in your company will go crazy.
>I've gone through and changed all my CURSORS to select
>loops (mucho improvement) and also dumped #tempTables in favor of table
>variables (more improvement)...
(snip)
>IS the ANY thing else I can do to squeeze some performance out of this
>monster?
Revisit your code. Try to get rid of all cursors, all select loops, all
temp tables and all table variables. SQL Server is optimized for
declarative, set-based processing. All procedural, row-based code (both
cursor and select loop; both temp table and table variable) will almost
always be slower than one single or a short batch of set-based queries.
Check if all your queries use indexes. Add indexes where necessary.
Remove unused indexes. Pay special attention to your choice of clustered
index.
If you need more specific help than this, you'll need to give more
specific information. Check out www.aspfaq.com/5006.
--
Hugo Kornelis, SQL Server MVP|||CAnderson [MVP] wrote:
> I'm working with a process that is initially invoked from VB, but runs
> through a set of stored procedures. The first four levels of the
> calls are simply gathering data that the other levels below will need
> and aggregating some of it. This data is used to determine if further
> processing is needed or not, and if so, calls the next level. W/o
> going into too much detail, since the upper levels are simply data
> gathering, I've been rather liberal in my application of WITH
> (NOLOCK) on virtually every table that is used in the look up.
> Here's the reason why, initially the process took two hours to run
> and it didn't fully complete. Since my users are not in a Snickers
> commercial, I can hardly expect them to wait that long for this
> process. So I need to make it as fast as possible. I've gone through
> and changed all my CURSORS to select loops (mucho improvement) and
> also dumped #tempTables in favor of table variables (more
> improvement)... and then I added WITH (NOLOCK) on every normal (non
> temp or derrived - ie inner selects) table in all queries... I
> notice more improvement again. I also rearranged a few queries where
> some calculations were done breaking out the data into two parts and
> changed a LEFT JOIN to an INNER JOIN....
> IS the ANY thing else I can do to squeeze some performance out of this
> monster? I haven't run it through profiler yet, but that's the next
> obvious choice.
> The other half of the query is: is it possible to go too far with WITH
> (NOLOCK)? Or is what I've done reasonable?
> =chris
I think th eapproach you should reall ybe taking here is performance
tuning the SQL running in this long running batch. After you've
diagnosed all the SQL and you know things are running as fast as
possible, then start looking at hints as a possible way to improve
performance. As Hugo clearly demonstrates, not all business requirements
can tolerate a NOLOCK hint. Your business needs to decide if this is
tolerable or not.
Regarding your comment: "dumped #tempTables in favor of table variables
(more improvement)". I don't know what your temp/table vars look like,
but temp tables have some major performance advantages with larger data
sets because you can create indexes to support the queries run off the
tables. Also, if you're looping through the temp tables, pulling one row
at a time, use a SELECT TOP 1 to pull in the data for the row. If you're
joining to the temp tables or deleting from them, an index will likely
help.
David Gugick - SQL Server MVP
Quest Software|||Thanks David & Hugo... pretty much confirmed what I had already suspected. As
far as reading uncomitted data when using nolock, that's not a problem as the
data isn't updated until the very last step. I really wish I had the time to
go back and re-engineer the process properly in VB code rather than in SQL,
but it's a 5yr old process and if I change it now the account managers will
have a fit, and so will the client as they've been waiting long enough as it
is for this "to work" - it works as originaly built, but not like they want
it to be... ah, clients... where would we be w/o them. What I'm finding is
that 6 times out of 10 it's lightning fast... it's the 4 times that takes the
longest (the 4 times will take more time than the 6 did total.)
I'm not sure I could explain the process w/o giving any trade secrets (drat
those NDA's) or w/o making heads explode as I try to explain the industry, so
I won't bore people w/ the details.
Yes, idealy I wish I could do it in batches of select statement but the
business rules are getting in the way - I truly regret building this the way
I did 5 yrs ago... If I only knew then what I know now... but hindsight is
20/20 right?
At any rate, thanks for your help, I'm going to expore the possibility of
converting the process into VB code, and if I can do it in one day (the
project manager is out today) then I might attempt it. Otherwise, it'll just
have to go as it is for now.
Cheers,
Chris|||On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
>Thanks David & Hugo... pretty much confirmed what I had already suspected. As
>far as reading uncomitted data when using nolock, that's not a problem as the
>data isn't updated until the very last step.
Hi Chris,
Not by that process, it isn't. But are you equally sure that no other
users are accessing and changing the data at the same time?
(snip)
>At any rate, thanks for your help, I'm going to expore the possibility of
>converting the process into VB code, and if I can do it in one day (the
>project manager is out today) then I might attempt it. Otherwise, it'll just
>have to go as it is for now.
Good luck. And let us know if you need further assistance!
--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" wrote:
> On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
> >Thanks David & Hugo... pretty much confirmed what I had already suspected. As
> >far as reading uncomitted data when using nolock, that's not a problem as the
> >data isn't updated until the very last step.
> Hi Chris,
> Not by that process, it isn't. But are you equally sure that no other
> users are accessing and changing the data at the same time?
>
Positive... The nature of the data being manipulated, as well as standard
business practicess in the industry, practicaly require that only one person
is going to be accessing and updating the data at any given time - even if
the process wasn't being automated.
> (snip)
> >At any rate, thanks for your help, I'm going to expore the possibility of
> >converting the process into VB code, and if I can do it in one day (the
> >project manager is out today) then I might attempt it. Otherwise, it'll just
> >have to go as it is for now.
> Good luck. And let us know if you need further assistance!
> --
> Hugo Kornelis, SQL Server MVP
>
I was able to port much of the business logic over to VB, leaving SQL to
just select queries and two action queries.... there was "some" improvement,
but it still isn't where I need to it be... I found several cases where I
was running through some logic where I didn't need to and short-circuited it
on that condition. And then I find out (from the QA dept no less) that the
Proj Manager has decided to ship it as it is with a note that says we are
working on the performance issue. Which means I can now take my time to do
this right rather than slapping it together like I did. Will wonders never
cease.
-Chris
