Hi There,
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!!!
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;
An index on [all_sales].sales_date is built successfully.
A composite index on ([date_map].start_date, [date_map].end_date) is
built successfully.
My server config is:
SQL Server 2000 with Service Pack 3
Windows 2000 with Service Pack 4
DELL PowerEdge 6650 Server
DUAL XEON 1900MHz Processors
2G RAM
2G Page File on Drive C
2G Page File on Drive D
DELL Diagnostics on all SCSI harddisks were all PASSED.
Any experts could simly give me a help????
Thanks x 1,000,000,000 4 18184
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!
[posted and mailed, please reply in news]
Karaoke Prince (ka************ @hotmail.com) writes: 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!!!
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.
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;
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, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>... [posted and mailed, please reply in news]
Karaoke Prince (ka************ @hotmail.com) writes: 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!!!
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.
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;
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.
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:))
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" <we********@mai l.dk> wrote in message
news:68******** *************** ***@posting.goo gle.com... Erland Sommarskog <so****@algonet .se> wrote in message
news:<Xn******* *************** @127.0.0.1>... [posted and mailed, please reply in news]
Karaoke Prince (ka************ @hotmail.com) writes: 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!!!
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.
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;
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.
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:)) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: ptouch |
last post by:
Hi,
I am using Python 2.3.4 with Windows 2000. It is a Pentium Xeon
2GHz with 1GB of RAM.
As the title says, importing most modules takes about 2 to 3 seconds.
Also, starting the interpreter takes 2-3 seconds (I think at the time it
tries to import site)
The variable sys.path seems like the default compiled-in one.
|
by: serge calderara |
last post by:
Dear all,
I have a simple class in my application whcih create
dynamically textbox control to be place on a form
When I execute the code :
log(time)
my control = new myclass()
log (time)
|
by: JJ |
last post by:
Hi,
I'm having a little problem. I use the following code to open a .pdf file
from one app. :
System.Diagnostics.ProcessStartInfo psi = new
System.Diagnostics.ProcessStartInfo();
string path =
@dir+"\\"+myDataTable.Rows.ToString();
|
by: mike |
last post by:
Hi,
Starting a debug session used to take about 2-3 seconds.
I took the project home and worked on my laptop at home,
burned it onto CD then brought it back to work. Now it
takes 15-20 seconds to start a debug session.
Anyone have any ideas on what could've caused this. (By
the way, I already went in and removed the "read-only"
status of all folders/files that resulted from the CD.)
|
by: Brian |
last post by:
Hello,
I have a vb.net web app that I am doing reads and adds to a database.
Everything works fine and happens instantly. I recently added an update and
it takes a long time to finish. I was wondering if there was anyone else who
experenced this problem and if there are any ways around it?
Dim MySQL As String
MySQL = "UPDATE db SET Var1 = 'Y' WHERE aimtno = " & tnum & "
and abook = " & tbook
Dim strConn As String =...
| |
by: William Sullivan |
last post by:
I've got a website that may, on occasion, display a large list of items in a
bulletedlist control. On the client side, it takes about 4 seconds to get a
response that weighs in at over 1mb. It takes less than a second to display
those nodes. That part is fine
The problem is that when the user causes a postback, the browser appears to
lock up for anywhere between 10-20 seconds, at which point it renders the
response from the server.
...
|
by: John Smith |
last post by:
Hello, I have 7 different crystal reports that need to be collated.
Since I want to end up with a page of each (which all together make a
single report), I created a blank main report and then added the 7 as
subreports so they can print one after the other. I'm supposed to be
printing them from a VB.Net 2003 application and I will be passing 2
parameters (start and end dates). These reports are huge (over 1000+
pages each).
My original...
|
by: vpai |
last post by:
We have a package in production which has suddenly started consuming greater than normal time to execute. On debugging this further, we found the following:
The package takes 5 mins to come out.
The queries inside the package, if run seperately, come out in milliseconds in total.
On looking through the sql trace when the package was run, it was found that an insert on a global Temp table is consuming around 5 mins (elapsed = 310 secs). ...
|
by: Jason Huang |
last post by:
Hi,
I am wondering why it takes a long time to open an .aspx file at the first
time in a browser.
Are there some methods to improve that speed? Given that we are using the
IIS5+VS2005 C#.
Thanks for help.
Jason
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |