SQL Server Performance Forum – Threads Archive

Can I have a better query for this

Hi, Below query deals with 2 tables Cash_Receipt and Tbl_Tln_Location. UPDATECash_Receipt
SETLocation_Cd= Tln.Target
FROMCash_Receipt
,Tbl_Tln_LocationTln
WHERECash_Receipt.Tmp_Company= Tln.Source This is taking around 6 hours to run. Can I have a better query for this?
Ashish Johri

---

I don’t see anything wrong with the query, you could use JOIN syntax which is better.
Also check how many indexes Cash_Receipt Table has got.

---

UPDATE Cash_Receipt cr
SET Location_Cd = Tln.Target
FROM Cash_Receipt cr inner join
Tbl_Tln_Location Tln
WHERE ON cr.Tmp_Company = Tln.Source
Do you have index on these tables ?
KH

---

Probably not any faster, just a rewrite for more standard SQL: UPDATE Cash_Receipt
SET Location_Cd = (SELECT Tln.Target
FROM Tbl_Tln_Location Tln
WHERE Tln.Source = Cash_Receipt.Tmp_Company); (I didn’t run this, so sorry if any errors) This would be consistent with having a unique index or constraint on Tbl_Tln_Location.Source column. If you have a gazillion records, updates can take a long time. Since it is no doubt a logged operation — how is your logging configured?

---

simonbaker4, IMO "more standard SQL" means using JOINs (like khtan’s version) rather than subqueries. Try updating multiple columns at the same time, from the same source table, and you’ll see why JOINs are preferable.

---

Adriaan, update with joins is sql server specific syntax, so Simon’s query is more standard. However, from performance point of view, as you already said, it is not better. Besides possible index design improvements, probably the process that uses this query can be improved (to avoid such long running query)

---

Adirran Update statistics and index rebuild will it help for this situation
Thank you & Regards
Jai

---

Adrian,
The query posted by simonbaker4 is the standard and preferred UPDATE syntax, IMHO. AFAIK, The UPDATE FROM is a T-SQL specific syntax and more error prone, becuase a JOIN might find more than one matching row and you cannot predict whcih matching rows values are used for updation. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

---

Roji & Mirko, I’m surprised that a FROM clause for an UPDATE query is specific to T-SQL. I think it is the clearest way of expressing the update, and the best option for multi-column updates (think importing data through staging tables).
For multiple matches, I would expect the subquery to return a more-or-less random value as well, without an error message. Garbage in, garbage out.[Silly me – in that case you would get a "subquery returned more than one value" error.] There was another thread recently where someone posted an Oracle multi-column update statement. This involved a mapping between column lists, sort of like it is in our INSERT INTO syntax, and joins expressed in a WHERE statement.

---

]]>