sql server programming: UPDATE - Lock \ Network Error -- sql server programming

UPDATE - Lock \ Network Error

MS User
9/19/2004 11:49:19 PM

sql server programming:

SQL 2K

I got a update statement (given below).

MyTable - 3 million rows
[commondata].[dbo].racurrencyconversionrates - 84 rows
Update is performing on a server with multiple databases, 'commondata' is a
different db.

The update is like this (@ Server - Myserver, db - Mydb)

update mytable
set currency_rate_cad = rate ,
bill_amt_usd = bill_amt_local,
bill_amt_cad = bill_amt_local /rate,
rate_usd = rate_local,
rate_cad = rate_local /rate
from [commondata].[dbo].racurrencyconversionrates
where
mytable.country = 'usa' and bill_amt_local >0 and
effectivedate = (select max(effectivedate) from
[commondata].[dbo].racurrencyconversionrates
where currencytype = 'cad' and effectivedate <= mytable.charge_date)

This update cause a LOCK to 'system' or 'sa' user with status 'BACKGROUND'
(Process Info screen ) .

I waited over 4 hrs but UPDATE did not complete and forced to kill the
process.

Sometime I get error

"[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken "

Now , I am updating this in a batch of 100000 and each set completing less
than 2 minutes.

Please help

Regards
MS

RE: UPDATE - Lock \ Network Error

Mike Epprecht (SQL MVP)
9/20/2004 4:39:02 AM

In this portion of the code:

"update mytable
set currency_rate_cad = rate ,
bill_amt_usd = bill_amt_local,
bill_amt_cad = bill_amt_local /rate,
rate_usd = rate_local,
rate_cad = rate_local /rate
from [commondata].[dbo].racurrencyconversionrates
where
mytable.country = 'usa' and bill_amt_local >0 and
"
you are not specifying the relationship between MyTable and
racurrencyconversionrates, (same as an outer juin), so your update affects 84
* 3'000'000 rows. Your expected result could be quite different to what you
want.

If you add something like " and mytable.country =
racurrencyconversionrates.country", then it does not perform an outer join,
but rather an inner join and will at most affect 3'000'000 rows.

Regards
Mike

[quoted text, click to view]

Re: UPDATE - Lock \ Network Error

MS User
9/20/2004 5:04:06 PM

Here is the structure of table racurrencyconversionrates

CurrencyType, Rate, EffectiveDate, EffectiveMonth, EffectiveYear,
ModDatetime, ModBy

All records in this table store ,column CurrencyType as 'CAD' . Sample data

CurrencyType Rate
EffectiveDate
------------ ----------------------------------------------------- ---------
---------------------------------------------
CAD 0.69379999999999997
1998-01-01 00:00:00.000
CAD 0.6976
1998-02-01 00:00:00.000
CAD 0.70579999999999998
1998-03-01 00:00:00.000
CAD 0.69940000000000002
1998-04-01 00:00:00.000
CAD 0.69189999999999996
1998-05-01 00:00:00.000

So there is NO common columns between mytable and racurrencyconversionrates.

Thanks for your suggestion.

MS

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.