Advanced SQL & Optimization for Sybase

Length: 4 Days
Prerequisites:  SQL syntax:  SELECT, INSERT, UPDATE, DELETE:
Recommended: " Introduction to Adaptive Server"


Overview

Soaring Eagle presents "Advanced SQL Optimization for Adaptive Server," to DBAs, System Administrators and Client Application
Developers. Attendees will learn how Adaptive Server processes queries. Learn to identify problematic statements and 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.

Key Benefits:  When you complete this course you learn how to write efficient SQL Statements, Understand When to Reorganize a Query.
You will be able to Recognize Performance Issues and understand how stored procedures function.


Outline


Chapter 1. Fundamentals of Query Processing

• Query Processing Steps
• Possible Bottlenecks
• Define The Basic Steps in Query Processing

Chapter 2. Updates

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

Chapter 3.  Union Queries

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

Chapter 4. BLOBs

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

Chapter 5. Multi-Table Queries

• Join Processing
• Optimization
• Nested Iteration
• Reformatting
• Device Performance
• Breaking up Large Queries
• Self Joins
• Outer Joins
• Multi-Table Joins

Chapter 6. Single-table Optimization

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

 Chapter 7.  Subqueries

• Categorization
• Expression
• Quantified Predicate
• Correlated Subqueries
• Processing & Flattening
• Use with ANY, ALL, IN, NOT IN, EXISTS & NOT EXISTS

Chapter 8.  Stored Procedures

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

Chapter 9.  Views

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

Chapter 10. Query Troubleshooting Tools & Approaches

• Showplan
• Statistics I/O and Time
• DBCC 302 & 310 Problematic Queries

Chapter 11.  Specific SQL Problems, Solutions & Issues

• Holdlock
• Grouping
• Vector Data
• Awkward Results
• Insert Performance

Chapter 12.  Cursors

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

Chapter 13.  SQL Statement Processing

• General Optimization Steps
• Using Select Distinct
• Sum
• Where
• Order by
• Having
• Understanding Worktables and Groups

 

Chapter 14.  NULL

• What are Nulls
• Null's effect on Queries

Chapter 15.  Datatype Conversion & Conflicts

• Adaptive Server Datatypes & Conversions
• Data Hierarchy
• Datatype Error Handling
• Deadlocks
• Analyze Data
• Deadlock Avoidance
• Deadlock Phases
• Deadlock Output

Chapter16.  Join Processing

• Join Optimization
• Special Topics
• join Order
• Indexes and Joins
• Overriding the Optimizer
• Breaking Up Large Queries
• Self Joins & Outer Joins

Back to Course Listing