Restricting resource usage by one app | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Restricting resource usage by one app

Hi, I have an application that takes 10’s of thousands of various record formats transforms them into a common format and uses JDBC to batch insert or batch update and other strategies to load these records into the database. It does this every hour. The trouble is that this is so intensive that the GUI application that allows users to view the records slows to a crawl when the transformation and loading is going on. Are there techniques to restrict the access to CPU, I/O etc. the user login that the transformation and loading application uses? I don’t care of the transformation and looding application takes twice as long every hour, so long as the GUI application gets access. I even added a delay in after each transaction (tried 5 and 10 ms) to see if this would allow SQL Server to service the SELECT queries performed by the GUI App. But that did not seem to make a difference. So advice from some SQL Server experts is most welcome on how to tackle this. I assume it cannot be an uncommon problem to solve. Thx. David Still learnin’ after all these years
– If you are not using stored procedures or triggers, turn off autocommit. All transaction levels operate faster with autocommit turned off, and doing this means you must code commits. Coding commits while leaving autocommit on will result in extra commits being done for every db operation.
– Turn off autocommit, but don’t leave transactions open for too long.
– Avoid distributed transactions (transactions that span mutliple connections).
– Follow standard JDBC optimizations: use connection pools; prefer stored procedures or direct SQL; use type 4 drivers; remove extra columns from the result set; use prepared statements when practical; have your DBA tune the query; choose the appropriate transaction levels. Take help of this articlehttp://vyaskn.tripod.com/analyzing_profiler_output.htm in analyzing the performance issues on slow running queries. Take help of these 2 KBAs which explains troubleshooting performance:
http://support.microsoft.com/Default.aspx?id=298475
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587 Ensure the latest service packs are applied to SQL & JDBC drivers. 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.
Hi, Well my application does all of the things you suggest, no autocommit, short transactions, no distributed transactions, PreparedStatements for everything. I have latest Service Packs and JDBC driver. I am going to ask the developers of the GUI app (in another country) to use ‘WITH NOLOCK’ hint. I might try the ‘USE ROWLOCK’ with the few UPDATE queries I run, see if that improves things. I’ll have a read of profiler article. But since my application hits 1500 records per second when doing batch inserts (batch size 10) on a single CPU test Db it’s performance seems fine. Probably hints I can push the GUI app developers way will help. Thx. David
Still learnin’ after all these years
Hi ya, also make sure that if your database uses char and varchar columns rather than nchar and nverchar, then that the jdbc driver is set to NOT send unicode as this would severly increase the amount of cpu time used by the batch process Cheers
Twan
]]>