no text exists for this slide

Efficient SQL Tips
Efficient SQL Tips
Importing/Exporting
Performance
Best Practices

Database Architect
Database Architect
Oracle and SQL Server DBA
Former Software Developer
Current Position:
Manager of Database Engineering
Policy Studies, Inc. - Denver CO

NOT FUN
NOT FUN
   SELECT *, Products.ProductID AS Expr1, Products.ProductName AS Expr2, Products.SupplierID AS Expr3, Products.CategoryID AS Expr4, [Order Details].ProductID AS Expr5, [Order Details].UnitPrice AS Expr6, [Order Details].Quantity AS Expr7, [Order Details].Discount AS Expr8, Orders.CustomerID AS Expr9, Orders.EmployeeID AS Expr10, Orders.OrderDate AS Expr11, Orders.*, [Order Details].OrderID AS Expr12, Products.* FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID INNER JOIN Orders ON [Order Details].OrderID = Orders.OrderID

Much Better
Much Better
SELECT p.ProductID, p.ProductName, p.SupplierID, p.CategoryID, o.ProductID, o.UnitPrice, o.Quantity, o.Discount, o.CustomerID, o.EmployeeID, o.OrderDate, od.OrderID
FROM Products as p
INNER JOIN [Order Details] as od
   ON p.ProductID = od.ProductID
INNER JOIN Orders as o
   ON od.OrderID = o.OrderID

Standard examples:
Standard examples:
Naming conventions
Installation procedures
Server and database configurations
Storage configurations
Make a standard and stick with it

Do’s and Don’ts
Do’s and Don’ts
Use caution:
Cursors
IN Predicate
UNIONS
Sorting
Triggers
Prefer joins over nested queries
Use temporary objects where appropriate
Use most restrictive predicates first

Take the time to understand Indexing
Take the time to understand Indexing
Always view execution plans
Never assume what the database engine will do or what the plan will look like
Always realize that there is more than one way to get to the same results
Sometimes this decision can make or break an application

Both Oracle and SQL Server provide many ways to tackle import/export needs
Both Oracle and SQL Server provide many ways to tackle import/export needs
Trick is to use the right one at the right time
Example
Bulk Insert
BCP
OPENROWSET –create Excel file from SQL query

no text exists for this slide

How do CF applications interact with the database?
How do CF applications interact with the database?
It is important to understand both sides of the fence.
When are transactions opened and committed?
Tips:
Remove unnecessary trips to the database
Understand how execution plans are created

Cached Queries
Cached Queries
Query of Queries
Prepared Statements

Prepared Statements
Prepared Statements
Use <cfqueryparam> tag to specify a bind parameter
<cfqueryparam cfsqltype="" value="">
When SQL statement is processed the database engine parses the SQL to make an execution plan
If SQL is same then it can use the execution plan from cache making it much faster

no text exists for this slide

Your every day tool
Your every day tool
Provides outline of execution
Returns valuable information for performance
Display Estimated Execution Plan
Does not take into consideration current operation on the server
Should produce similar results   
Does not work with temp tables
Run and Get Execution Plan
Will return the actual plan used

Tips on reading plans
Tips on reading plans
Each piece represents a separate process or step that the engine performed or will perform
Each of the execution plan steps are often broken down into smaller sub-steps.
View the steps from right to left.
Each of the sub-steps and steps are connected by an arrow that shows the path taken
Thickness of arrow is related to cost
Move your cursor above any of the steps or sub-steps for more detailed information about this particular step or sub-step.
Text in red means that statistics are missing for related table

Table Scan
Table Scan
There was no index used to look up the results
Each row in the table had to be examined
If a table is relatively small, table scans can be very fast, sometimes faster than using an index
When you see that a table scan has been performed, see how many rows there are in the table

