SQL Server Performance Forum – Threads Archive

How to speed up SQL Server performance?

We have applications that can run on both Oracle and SQL Server. (through ODBC connections) Because we need to supply ‘Undo and Redo’ function, we are actually generating database updating script for each record involved. sometimes the function could involve more than 10,000 sql statements in one transaction. Because SQL Server’s locking policy not allow reads while doing updating, this is causing blocking very offten. (On oracle this is not happening) Since the undo & redo functionality, we couldn’t use stored procedures to speed things up. If we try to split the large transaction into small ones, How can we keep the data compelete if any of the small transaction fails? Thanks in advance.

---

Any reason why undo & redo function are used while data updation?
You will gain by keeping the transactions in batches and manage consecutive batches to rollback if former one is failed. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

can you provide a small version of your code, perhaps of 10 sql statements or there abouts

---

Thanks, Can you please make an example how should I arrange the transaction? I put some example of our database updating procedures.
How can I make sql server release the exclusive locks before the last commit statement?

---

One option is to create a select statement with NOLOCK on it but this will result in reading dirty data so you can read rows thats in an uncommitted transaction. If this is ok it should solve your problem of performance hinder from locks. My Oracle knowledge isn’t that great but I believe Oracle solves this solution by not displaying records that are in the uncommitted phase.

---

http://support.microsoft.com/default.aspx?scid=kb;EN-US;295108 some relevance to the issue. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Thanks, we would like to try to allow SQL Server reads dirty pages, at least this will avoid the reading blocked by updates.
yes, for Oracle, there is no problem, as it always read from the imaging data.

---

]]>