![]() Then from there, I would have to repeat for each major rewrite alternative, and simply choose the best run time among the final options for each major rewrite. I’m not going to show every step of the SQL performance tuning and optimization process, but rather how I attacked one major rewrite option to find its best minor rewrite options with hints. SQL Optimizer makes short work of this rather complicated scenario. ONLINE MYSQL OPTIMIZER MANUALHow do you know which rewrite and which optimizer hints to choose that will run best and work with the existing data structures? Are there modifications to existing data structures which would result in plans that run faster? Are there new data structures to be added which would improve run times? Are there existing data structures which could be dropped to obtain better results (or more safely just ignored for this query)? It would be at least a week’s worth of manual efforts to answer all these questions while hoping that we don’t miss anything from the many thousands of alternatives. But we’re nowhere near done, because each of these alternatives could possibly be augmented by optimizer hints, thereby offering many thousands of alternatives per rewrite. Select a.LASTNAME, a.FIRSTNAME, b.RENTALDATE,įrom employee a, movierental b, rentalitem c, moviecopy dįrom employee a JOIN movierental b using(EMPLOYEEID)ĪNSI JOIN + IN à EXISTS CORRELATED SUBQUERYĪNSI JOIN + COMMON TABLE EXPRESSION (CTE) I came up with four, so here are the original and those four equivalent SQL rewrites. Now before diving into explain/execution plans, I considered all the equivalent rewrites to match the original SQL. I next ran the original query to establish that it ran slowly and to provide a baseline for comparisons. So a red flag went up immediately that this SQL might be a possible candidate for rewriting. ONLINE MYSQL OPTIMIZER CODEOnce during a code review, I saw a SQL statement that was written using the old, non-ANSI join syntax. So let’s examine a real world scenario where SQL Optimizer helped me with SQL performance tuning and optimization. So I prefer SQL Optimizer since its primary focus is SQL rewrites while also offering all the other areas for improvement, including database configuration and structural modifications. And even when they do, I have found those tools to be far more oriented toward suggesting database configuration and structural modifications, as opposed to basic SQL tuning recommendations. But I cannot assume my customers are using the enterprise database version with the optional diagnostics and tuning packs. And yes, some database vendors, such as Oracle, offer SQL performance tuning and optimization tuning tools. Inefficient SQL sits under the surface of database performanceīut even with 30 year’s database experience, I need a tool to assist with such efforts. Since a picture is often worth a thousand words, please remember this image when thinking about SQL performance tuning and optimization. Hence, SQL performance tuning and optimization is paramount for remedying inefficient SQL and improving database performance. Even an Oracle Exadata machine can be slowed down by unhealthy SQL. I often joke that a Cray supercomputer could run an infinite loop in a mere three seconds! But the sad truth is that even with today’s mature database optimizers, bad SQL can easily cripple any database’s performance. Of all those, the last is the most prevalent and damaging. Inferior application design of database access methods.Poor design of tables, indexes, partitions, shards, etc.Inappropriately configured database parameters.Unsuitably configured operating system parameters.Improperly configured computing resources.Here are the most common causes of inefficiency: So what can cause that time to take longer than is acceptable? How can SQL performance tuning and optimization make that process faster? We want the time from when the SQL is submitted to the database to when the final answer is delivered to happen as quickly as possible. It is only when a SQL statement is submitted that data is put into motion. Remember, that a database at rest does absolutely nothing. That means applying SQL performance tuning and optimization to our execution plans. No database can process all queries expediently, but we can strive to make sure our databases process SQL as efficiently as possible. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |