Update Takes Long Time to Complete!?

**Ray Higdon** · Jul 20 '05, 01:41 AM

Re: Update Takes Long Time to Complete!?

Where is your clustered index? It might help to have it on sales date
(in this scenario, don't know if it fits the business use)

First of all make sure your statistics are up to date, you also want to
make sure the tables are not fragmented by reindexing if needed. (in
case you don't know, rebuilding the clustered index alone will
automatically rebuild all non-clustered indexes on that table)

I would try changing that composite index to two different non-clustered
indexes as the composite index could not be used in the second part of
your where condition. When you have an index on columns (a,b) you can
use the index when you say "where a = N" but the index will not be used
when you say "where b = N"

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

**Erland Sommarskog** · Jul 20 '05, 01:41 AM

Re: Update Takes Long Time to Complete!?

[posted and mailed, please reply in news]

Karaoke Prince (karaoke_prince @hotmail.com) writes:[color=blue]
> I have an update statement to update a field of a table (~15,000,000
> records). It took me around 3 hours to finish 2 weeks ago. After that
> no one touched the server and no configuration changed. Until
> yesterday, I re-ran it again and it took me more than 18hrs and still
> not yet finished!!![/color]

The first thing to check for is blocking. It might simply be that
some other process is holding a lock on the [all sales] table, preventing
you from continue. Use sp_who to check this. If you see a non-zero value
in the Blk column, this spid is blocking the spid on this line.

The second thing to check for is triggers. A trigger on a 15-million-
row table could be fatal.

Not that these are the most likely reasons, but they are easy to check.
[color=blue]
> What's wrong with it? I can ran it successfully before. I have tried
> two times but the result was still the same.
>
> My SQL statement is:
>
> update [all_sales] a
> set a.accounting_mo nth = b.accounting_mo nth
> from date_map b
> where a.sales_date >= b.start_date and a.sales_date < b.end_date;[/color]

If that is your SQL, you are not running SQL Server, but some other
product. When I run the above, I get a syntax error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'a'.

In lieu of other information, I have to assume that the real query is

update [all_sales]
set accounting_mont h = b.accounting_mo nth
from [all sales] a, date_map b
where a.sales_date >= b.start_date and a.sales_date < b.end_date;

I would recommend that you add this condition to this query:

and a.accounting_mo nth <> b.accounting_mo nth

This may or may not improve the query plan, but at least it reduces
the number of rows. (Since you ran this statement a couple of
months ago, I guess that most rows already have the correct
accounting_mont h.)

Note that the query is such that SQL Server will have to read all rows
in the table. (I assume that all values of [all sales].sales_date
are covered by date_map.)

If there were clustered indexes on a.sales_date and b.start_date,
SQL Server could use a merge join. But this would only give a minor
improvement over the case where there are no indexes at all, in
which case I would expect SQL Server to hash date_map (I guess
this table has < 1000 rows).

Really, what is going on I don't know, but if you have a powerful machine,
SQL Server may be doing some sort of a loop join, invoking parallellism.

You could type the query into Query Analyzer, and then press Ctrl-L to
see the estimated execution plan. If the plan involves loop join, I
would try this:

update [all_sales]
set accounting_mont h = b.accounting_mo nth
from [all sales] a
inner hash join date_map b
on a.sales_date >= b.start_date and a.sales_date < b.end_date
where a.accounting_mo nth <> b.accounting_mo nth

SQL Server will issue a warning when you use join hint, but as long as
the query executes well, that's OK.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**Bjarke Wedemeijer** · Jul 20 '05, 01:45 AM

Re: Update Takes Long Time to Complete!?

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns940AEA A8A63DEYazorman @127.0.0.1>...[color=blue]
> [posted and mailed, please reply in news]
>
> Karaoke Prince (karaoke_prince @hotmail.com) writes:[color=green]
> > I have an update statement to update a field of a table (~15,000,000
> > records). It took me around 3 hours to finish 2 weeks ago. After that
> > no one touched the server and no configuration changed. Until
> > yesterday, I re-ran it again and it took me more than 18hrs and still
> > not yet finished!!![/color]
>
> The first thing to check for is blocking. It might simply be that
> some other process is holding a lock on the [all sales] table, preventing
> you from continue. Use sp_who to check this. If you see a non-zero value
> in the Blk column, this spid is blocking the spid on this line.
>
> The second thing to check for is triggers. A trigger on a 15-million-
> row table could be fatal.
>
> Not that these are the most likely reasons, but they are easy to check.
>[color=green]
> > What's wrong with it? I can ran it successfully before. I have tried
> > two times but the result was still the same.
> >
> > My SQL statement is:
> >
> > update [all_sales] a
> > set a.accounting_mo nth = b.accounting_mo nth
> > from date_map b
> > where a.sales_date >= b.start_date and a.sales_date < b.end_date;[/color]
>
> If that is your SQL, you are not running SQL Server, but some other
> product. When I run the above, I get a syntax error:
>
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'a'.
>
> In lieu of other information, I have to assume that the real query is
>
> update [all_sales]
> set accounting_mont h = b.accounting_mo nth
> from [all sales] a, date_map b
> where a.sales_date >= b.start_date and a.sales_date < b.end_date;
>
> I would recommend that you add this condition to this query:
>
> and a.accounting_mo nth <> b.accounting_mo nth
>
> This may or may not improve the query plan, but at least it reduces
> the number of rows. (Since you ran this statement a couple of
> months ago, I guess that most rows already have the correct
> accounting_mont h.)
>
> Note that the query is such that SQL Server will have to read all rows
> in the table. (I assume that all values of [all sales].sales_date
> are covered by date_map.)
>
> If there were clustered indexes on a.sales_date and b.start_date,
> SQL Server could use a merge join. But this would only give a minor
> improvement over the case where there are no indexes at all, in
> which case I would expect SQL Server to hash date_map (I guess
> this table has < 1000 rows).
>
> Really, what is going on I don't know, but if you have a powerful machine,
> SQL Server may be doing some sort of a loop join, invoking parallellism.
>
> You could type the query into Query Analyzer, and then press Ctrl-L to
> see the estimated execution plan. If the plan involves loop join, I
> would try this:
>
> update [all_sales]
> set accounting_mont h = b.accounting_mo nth
> from [all sales] a
> inner hash join date_map b
> on a.sales_date >= b.start_date and a.sales_date < b.end_date
> where a.accounting_mo nth <> b.accounting_mo nth
>
> SQL Server will issue a warning when you use join hint, but as long as
> the query executes well, that's OK.[/color]

Dear Erland -

these are just guesses..... If queries run for such a long time I
normally
use the stored procedure track_waitstats
see :(www.microsoft.com/australia/resources/ distribution_sl ides.ppt )

during the long run i sample the waits with the dbcc
sqlperf(waitsta ts)
and then I know exactly what SQL Server is waiting for, with that
knowledge one quickly can determine what is wrong.

In fact - in the oracle community the wait events have been along a
long
time - for several years this is also possible on SQL Server with the
dbcc sqlperf(waitsta ts), though on ehas to take into consideration
that
the waitstats on SQL Server are server-wide - one can not
(unfortuanality not yet) trace the wait events on a single user or
process.

For an exact description of waits and DBMS systems I also refer to
http//www.hotsos.com - one can always learn someting (good and bsad)
from oracle:))

**Mario** · Jul 20 '05, 01:48 AM

Re: Update Takes Long Time to Complete!?

Bjarke/Erland,

I did a presentation on this subject (in Lalandia, DK), early October. The
presenation and tools are on www.sqlinternals.com. I also added some tools:
an experimental dll that you can use to collect waitstats per user (and
query them, from an exe or from an xp). Only works on SP3(!)
I also added a little C-program (with source) which looks at the waitstats
of a particular spid. It accesses sqlserver memory directly through
'readprocessmem ory', so very low overhead and possible high sample counts
(which you can specify in ms).
The big problem is still documentation on those waitstats: things get more
interesting if you realy know what you are looking at.

--
regards,
Mario

http://www.sqlinternals.com

"Bjarke Wedemeijer" <wedemeijer@mai l.dk> wrote in message
news:68549693.0 310111158.3f2fb 3f7@posting.goo gle.com...[color=blue]
> Erland Sommarskog <sommar@algonet .se> wrote in message[/color]
news:<Xns940AEA A8A63DEYazorman @127.0.0.1>...[color=blue][color=green]
> > [posted and mailed, please reply in news]
> >
> > Karaoke Prince (karaoke_prince @hotmail.com) writes:[color=darkred]
> > > I have an update statement to update a field of a table (~15,000,000
> > > records). It took me around 3 hours to finish 2 weeks ago. After that
> > > no one touched the server and no configuration changed. Until
> > > yesterday, I re-ran it again and it took me more than 18hrs and still
> > > not yet finished!!![/color]
> >
> > The first thing to check for is blocking. It might simply be that
> > some other process is holding a lock on the [all sales] table,[/color][/color]
preventing[color=blue][color=green]
> > you from continue. Use sp_who to check this. If you see a non-zero value
> > in the Blk column, this spid is blocking the spid on this line.
> >
> > The second thing to check for is triggers. A trigger on a 15-million-
> > row table could be fatal.
> >
> > Not that these are the most likely reasons, but they are easy to check.
> >[color=darkred]
> > > What's wrong with it? I can ran it successfully before. I have tried
> > > two times but the result was still the same.
> > >
> > > My SQL statement is:
> > >
> > > update [all_sales] a
> > > set a.accounting_mo nth = b.accounting_mo nth
> > > from date_map b
> > > where a.sales_date >= b.start_date and a.sales_date < b.end_date;[/color]
> >
> > If that is your SQL, you are not running SQL Server, but some other
> > product. When I run the above, I get a syntax error:
> >
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near 'a'.
> >
> > In lieu of other information, I have to assume that the real query is
> >
> > update [all_sales]
> > set accounting_mont h = b.accounting_mo nth
> > from [all sales] a, date_map b
> > where a.sales_date >= b.start_date and a.sales_date < b.end_date;
> >
> > I would recommend that you add this condition to this query:
> >
> > and a.accounting_mo nth <> b.accounting_mo nth
> >
> > This may or may not improve the query plan, but at least it reduces
> > the number of rows. (Since you ran this statement a couple of
> > months ago, I guess that most rows already have the correct
> > accounting_mont h.)
> >
> > Note that the query is such that SQL Server will have to read all rows
> > in the table. (I assume that all values of [all sales].sales_date
> > are covered by date_map.)
> >
> > If there were clustered indexes on a.sales_date and b.start_date,
> > SQL Server could use a merge join. But this would only give a minor
> > improvement over the case where there are no indexes at all, in
> > which case I would expect SQL Server to hash date_map (I guess
> > this table has < 1000 rows).
> >
> > Really, what is going on I don't know, but if you have a powerful[/color][/color]
machine,[color=blue][color=green]
> > SQL Server may be doing some sort of a loop join, invoking parallellism.
> >
> > You could type the query into Query Analyzer, and then press Ctrl-L to
> > see the estimated execution plan. If the plan involves loop join, I
> > would try this:
> >
> > update [all_sales]
> > set accounting_mont h = b.accounting_mo nth
> > from [all sales] a
> > inner hash join date_map b
> > on a.sales_date >= b.start_date and a.sales_date < b.end_date
> > where a.accounting_mo nth <> b.accounting_mo nth
> >
> > SQL Server will issue a warning when you use join hint, but as long as
> > the query executes well, that's OK.[/color]
>
>
> Dear Erland -
>
> these are just guesses..... If queries run for such a long time I
> normally
> use the stored procedure track_waitstats
> see :(www.microsoft.com/australia/resources/ distribution_sl ides.ppt )
>
> during the long run i sample the waits with the dbcc
> sqlperf(waitsta ts)
> and then I know exactly what SQL Server is waiting for, with that
> knowledge one quickly can determine what is wrong.
>
>
> In fact - in the oracle community the wait events have been along a
> long
> time - for several years this is also possible on SQL Server with the
> dbcc sqlperf(waitsta ts), though on ehas to take into consideration
> that
> the waitstats on SQL Server are server-wide - one can not
> (unfortuanality not yet) trace the wait events on a single user or
> process.
>
> For an exact description of waits and DBMS systems I also refer to
> http//www.hotsos.com - one can always learn someting (good and bsad)
> from oracle:))[/color]

Update Takes Long Time to Complete!?

Update Takes Long Time to Complete!?

Comment

Comment

Comment

Comment