Module 7 Designing Queries for Optimal Performance

1. Module 7 Designing Queries for Optimal Performance

2. Module Overview Considerations for Optimizing Queries for Performance Refactoring Cursors into Queries Extending Set-Based Operations

3. Lesson 1: Considerations for Optimizing Queries for Performance Overview of Query Logical Flow Using the Query Optimizer to Process Queries Guidelines for Building Efficient Queries Considerations for Creating User-Defined Functions Considerations for Using User-Defined Functions Considerations for Determining Temporary Storage Discussion: Optimizing a Query

4. Overview of Query Logical Flow Non-Aggregate Query Where Order By From and Join Select Result Set Aggregate Query Grouping and Aggregation Order By Having Result Set Result Set Rows

5. Using the Query Optimizer to Process Queries Query Query Plan Query Optimizer Database Schema

6. Guidelines for Building Efficient Queries Favor set-based logic over procedural or cursor logic ü Test query variations for performance ü Avoid query hints ü Use correlated subqueries to improve performance ü Avoid using a scalar user-defined function in the WHERE clause ü Use table-valued, user-defined functions as derived tables ü Avoid unnecessary GROUP BY columns; use a subquery instead ü Use CASE expressions to include variable logic in a query ü Divide joins into temporary tables when you query large tables ü

7. Considerations for Creating User-Defined Functions User-Defined Function SELECT FROM WHERE Identify the type of function to be used Qualify object names referenced by a function with the appropriate schema name Create each function to accomplish a single task Troubleshoot and test the function Consider relevant factors when indexing the results of the function

8. Considerations for Using User-Defined Functions Integrate the user-defined function into the query plan as a join Consider the balance between performance and maintainability User-Defined Function Avoid using a user-defined function if performance suffers tremendously

9. Considerations for Determining Temporary Storage To achieve optimal tempdb performance: • Set the recovery model of tempdb to SIMPLE • Allow for tempdb files to automatically grow • Set the file growth increment to a reasonable size • Preallocate space for all tempdb files • Create multiple files to maximize disk bandwidth • Make each data file of the same size • Load the tempdb database on a fast I/O subsystem • Consider transferring the tempdb database to a different subsystem or disk

10. Discussion: Optimizing a Query What is the primary consideration when handling repetitive tasks against a set of data? What will be the effect of having the tempdb database on the same disk or Logical Unit Number (LUN) as the transaction log file? Can disciplined code formatting and using naming standards improve query execution performance? Explain the benefits of disciplined code formatting and using naming standards.

11. Lesson 2: Refactoring Cursors into Queries Building a T-SQL Cursor Common Scenarios for Cursor-Based Operations Demonstration: How To Refactor a Cursor Discussion: Using Cursors Guidelines for Using Result Set-Based Operations Selecting Appropriate Server-Side Cursors Selecting Appropriate Client-Side Cursors

12. Building a T-SQL Cursor Use the DECLARE CURSOR statement to define the SELECT statement 2 Use the OPEN statement to execute the SELECT statement 3 Use the FETCH NEXT INTO statement to retrieve values from the next row 4 Issue the CLOSE and DEALLOCATE statements to close the cursor 5 Why Cursors Are Slow • Each FETCH in a cursor has the same performance as a SELECT statement • Cursors use large amounts of memory • Cursors can cause locking problems in the database • Cursors consume network bandwidth Declare the variables for the data to be returned by the cursor 1

13. Common Scenarios for Cursor-Based Operations *Constructing a dynamic cross-tab query requires using a cursor to build the columns for the dynamic SQL

14. Demonstration: How To Refactor a Cursor In this demonstration, you will see how to: Refactor a cursor

15. Discussion: Using Cursors • List some of the disadvantages of using a cursor. • What is the major issue with using a cursor in modern relational databases? • What kind of a problem is best solved by using a cursor? • Discuss your own experiences with cursors.

16. Guidelines for Using Result Set-Based Operations Use queries that affect groups of rows rather than one row at a time ü Minimize the use of conditional branches inside queries ü Avoid making inline calls to scalar UDF in large result sets ü Limit query cardinality as early as possible ü Use result sets instead of cursor-based processes to minimize I/O ü

17. Selecting Appropriate Server-Side Cursors Dynamic Cursor Forward-Only Cursor Static Cursor Keyset-Driven Cursor Server-Side Cursors

