SQL Server Performance Forum – Threads Archive
SQL Performance problem need help
I have a sql problem. suppose we have two tables ex. A and B which have A1,A2,A3 column and B has B1,B2,B3 Column A1 and B1 are primary key and B2 is foreign key of A(A1) Now B3 has some values in it which i wanted to map with column A3. means i wanted to update column A3 as same as B3 where we have A1=B2 relationship. can we write this in a single sql statement. what i am doing is writing a cursor on table A which one by one retrieve value from table B(B3) and update table A(A3). But problem is that this process is very slow and lock the table at the time it perform operation so all other request will have to wait until this process terminate. otherwise some times sqlserver detects it as a deadlock condition and unconditionally terminate process. So my aim is improve performace of all such processes. Table has bulk amount of data i. e. around 10-15 lacks records. I need some alternative solution for many of such process in which i have used same logic am working on. also the database is of prouction server so I cannot kept it busy for long time. Error :Transaction (Process ID 773) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. I wanted some thing like as easy as update A set A.a3= B.b3 where A.a1 =B.b2 which does not work in this case.
Pallav Deshmukh
Why doesn’t
update A set A.a3= B.b3 where A.a1 =B.b2
work? It should do but will probably take too long. you could do it in batches using the PK on A declare @pka int, @pkamax int select @pka = 0, @pkamax = max(A1) from A
while @pka < @pkamax
begin
update A set a3= B.b3 where A.a1 =B.b2 and A.A1 between @pka and @pka + 1000
select @pka = @pka + 1000+1
end you will need to loop on the pk depending on the datatype and distribution.
Nigel, it looks like you forgot something [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><pre id="code"><font face="courier" size="2" id="code"><br />declare @pka int, @pkamax int<br /><br />select @pka = min(a1), @pkamax = max(A1) from A<br /><br />while @pka <= @pkamax<br />begin<br /> update A <br /> set a3= B.b3<br /> from A<br /> join B on A.a1 =B.b2<br /> where A.A1 between @pka and @pka + 999<br /><br /> select @pka = @pka + 1000<br />end<br /></font id="code"></pre id="code"><br />fixed. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]
I am not having good experience on sql update A set a3=B.b3 from A INNER JOIN B on A.a1=B.b2 works nicely Thanks for help. Pallav Deshmukh
]]>