T
tdk
Guys
Quite a tough question. We are using .net 2 and the new
transactionscope class to execute a distributed transaction across two
SQL2005 servers from a webserver running windows 2003. The code in
test simply calls a stored proc on both sql servers that just inserts
a row into a test table and returns. The two stored procs are called
one on each SQLServer within the same distribtued transaction. It was
quite painful to get going but once we configured DTC properly it all
seems to work fine.
The problem now is performance. In our test harness we execute 2000
distributed transactions and it takes 30 times as long as if we run
them not within a distributed transaction e.g. just executing the
stored proc on each server outside of the .Net transactionscope.
I would expect there to be some kind of overhead because a transaction
must be stated on each server and at the end a commit protocol run
over the servers to decide if the transaction should be completed by
30 times as slow is ridiculous. I would have thought a 20% overhead
acceptable.
I had a quick read of some news groups and people do suggest that
there are problems with performance and I wondered if anyone had any
ideas on how to solve this?
The peudo code of what we are doing is below but I should stress the
code is fine its just a performance issue.
try {
using (ts = new transactionscope()) {
using(conn1 = new sqlconnection(SERVER1)) {
using (conn2 = new sqlconection(SERVER2)) {
conn1.execute(sp);
conn2.execute(sp);
}
}
ts.commit();
}
}
catch(Execption ex) {// transaction failed}
Quite a tough question. We are using .net 2 and the new
transactionscope class to execute a distributed transaction across two
SQL2005 servers from a webserver running windows 2003. The code in
test simply calls a stored proc on both sql servers that just inserts
a row into a test table and returns. The two stored procs are called
one on each SQLServer within the same distribtued transaction. It was
quite painful to get going but once we configured DTC properly it all
seems to work fine.
The problem now is performance. In our test harness we execute 2000
distributed transactions and it takes 30 times as long as if we run
them not within a distributed transaction e.g. just executing the
stored proc on each server outside of the .Net transactionscope.
I would expect there to be some kind of overhead because a transaction
must be stated on each server and at the end a commit protocol run
over the servers to decide if the transaction should be completed by
30 times as slow is ridiculous. I would have thought a 20% overhead
acceptable.
I had a quick read of some news groups and people do suggest that
there are problems with performance and I wondered if anyone had any
ideas on how to solve this?
The peudo code of what we are doing is below but I should stress the
code is fine its just a performance issue.
try {
using (ts = new transactionscope()) {
using(conn1 = new sqlconnection(SERVER1)) {
using (conn2 = new sqlconection(SERVER2)) {
conn1.execute(sp);
conn2.execute(sp);
}
}
ts.commit();
}
}
catch(Execption ex) {// transaction failed}