Transcript document
3150 Optimization II: Optimizing InterBase SQL and Metadata Craig Stuntz TeamB / Senior Developer Vertex Systems Corporation About this Course How to design DB metadata for optimum performance. How to write optimal SQL statements. Will not cover optimizing applications (that’s course #3154) Will not cover optimizing server configurations (that’s course #6104 – CD only) The Golden Rule of Optimization Optimize the slow stuff first. In other words, it is important to optimize use of your own time as well as your application. Corollary to the Golden Rule Always develop database applications using a database populated with real data. customer data, if available. • Use real • Scramble to protect customer privacy. • Use a test data generator if real data is not available. • Avoid using a local server. Fundamentals Understand query execution Good metadata design Good SQL design A well-maintained database Some Useful Tools IBConsole features. Query analyzer. Standalone performance monitoring. Gstat. Understand Query Execution Client view: Prepare, Execute, Fetch Server view: Parse, Optimize, Execute, wait for Fetch. BLR stored for some objects, optimization PLAN never stored. Result set size Records read vs. records returned Indexed vs. sequential table reads Analyzing Statement Execution PLAN statement – easier to understand when in graphical form. Fetch statistics. Performance Monitoring. Explicit PLAN Statements Can specify a PLAN in a SELECT statement. Not a “hint,” overrides optimizer altogether. Doesn’t work with indices generated for primary/foreign keys. You can “hint” to the optimizer by using some SQL tricks. Avoid whenever possible. Understanding Indices UNIQUE vs. non-unique. ASCENDING vs. DESCENDING. Single-column vs. compound. Can be combined during execution. Selectivity. Generally not useful for ORDER BY. Prefix compression. Slower DML. Compound Indices No execution-time penalty for combining columns. Useless if columns in wrong order in index. Can confuse optimizer prior to InterBase 7.1 Service Pack 1 Indices and JOINs JOIN vs. MERGE in PLAN. Cannot use an index to perform a JOIN with the result set of a SELECTable stored procedure. Can use an index to perform a JOIN with the result set of a VIEW, but it can get complicated. INNER vs. OUTER JOINs. Indices and WHERE Clauses Look for NATURAL vs. INDEX in PLAN (but PLAN doesn’t show everything. Consider real selectivity. LIKE, STARTING WITH, and parameters. SQL functions and UDFs. How to defeat index use. Indices and ORDER BY Total time to execute a query and fetch a result set is usually slower when an index is used to “optimize” the ORDER BY clause. Really! On the other hand, the execution (without fetching) is faster with the index. How to defeat index use for ORDER BY. Statements with Multiple PLANs Statements with subqueries and UNIONs will have multiple PLANs. You can optimize each SELECT separately, but watch out for correlated subqueries. Subqueries with lists of constants. Understand the difference between UNION and UNION ALL. Understanding the RDB$DB_KEY Mostly useful in stored procedures. Faster than the primary key for locating a single record. Especially useful for UPDATE statements which would affect many rows – replace with a FOR SELECT and an inner UPDATE for each row. Database Maintenance Index statistics. Rebalancing indices. Data page fill. Backup and restore. Questions? Thank You 2124 InterBase Performance Monitoring: Vision and Control Please fill out the speaker evaluation You can contact me further by posting questions on the Borland newsgroups