1094
Part VIII: Performance Tuning and Optimization
Make sure you consider the following points when designing your application for
performance:- Begin with a well-designed database schema: Start with a clean, simplifi ed schema
to reduce the number of unnecessary joins and reduce the amount of code used to
shuttle data from bucket to bucket. - Use effi cient set-based code, rather than slow iterative cursors or loops. However,
there is a tradeoff. Large set-based operations can cause locking and blocking, so
there must be a fi ne line between large and small sets when modifying data. - Use a good indexing strategy to eliminate unnecessary table scans and to speed
transactions.
To reduce the severity of a locking problem, do the following:
■ Evaluate and test using the read committed snapshot isolation level. Depending
on your error handling, application fl exibility, and hardware capabilities, snapshot
isolation can signifi cantly reduce concurrency contention.■ (^) Check the transaction-isolation level, and make sure it’s not any higher than
required.
■ (^) Make sure transactions begin and commit quickly. Move any code that isn’t neces-
sary to the transaction out of the transaction unless it is needed to ensure transac-
tional consistency.
■ If two procedures are deadlocking, make sure they lock the resource in the same
order. Review the objects that the deadlock occurs on.
Evaluating Database Concurrency Performance
It’s easy to build a database that doesn’t exhibit lock contention and concurrency issues
when tested with a handful of users. The real test is when several hundred users are all
updating orders.
Best Practice
Multiuser concurrency should be tested during the development process several times. Do not allow
your fi rst tests to be the ones the real users put on your production system.
Concurrency testing requires a concerted effort. At one level, it can involve everyone avail-
able running the same front-end form concurrently. A program that constantly simulates a
user viewing data and updating data is also useful. A good test is to run 20 instances of a
script that constantly hits the database and then let the test crew use the application.
c47.indd 1094c47.indd 1094 7/31/2012 10:23:34 AM7/31/2012 10:23:34 AM
http://www.it-ebooks.info