November 8, 2001 at 9:43 am
It appears that setting the isolation level to 'READ UNCOMMITTED' slows down our queries over isolation level 'READ COMMITTED'. Has anyone had similar experience or know why this might be happening?
Our application is OLTP and we have a reporting tool that sits on top of it. The reporting tool runs in 'READ UNCOMMITTED' mode becuase the numbers do not need to be exact, just a ball park. When the reporting tool is switched back to 'READ COMMITTED' the whole system works faster, however, we see more locking contention.
Thoughts?
- Brendan
November 8, 2001 at 9:55 am
Haven't seen much use of this option. Theoretically, it should speed things up. Are you sure the reporting tool is setting this for all queries? Have you run Profiler to watch the tool work?
Steve Jones
November 8, 2001 at 5:37 pm
I would expect it to be faster - but like Steve, I don't use it often! Profiling is a good idea. How long does the report (the query or queries) take to run? I usually only resort to no locking as a last resort. A common solution is to replicate it to another db, report from there.
Andy
Andy
November 9, 2001 at 6:00 am
The way I have been measuring the *performance* of the systems is the number of documents per hour that our system can process. A "document" is an image with a lot of business data surrounding it. My peformance needs are to process 15,000 documents per hour through a 'workflow' engine. We are close to that. The query time has always been sub-second, wether READ UNCOMMITTED is user or not, however, the system throughput slows down 20-30 percent when READ UNCOMMITTED is on. So instead of 15,000 per hour I go down to say 11,500 documents per hour.
Our application and database runs fine under low loads, it's when the everything gets cranking up that we notice the hit on READ UNCOMITTED. Always trade offs. Locking contention verse speed.
If I learn more I'll be sure to post it here.
Cheers.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply