SQLPass 2011 Notes

01/22/2011 – SQLPass training at UofL in louisville Kentucky

Live notes from sessions

 

Session 1 - SQL Query performance optimization

Use Set Statistics ON to get feedback on the efficiency of your statements. Provides results, execution plan, and message that shows efficiency of the statement.

Thumb rule - whenever possible, do not use functions on select statements, far more 'busy'

BAD:

SELECT ModifiedDate

FROM SALES.Customer c

WHERE YEAR(ModifiedDate) = 2005

GOOD

SELECT ModifiedDate

FROM Sales.Customer c

WHERE ModifiedDate >= '01/01/2005' AND ModifiedDate < '01/01/2006'

Parameter Sniffing

Local variables are expensive – compiler has to make assumptions about values when creating the plan and account for possibilities.

Instead, use stored procedures with parameters. Compilation uses the actual values when compiling.

NOTE: stored proc execution plan compiles for the values present when compiled. If you rerun with new values, this requires a new plan and may be more expensive.

Action: Check out Cross Apply action.

Better Data Vis with Reporting Svcs 2008 Rw

Presenter Arie Jones arie.jones@pti.net

www.programmersedge.com

www.sqlsherpa.com

www.twitter.com/programmersedge

Check out MongoDB video on YouTube

AJ’s 10 second rule – Data vis needs to be understandable within 10 seconds of viewing the report

Image Indicators

SQL 2008 R2 includes indicators as part of the available tool elements.

When setting ranges for kpi’s, start is >=, end is <

KPI’s include all the conditiional formatting capabilities of other report elements

Custom images can be imported and used in reports (company logo)

Data Bars

Show a bar-chart style representation of values

To show multiple styles of representation based on user preference, add multiple elements and use visibility property and show / hide based on a report parameter. Can even store user preference in db and pull back into reports using a 2nd dataset for the report.

Sparklines

Show trending over time

Can use tooltips for datapoints to display the values at specific points.

Switching between sparkline types maintains tooltips, conditional expressions, etc.

Spatial Data

Includes mappings by state, by county, checkbox for bing map linking

Metadata is included in the maps so the geolocation is not needed

Running in Windows7, you have to run dev environment as Administrator

New Features

Pagination improvements

Named Excel Worksheet Tabs

Text rotation up to 270 deg

Cache refresh plans

New Web Svc endpoints

Azure data retrieval

<fill additional feature list>

 

Database Design Considerations

• Gather requirements
• Initial design
  • Tables, Columns, Relationships, Constraints
• Normal Forms <investigate>
• Atomicity – break data down until it cannot be split further
  • Store data at the level you want to use it to report
• To be in 1NF, some uniqueness needs to be on a column in the table that has meaning
• FName,Lname,Children not 1NF – Custnum,FName,LName good
• Investiage Boyce-Codd normal Forms

Real World Analytics

• Ranking Data
• Recursive expressions
• Rows to columns / crosstab or pivot
• Use DateDiff() to programmable start / end dates rather than using functions – functions cannot make use of table indexes and prompt full scans rather than seeks
• Check on Anchor queries, recursive CTE
• Mark Wills article Dynamic Pivot Procedure for SQL Server – http://e-e.com/A_653.html

Red Gate SQL Monitor

Presenter Louis Davidson – drsql@hotmail.com

• http://monitor.red-gate.com
• Does not offer remote agent pushing to collection server, CS has to pull from remote servers

Effective SQL: Understanding Order of Operation

Presenter Kevin C. Cross – Elliott Tool Technologies LTD

• PEMDAS
• Parenthesis
• Exponents
• Multiplication and Division
• Addition and Subtraction
• Additional
• Unary Operations
• Modulo
• Type Conversions / Collation
• Bitwise and logical operations

BAD:

SELECT *

FROM Products

WHERE CATEGORY = 1 OR CATEGORY = 2

AND PRICE <=40

The price <=40 is tied to the second Category, not as a second where clause

GOOD:

SELECT *

FROM PRODUCTS

WHERE (CATEGORY = 1 OR CATEGORY = 2)

AND PRICE <= 40

• To avoid integer division where 1/3 = 0, use mathematics in the query to cast one of the values to a float. This avoids the expensive use of dropping a formula on top of the column.

BAD: Select cast(column1) / column2

Good: SELECT column1 * 1.00 / column2

• Like data type conversions, collation will be applied to string before comparison (EXPENSIVE)
• Bitwise ops are equal to arithmetic (after multiplation / division)
• When joining, start with the table that contains the most data you’re using – use the least selective table
• Select precedence
• from
• where
• group by
• having
• select
• distinct
• union all
• order by
• top