Clustered Index Scan
Clustered Index Scan
A clustered index scan is like a table scan, except that it is done on a table that has a clustered index.
Clustered index scans occur for two different reasons.
There may be too many rows to retrieve, relative to the total number of rows in the table. See the "Estimated Row Count" to verify this.
Second, it may be due to the column queried in the WHERE clause may not be selective enough.
Generally, the only thing you can do to change a clustered index scan to a clustered index seek is to rewrite the query so that it is more restrictive and fewer rows are returned.

Clustered Index Seek
Clustered Index Seek
Performance is very quick
This is the fastest type of index lookup SQL Server can do
Think of a phone book
Is it faster to read through the entire book starting on page 1 when looking for something beginning with M?
Or is it faster to go to the M’s first?
Then imagine looking up hundreds of names at a time starting at page 1 each time

Index Seek & Index Scan
Index Seek & Index Scan
The query optimizer used a non-clustered index on the table to look up the results
The Where Clause and Joins are major factors in Index Usage
Scan:
Optimizer read all rows in index
Seek:
Optimizer found exact location of the row
Performance is generally very quick, especially when few rows are returned

Table Joins
Table Joins
In most cases, the query optimizer will analyze joins and JOIN the tables using the most efficient join type, and in the most efficient order
Table Join Types:
Nested loop
Hash
Merge
JOIN hint to change JOIN type may boost or hinder performance

Some Warning signs
Some Warning signs
Index or table scans:
May indicate a need for better or additional indexes.
Bookmark Lookups:
Consider changing the current clustered index, consider using a covering index, limit the number of columns in the SELECT statement.
Filter:
Remove any functions in the WHERE clause, don't include Views in your Transact-SQL code, may need additional indexes.
Sort:
Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently?

More Tips on reading plans
More Tips on reading plans
View the steps from right to left
Execution Plans are not always 100% accurate
The steps are accurate, but the cost information is not always accurate
Bugs can cause execution plan issues
Always be on most recent Service Pack
Icons and step definitions:
http://msdn2.microsoft.com/en-us/library/ms175913.aspx

Execution plans can be shown in SQL Profiler
Execution plans can be shown in SQL Profiler
Events to Capture
Performance: Execution Plan
Performance: Show Plan All
Performance: Show Plan Statistics
Performance: Show Plan Text
Data Columns to Display
StartTime
Duration
TextData
CPU
Reads
Writes

EXAMPLE
EXAMPLE
PopulateAdvWorksExampleTabs.sql
Execplan.sql
PerformanceIssue.sql
Indexmaintenance.sql

no text exists for this slide

Overview of some major best practices
Overview of some major best practices
Always use Execution plan
Remember there are more than one way to do something
Use CFQueryParam
Consider Caching and Query of Queries
Use Caution with: Cursors, In, Sorting, and Unions
Always think Index
Index Foreign Keys
Use Foreign Keys

Overview of some major best practices
Overview of some major best practices
Don’t over normalize
Remember Its OK to not be fully normalized
Use NOCOUNT on procedures
Re-Index Indexes weekly
Defrag Indexes on a regular basis
Update Statistics regularly
Fix the problem not the symptom
Keep Multiple Transactions in One Transaction
Use at least Simple Error Handling (XACT_ABORT)
EXAMPLE
Xact_abort.sql

Overview of some major best practices
Overview of some major best practices
Consider Temp tables and Table Variables to avoid Locking static tables
Consider Locking Effects before you do something
Collect all info possible before fixing a problem
Consider Actual Query Cost rather just time that query runs
Get the low hanging Fruit
Read Execution Plans from Right to left
Use useful primary keys that are used in queries
Avoid Table Scans

SQL Server Profiler
SQL Server Profiler
The best way to understand how the database is used
Can be executed through Profiler or stored procedures
Tips
Don’t run from the server
Don’t trace to a table
Results can be saved to a table after collected
Limit the events traced
Always use the trace stop time
The Blackbox
What is it and what does it do?

no text exists for this slide