18. Selecting Appropriate Client-Side Cursors OLE DB ODBC ADO ADO.NET-SqlClient Client Data Access Libraries That Support Client-Side Cursors Considerations for Using Client-Side Cursors • Network latency.Client cursors use more network resources • Additional cursor types.Client cursors support only a limited functionality • Positioned updates.Client-side cursors will not reflect database changes until the changes are synchronized with the database • Memory usage.The client computer should have enough memory to handle the size of the entire result set

19. Lesson 3: Extending Set-Based Operations What Are Common Table Expressions? Comparing CTE with Other SQL Tuning Techniques Demonstration: How To Use a CTE Discussion: Using Common Table Expressions Demonstration: How To Perform Recursive Queries with CTE Discussion: Recursion with CTEs Introduction to Ranking Functions Demonstration: How To Use Ranking Functions To Rank Rows What Are PIVOT and UNPIVOT Operators? Demonstration: How To Use PIVOT and UNPIVOT Options To Convert Data

20. What Are Common Table Expressions? A CTE is a named temporary result set based on a regular SELECT query. The following table describes the syntax parameters for a CTE

21. Comparing CTE with Other SQL Tuning Techniques CTE vs Temporary Table • A CTE does not store data anywhere until you actually execute it whereas in a temporary table, the data is stored in the tempdb database • You must call a CTE immediately after stating whereas you can call a temporary table over and over again from within a statement • Compute, Order By (without a TOP), INTO, Option, FOR XML, and FOR BROWSE are all not allowed in CTE whereas these options are supported in a temporary table CTE vs Subquery • In the CTE, the result set will be evaluated just once when a query is executed whereas in a subquery the result set will be evaluated every time a query is executed

22. Demonstration: How To Use a CTE In this demonstration, you will see how to: Create and use a CTE

23. Discussion: Using Common Table Expressions • How does a CTE differ from a #Temp table? • Can you execute two or more queries against a CTE? • How does a CTE differ from a derived table? • Can you build indexes or constraints on a CTE?

24. Demonstration: How To Perform Recursive Queries with CTEs In this demonstration, you will see how to: Perform recursive queries with CTEs

25. Discussion: Recursion with CTEs • What is the maximum number of recursive levels in a common table expression (CTE)? • What is the default number of recursions in a recursive common table expression? • Assuming that each recursion adds only one row to the results, how many rows will be returned with OPTION MAXRECURSION(100)? Select an option from the following: • 99 • 100 • 101

26. Introduction to Ranking Functions Ranking functions return a ranking value for each row in a partition

27. Demonstration: How To Use Ranking Functions To Rank Rows In this demonstration, you will see how to: Use Ranking Functions to rank rows

28. What Are PIVOT and UNPIVOT Operators? PIVOT is used to generate crosstab queries in which values are converted to column headers. UNPIVOT is used to convert column headers to values. The following table describes the parameters in the PIVOT and UNPIVOT syntax.

29. Demonstration: How To Use PIVOT and UNPIVOT Options To Convert Data In this demonstration, you will see how to: Use PIVOT and UNPIVOT options to convert data

30. Lab 7: Designing Queries for Optimal Performance Exercise 1: Optimizing Query Performance Exercise 2: Refactoring Cursors into Queries Virtual machine NYC-SQL1 Administrator User name Password Pa$$w0rd Logon Information Estimated time: 60 minutes

31. Lab Scenario You are a lead database designer at QuantamCorp. You are working on the Human Resources Vacation and Sick Leave Enhancement (HR VASE) project that is designed to enhance the current HR system of your organization. This system is based on the QuantamCorp sample database in SQL Server 2008. The main goals of the HR VASE project are as follows: • Provide managers with current and historical information about employee vacation and sick leave. • Grant view rights to individual employees to view their vacation and sick leave balances. • Provide permission to selected employees in the HR department to view and update the vacation and sick leave details of employees. • Grant the HR manager with the view and update rights to all the data. You are working on a project to integrate HR VASE with an intranet site which is used to send email broadcast to external people. The details of email recipients are loaded from QuantamCorp HR VASE into the system named Baldwin2. Recently, a number of functions at Baldwin2 receive many complaints about the performance. You are assigned to help fine tune the performance of the SQL used by those functions.

32. Module Review and Takeaways Review Questions Real-World Issues and Scenarios