I have a very standard UPDATE query wherein I am updating a column in
one table-1 with data from column in another table-2 using a INNER JOIN
- standard stuff.

Now, I have several identical table-1s which I am updating with the
identical SQL statements. When I run it on tables with around 700K
records it takes 3 mins. However, when I run it on tables with approx
1.5 Million records it does not finish even after 1 hour.

What could be causing this? How should I troubleshoot this? What could
be causing this?

Pls help.

Are the tables really identical? Could there be more indexes, or even
triggers? Are the in the same database, are the databases on the same server
instance.

First guess, it really sounds like indexing issues.

It would help us better assist you if you could include table DDL, the
UPDATE queries (or stored procedures). (For help with that refer to:
http://www.aspfaq.com/5006 ). Without this effort from you, we are just
playing guessing games.

Show quoteHide quote

Jay wrote:
Show quoteHide quoteCompare the execution plans between a query that runs fast, and one that
runs slow.  What are the differences?

Tracy,

A guess: the 700K-row table fits entirely in memory, but the 1.5M-row
table does not, and the query plan for the update has a nested loop join
or some other feature that results in the table's data pages being accessed
in a very scattered way.  If you are near the server, are you hearing the
disk drives make a lot of noise?

If this is the issue, there may be ways to get a query plan that accesses
the table more sequentially (with better indexing, or by putting the
relevant
columns of table 2 into a differently-indexed and narrower temporary table,
for example).  You could try (if feasible) breaking the update up into
smaller
chunks, too, which could help on its own or reduce the load having to do
with logging.

Steve Kass
Drew University

Tracy McKibben wrote:

Show quoteHide quote

Oops.  My apologies for replying to you in my previous message.

I should have replied to Jay!

SK

Tracy McKibben wrote:

Show quoteHide quote

Spreadsheet totals
Foreign Key may cause multilple cycles
Bulk Insert from Excel
Sort Numbers in Nvarchar As Numbers
How do I filter out unwanted rows by only one different column?