• Learn to Write Efficient SQL Statements

• Hands-on Exercises

Soaring Eagle presents "Advanced SQL Programming & Tuning for Adaptive Server Enterprise" DBAs, System Administrators, and Client Application Developers. Attendees will learn how Adaptive Server processes queries. Learn to identify problematic statements & queries in an application design before they are implemented saving time and money. Students will estimate query performance on paper, and use those estimates to identify implementation issues with queries.

   The instructors and authors of the materials bring years of general database systems experience to the attendees. All are experienced Adaptive Server professionals ready to add their insight to the classroom environment.

• Fundamentals of Query Processing
  Query Processing Steps, Possible Bottlenecks, Define the Basic Steps in Query Processing

• Updates
  Performance, Deferred Updates, Direct, Updates,
  In- Place Updates

• Union Queries
  Features, Restrictions,
  Showplan Output for Union, UNION Optimization

• BLOBs
  Text and Images, Writetext, Readtext, Updatetext, Network Packet Size

• Multi-Table Queries
  Join Processing, Optimization, Nested Iteration, Reformatting, Device Performance,
  Breaking Up large Queries, Self Joins, Outer Joins, Multi-Table Joins

• Single-Table Optimization
  Search Methods, Table Scan Optimization,
  Index Selection, Optimizer Selection Criteria, SARG Matching, Using Indexes, Clustered Indexes vs. NonCIustered Indexes, Queries with OR, Metadata Cache

• Subqueries
  Categorization, Expression, Quantified Predicate, Correlated Subqueries, Processing and Flattening, Use with Any, All, In, Not In, Exists, & Not Exists

• Stored Procedures
  Coding Standards and Conventions, Optimization, Error Handling, Recompiling, Temp Table Performance,
  Multi-Purpose Procedures, Return Status and Parameter Techniques

• Views
  Performance, Materialized with Distinct, Group By Views, Uses of Views, Views with Check Option, Improve Code Maintenance, Gain Modularity

• Query Troubleshooting Tools and Approaches Showplan, Statistics I/O and Time, DBCC 302 and 310, Problematic Queries

• Specific SQL Problems, Solutions, & Issues Holdlock, Grouping, Vector Data,
  Awkward Results, Insert Performance

• Cursors
  Row vs. Set Processing, Performance Issues, Locking, Nested Cursors, Multi-table Cursors

• SQL Statement Processing
  General Optimization Steps, Using Select Distinct, Sum, Where, Order By, and Having,
  Understanding Worktables and Groups

• NULL
  What are Nulls, Null’s effect on Queries

• Datatype Conversion and Conflicts        
  Adaptive server Datatypes and Conversions, Data Hierarchy, Datatype Error Handling, Deadlocks, Analyze Data, Deadlock Avoidance, Deadlock Phases, Deadlock Output

• Join Processing
  Join Optimization, Special topics, Join Order, Indexes and Joins, Overriding the Optimizer, Breaking Up Large Queries, Self Joins and Outer Joins