|
|
Advanced Sybase SQL and Optimization
| Duration: 4 days |
| Participants: This course is for Sybase Adaptive Server Enterprise DBAs, System Administrators, and Client Application Developers. |
|
Objectives:
Upon successful completion of this course you will be able to:
- Write efficient SQL statements. - Understand when to reorganize a Query. - Recognize SQL performance issues. - Code and use stored procedures function. |
|
Overview:
- This course teaches participants how Adaptive Server processes queries. Participants learn to identify problematic statements and queries in an application design before they are implemented, thus saving time and money. - Participants also estimate query performance on paper, and use those estimates to identify implementation issues with queries. - The instructors are all are experienced Adaptive Server professionals ready to add their insight to the classroom environment. |
| Prerequisites: Knowledge of SQL syntax: SELECT, INSERT, UPDATE, DELETE. Prior attendance at the 'Introduction to Adaptive Server' course is recommended. |
| Format: Lecture and discussion with hands-on exercises. |
|
Topic Outline:
- Fundamentals of Query Processing Query Processing Steps Possible Bottlenecks 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, and 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, and 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 |