Update Qry runs to long (never finishes, SQL2005)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Qry runs to long (never finishes, SQL2005)

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-09-29 : 06:13:26

Good day, i need help please.

I have a table in which i'm updateing 1million records from a table that contains 16million records is still updateing afer 3hrs (sql2005)

My join is on the same table with a different type, no lock & the server is running fine

My where and on clause is in the exactly same order as my clustered index (Type,Period,Item,Customer,Depot)

Update V_Main Set V_Main.IF_Advertising=coalesce(Q1.IF_Advertising,0)
From dbo.Temp_IForecast_Data V_Main ( NOLOCK )
left join dbo.Temp_IForecast_Data Q1( NOLOCK )
on Q1.IF_Type='COST_NETVAL_CALC'
and q1.IF_Period=V_Main.IF_Period
and q1.IF_Item=V_Main.IF_Item
and Q1.IF_Customer=V_Main.IF_Customer
and Q1.IF_Depot=V_Main.IF_Depot
Where V_Main.IF_Type='COST_BASE'

Please help

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-09-29 : 06:37:26

Can't seem to find anything wrong with the query other than the NOLOCK in an update statement which is HORRIBLE!

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-09-29 : 06:44:10

Thanks for helping

should i remove the No Lock even when i'm join on the same table
?

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-09-29 : 07:07:30

Thank You very much, It works nicely - did not know that (Nolock) on Update was not good.

I also removed the Left to an Inner join.

Thanks

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-09-29 : 07:17:58

If you don't know what the NOLOCK hint does then you should stop using it at once. It can cause severe problems and even corruption in your database if you're not using it right. I suggest you search this forum for NOLOCK and see what you find...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-29 : 08:40:29

quote:
Originally posted by Lumbago

If you don't know what the NOLOCK hint does then you should stop using it at once. It can cause severe problems and even corruption in your database if you're not using it right. I suggest you search this forum for NOLOCK and see what you find...

Hint -- it won't be pretty.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources