problems running memory intensive queries

[rishi_israni]

Hi,
i am having a strange problem running memory intensive queries on SQL
server.

I am doing an update on a table with 9 million records from another
table
with 50 records.

the query i am running is

update table1
set var1 = b.var2
from table2 b
where key1=b.key1

this query hanges for ever. I had thought that there was a problem with
my machine...but once out of the blue it ran in 16 minutes.

I am running a 1 Ghz PIII with 512 MB of memory.
Any ideas as to what could be the issue ?

Regards
Rishi

[rishi]

forgot to mention...the size of the database is around 4 gb.
Cheers
Rishi

[Dave]

>From the limited info about your situation, I would say for starters
check to make sure you have proper indexes defined. Indexes are the key
to life in sql...

A good candidate for your indexes are the column in your where clause
and the column in your set clause....

-dave

[Erland Sommarskog]

(ri**********@yahoo.com) writes:

i am having a strange problem running memory intensive queries on SQL
server.

I am doing an update on a table with 9 million records from another
table with 50 records.

the query i am running is

update table1
set var1 = b.var2
from table2 b
where key1=b.key1

this query hanges for ever. I had thought that there was a problem with
my machine...but once out of the blue it ran in 16 minutes.

Does that UPDATE hit all nine million rows? In such case, it will
certainly take some time to execute the query. Things goes even worse
if the column you are updating is part of the clustered index. It gets
even worse if there is a trigger on the table.

For such huge updates, it's not an uncommon to run the update in batches.
In this case, maybe one update per key value could be an idea, at least
if there is an even distribution.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[rishi_israni]

hi,
I also thought that the update would take too long and had given up
....but the funny thing is that once it ran in 16 mins and now when i
run it again, it hangs indefinitey...any clues ?

[rishi_israni]

another point that i noted...the time when the query ran successfully
the task manager showed the cpu usage to be quite high and available
physical memory to be quite low...
but when it fails...the cpu usage is very low and the available memory
is also high ?

is that any kind of a sign ?

Rishi

[rishi_israni]

i created an index on the key column and tried again....but alas no
result again..
it hanged.....
This is the cancellation message i get..

Query cancelled by User
[Microsoft][ODBC SQL Server Driver]Operation canceled
[Microsoft][ODBC SQL Server Driver]Timeout expired
ODBC: Msg 0, Level 16, State 1
Communication link failure

Rishi

Connection Broken

[Erland Sommarskog]

(ri**********@yahoo.com) writes:

I also thought that the update would take too long and had given up
...but the funny thing is that once it ran in 16 mins and now when i
run it again, it hangs indefinitey...any clues ?

Clues? With almost no knowledge about your tables?

Please post the CREATE TABLE and CREATE INDEX statements for your
tables, including FOREIGN KEY and CHECK constraints. Please also post
the exact UPDATE statement you are having problem with.

That is no guarantee that I or anyone will be able to say anything useful,
but at least it improves the odds.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[rishi_israni]

hey the table create statement has 50 columns but i will list the most
important columns
there are no indexes on the table and no primary key

table name: DepTableFormat
rd_yr int,
vc_id int,
pc_nat_id int,
b_scno_vc int

the first 3 columns make up the structure of the table and account for
the 9 million rows.
then i take data from another table and join into the 4th column....

structure of the second table (BDataTable) is
rd_yr1 int,
b_scno_vc int

the query i run is

update DepTableFormat
set b_scno_vc = b.b_scno_vc
from BDataTable b
where rd_yr = b.rd_yr1

this will update all the 9 million records....

it takes forever,..... however i dont know how once it completed in 16
mins...
the transaction log has become bigger than my database also.
cheers
Rishi

[rishi_israni]

hi,
I found the solution to my problem. I re phrased the sql query
and it worked
from :
update DepTableFormat
set b_scno_vc = b.b_scno_vc
from BDataTable b
where rd_yr = b.rd_yr1
to :
update DepTableFormat
set b_scno_vc = (select b.b_scno_vc
from BDataTable b
where rd_yr = b.rd_yr1)

and viola query runs in16 mins updating 9 million records
any light on the reasons ?

cheeros
Rishi

[Gert-Jan Strik]

The answer is in the query plan (which we haven't seen)...

Note that the two queries are different. Query 1 will only update the
rows that have a matching row in table BDataTable. Query 2 will update
all rows in table DepTableFormat.

Gert-Jan
ri**********@yahoo.com wrote:

hi,
I found the solution to my problem. I re phrased the sql query
and it worked
from :
update DepTableFormat
set b_scno_vc = b.b_scno_vc
from BDataTable b
where rd_yr = b.rd_yr1
to :
update DepTableFormat
set b_scno_vc = (select b.b_scno_vc
from BDataTable b
where rd_yr = b.rd_yr1)

and viola query runs in16 mins updating 9 million records
any light on the reasons ?

cheeros
Rishi

[Erland Sommarskog]

(ri**********@yahoo.com) writes:

I found the solution to my problem. I re phrased the sql query
and it worked
from :
update DepTableFormat
set b_scno_vc = b.b_scno_vc
from BDataTable b
where rd_yr = b.rd_yr1
to :
update DepTableFormat
set b_scno_vc = (select b.b_scno_vc
from BDataTable b
where rd_yr = b.rd_yr1)

and viola query runs in16 mins updating 9 million records
any light on the reasons ?

No. You were asked to supply complete information about the tables, and
you didn't. That's your choice, but it is also my choice to refrain to
do guessworks on partial input.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp