Case Scenario: Evaluating Cursor Performance


 

In the this case scenario, you are a database developer for a large financial company that provides mortgages to people with poor credit ratings. For the past three years, the company has been utilizing a complex ASP.NET application that retrieves data from a SQL 2000 database. As the amount of data in the database increased, company employees have noticed that the application response time has decreased.

The company now intends to move the data to a SQL 2005 database. They would like for you to analyze the solution and recommend ways that the application can be improved. Upon reviewing the SQL code, you notice that a cursor is used in about half the stored procedures. What strategy might you recommend for improving database performance?

image from book

Answers

 

It might not be necessary to replace all the cursors in this poorly performing application. The best place to start is by identifying those areas of the application that are experiencing the largest delays. You could use SQL Server Profiler to record a trace while the application executes some of the slower functions. The trace recording can be used as input for the Database Engine Tuning Advisor, as an alternative to replacing the cursor. You might be able to resolve the application problems by simply applying a few indexes.

If the application performance continues to suffer, then you should consider replacing some of the cursors with set-based alternatives. You would start by identifying those cursors that take the longest to execute. For these long-running stored procedures, try to find a way to replace the cursor with one of the alternatives mentioned in Lesson 2, "Designing a Cursor Strategy." Compare the execution times for the alternative with the original cursor to determine if the change was successful. You might just have to do this procedure with a few of the cursors to achieve the desired performance results. There is a tradeoff to consider between development time and application performance.




MCITP Self-Paced Training Kit Exam 70-442  .Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
MCITP Self-Paced Training Kit Exam 70-442 .Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
ISBN: 073562383X
EAN: N/A
Year: 2007
Pages: 162

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net