Quick Check
Quick Check
|
1. |
Which caching method is easy to implement but allows you the most control in how it is implemented? |
|
|
2. |
What new feature with SQL Server 2005 enables you to receive notifications when the data in your cache has been updated? |
|
|
3. |
What are some commonly used alternatives to using a server-side cursor? |
|
|
4. |
Which two statements should be executed after all the rows in your cursor are
|
|
|
5. |
Which concurrency option should be avoided to minimize blocking? |
|
Answers
|
1. |
Control level-caching is easily implemented by adding the OutputCache directive to the top of the HTML. By using control level-caching as opposed to page level-caching, you have more control over what is cached in your Web page. |
|
2. |
Query notifications allow you to receive notifications when the data in your cache has been updated. You can then execute a refresh of the cache and ensure that you have the latest version of the data. |
|
3. |
Often, you can replace a cursor with a single SELECT statement. You can also consider using a WHILE loop, CASE function, and a recursive query. |
|
4. |
You should always include the CLOSE and DEALLOCATE statements after all the data in your cursor has been fetched. This will release all locks and also remove the cursor from server memory. |
|
5. |
The SCROLL_LOCKS option should be avoided, because it assumes updates might occur to the data before the cursor has been closed. Depending on the lock hints specified in the SELECT statement, locks might be issued on the underlying tables by using this option. |
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
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? |
|
Answers
|
|
It might not be necessary to replace all the
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
|
Similar products
- C# Programmer[ap]s Cookbook
Networkstream endwrite asynchronous operation exception - QuantumPM - Microsoft Office Project Server 2003 Unleashed
2003 epm architecture - MCSE. Windows Server 2003 Network Security Design Study Guide Exam 70-298
Windows 2003 server components&services architecture - Types and Programming Languages
Untyped arithmetic expressions and or