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. 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. 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. Pallav Deshmukh
-
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. 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. 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. Pallav Deshmukh
-
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. 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. 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. Pallav Deshmukh
Something like this:
UPDATE A
SET A3 = B.B3
FROM A
INNER JOIN B ON A.A1 = B.B2
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
something like this ??? update a set a.a3 = (SELECT top 1 b.b3 FROM b INNER JOIN a ON a.a1 = b.b1) My second computer is your linux box.
Thanks Colin Angus Mackay & woudwijk I am not having good experience of database thanks for help it works. Pallav Deshmukh
-
Something like this:
UPDATE A
SET A3 = B.B3
FROM A
INNER JOIN B ON A.A1 = B.B2
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
Thanks Colin Angus Mackay & woudwijk I am not having good experience of SQL. thanks for help!! it works. Pallav Deshmukh