Beyond Basic SQL

From BriansWiki

Contents 1 Presenter 1.1 Nate Nelson 2 Agenda 3 SQL Standards 4 Getting what you want 4.1 Inner Joins 4.2 Outer Joins 4.3 Wild Cards 4.4 Common Functions 4.5 Group Aggregate Functions 5 Query Optimization 6 The Execution plan 6.1 Metrics: 7 Cached Queries 8 Nested Sub Queries 9 Correlated Sub Query

[edit] Presenter

[edit] Nate Nelson

[edit] Agenda

• SQL Standards
• Getting what you want
• Query optimizations
• Some advanced queries

[edit] SQL Standards

Find one that you like and use, the specifics of it are not all that important

• Avoid spaces in table names
• Use whole words
• Table names are plural
• Name Views with a descriptor like v for views
• For stored procedures use "usp" for user stored procedures
• Use case consistency – SQL keywords in Upper Mixed case in table and field names

[edit] Getting what you want

[edit] Inner Joins

Join like tables from multiple tables It can be explicit; using the INNER JOIN keywords or implicit with a where clause matching two table foreign keys,

[edit] Outer Joins

Can return all rows even if key values are not found in both tables Using left or Right keywords lets you specify which table has control or will only show those records that have a match or do not have a match,

[edit] Wild Cards

More function available than just A%

• % = match a string of 0 or more characters
• _= match any single character
• [xyz] match xyz once
• [a-z] match any character from a to z
• [‘asn] everything but asn

[edit] Common Functions

• AVG()
• MAX()
• MIN()
• SUM()
• COUNT()
• Perform quickly on all fields
• Can use more than once in a Query
• Must you alias if you are pulling a value out in CF

[edit] Group Aggregate Functions

• You can limit by using HAVING key words
• An aggregate function will perform on every item in the group
• Having comes after the GROUPBY in query
• Output queries can use groups too; they can also have nested output queries

[edit] Query Optimization

Two ways to optimize

• With code itself
• With ColdFusion

Put most restrictive element first when selecting where involves multiple columns. This cuts down on the size of the compare group. The EXISTS keyword

SELECT ALL USERS Where EXISTS (Select Title from SomeTitleDatabase ST where TitlePage =‘Something" AND ST.Title = A.Title)

Use a WHERE clause instead of a UNION if possible,

IF Statements should also list the most restrictive items first

[edit] The Execution plan

This is a utility that is part of Server Management studio that can show you the performance characteristics of a query.

[edit] Metrics:

• Access method - tells you how the record was accessed either by a table scan or an indexed lookup
• Index Type - you can only have one clustered index and it is add physically to the data base. By default, a primary key is always a clustered key and it is automatic, Foreign keys are non-clustered.

There are tools that can help you identify tables with foreign keys that are not indexed. Look for the zip file from the other database optimization session by same presenter Note: Indexing does add overhead on updates

[edit] Cached Queries

Specify number of queries Use CachedAfter and cachedBefore properties. Use cfQueryParam to increase caching (abstract values due to late binding of the values. Queries must have the same names and the same data source and the same sql. Has to have been within the specified before and after time spans. Use Query of Query, Use Prepared Statements

[edit] Nested Sub Queries

[edit] Correlated Sub Query

Nate at Xententia.com