While profiling my application against SQL Server 2008 running against a JDBC Application most of the time consumed were by following
- COMMIT TRAN
- FETCH API_CURSOR & exec sp_cursorfetch
I cannot reduce the frequency of commits in my app – so is there any setting/configuration like increasing the redo log buffers or log file size that can provide relief ?
And should I look at avoiding server side cursors as it eats up most of the time?
But since most of those statements involve blobs probably batch returning it makes more sense.
Please advice.
Best Answer
Cursors may be the right solution to a given problem, but usually not in SQL Server. Try to use set-based solutions instead of an iterative one if at all posible. Worst case scenario, you can look at using an iterative SQL solution. I've done that before and ended up an order of magnitude faster than an equivalent cursor implementation.
You can return BLOBs from the DB in normal SQL statements so you don't need a cursor for that - granted, if your application can't handle the incoming data stream, you might be changing cursor waits for async_network_io waits (meaning SQL is done, but your app is too slow - Access does this all the time).
If you post more details about what your app is doing or more specifics, we might be able to get more focused advice.