| Author |
Topic |
|
gison
Starting Member
5 Posts |
 Posted - 2009-12-30 : 22:56:08
|
| Dear all,I got a very serious performance while I want to update a field which has more than 40,000,000 records. If anyone can have a quick glance would be thankful.Following is my SQL statement==================================UPDATE dbo.[TEST$GL Entry]SET [Register No] = R.NoFROM dbo.[TEST$GL Register] R, dbo.[TEST$GL Entry] GLWHERE R.[From Entry No]<= GL.[Entry No] AND R.[To Entry No] >= GL.[Entry No]==================================PS.[TEST$GL Register]have more than 5,000,000 records [TEST$GL Entry] have more than 40,000,000 records |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2009-12-31 : 02:29:44
|
| UPDATE GL SET [GL.Register No]=R.No FROM dbo.[TEST$GL Register]as RINNER JOIN dbo.[TEST$GL Entry] as GL ON R.[To Entry No] >= GL.[Entry No] AND R.[From Entry No]<= GL.[Entry No] |
 |
|
|
gison
Starting Member
5 Posts |
Posted - 2009-12-31 : 03:06:14
|
| dears, finally I do it this way to enhance performance.Please have a reference.==============================declare @register_No intdeclare @entry_No_from intdeclare @entry_No_to intset @register_no = 1while (@register_no <= 4554570)begin select @entry_No_from =r.[FROM Entry No_] ,@entry_NO_to = r.[To Entry No_] from dbo.[TEST$GL Register] r where r.No = @register_no UPDATE dbo.[TEST$GL Entry] SET [Register No] = @register_no where dbo.[TEST$GL Entry].[Entry No] between @entry_No_from and @entry_No_to set @register_no = @register_no +1 |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2009-12-31 : 08:19:43
|
| So, a while loop performed better than a set based operation?Hard to believe...http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2009-12-31 : 08:32:56
|
| It may just be a case of it looking at fewer lines, he has the condition 'while (@register_no <= 4554570)', 4.5 M records instead of 40 M, if that was set in other suggested queries it may perform better. |
|
|
|
gison
Starting Member
5 Posts |
Posted - 2010-01-04 : 04:38:09
|
| Actually, these two ways all need to cost many hoursI'm still figuring out how to fix the problem |
|
|
|
dhilditch
Starting Member
2 Posts |
Posted - 2010-01-04 : 11:46:02
|
| Hi - a few things to try:UPDATE GLSET [GL.Register No]=R.No FROM dbo.[TEST$GL Register]as RINNER JOIN dbo.[TEST$GL Entry] as GL ONGL.[Entry No] between R.[From Entry No] and R.[To Entry No] -- using the between potentially gives an additional hint to the optimiser - it can't handle using an index on two range queries when the ranges are on different columns - should be able to use the index on [entry no] (clustered index on that one?)where [GL.Register No] <> R.No -- no functionality difference but avoiding updates when you don't HAVE to do them will help - e.g. any transactional replication will not have to kick in, any triggers will not be affected, index updates won't have as much work to do etcCan't help much more than that until you post indexes and ideally the execution plans being used.www.skyscanner.net |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Update statement performance issue