Db2 11 - Performance - Writing efficient SQL queries.
This SQL tutorial is much more than a mere list of hints and tips on writing efficient SQL - it also include the reasons why and explains what the difference is.. One important point to remember is that Oracle caches the compiled form of SQL and is therefore able to re-use queries which are the same as previously executed queries.
In early phase of my career, I learned that writing SQL queries was not just about writing but writing so it can run efficiently. Performance of SQL queries are critical to any application that is built to use Oracle and I say Oracle because this blog is specifically for Oracle SQL.
The mindset of “the more data, the better” isn’t one that you should necessarily live by when you’re writing SQL queries: not only do you risk obscuring your insights by getting more than what you actually need, but also your performance might suffer from the fact that your query pulls up too much data.
It is definitely more efficient. My question is, If I am using this query on a web page to pull the data for automated metrics, when and where do I create the temp tables. If I do it on the page load, I am still going to have to wait on the temp tables to be created.
The most efficient way to write this query is without joins at all. The key is that the CASE expression is only ever going to return 3 (or 30) unique values if it finds a match. You can save off the results into local variables and just use those in the query. The query below finishes in around 1.044 seconds.
This may not seem like a big deal, now, but if you ever have to change the query, it is, as now when you make a change you need to be sure to make the same change in several locations. SubQuery or Inner Join? Which is More Efficient? Here is the query plan for the sub query version: I’ve highlighted the effect of the four subqueries.
SQL query optimization is being applied in order to minimize the possibility of your query being the system bottleneck. Hence, it is always good to know some good and simple ways to optimize your SQL query. In this article, you will get to see 15 simple and easy to applied SQL query optimization.