Before completing the look at Qure’s recommendations, let’s review two more recommendations.
OR clauses: Such a construct like SELECT … FROM table WHERE column A = ? OR columnB = ? is quite frequently used to support flexible search queries from the client application. Nothing wrong with providing such a flexibility in searches, but instead of using OR clauses many queries can be rewritten like this and provide “better” performance:
Here you see what Qure suggests a rewrite for such an OR query.
And here you see the effect of the recommendation. Most notable is the reduction in logical reads. But now we guess people will start arguing about the reduction in overall duration comes now as additional CPU time. Wait! Before shouting let’s have a look at how the recommended rewrite affects the query execution plan:
Yes, you can actually see the original and the rewritten execution plan from within Qure. For this, just click on the “Execution Plan” button.
The original query on the left uses a clustered index scan which is kind of expected given the OR clause. Now, the rewritten query on the right uses an index seek which is as efficient as it can get. So, the rewrite is most likely well worth the increase in CPU time. Especially when the table grows and more and more data would have to be processed by the originally chosen clustered index scan.
Last but not least let’s have a look at another optimization technique in Qure’s arsenal that is quite remarkable for an automation tool: Rewriting function calls on columns. What does that mean? Well, imagine you want to find all orders that were placed between the 02.02.2004 and 06.02.2004. While SQL is a declarative language where you just express what you want and leave it up to the engine to carry out the request, all too often you will find that such a request is expressed as SELECT … FROM table WHERE YEAR(OrderDate) = ? AND MONTH(OrderDate) = ? AND DAY(OrderDate) BETWEEN 2 AND 5. Well, certainly this will give you the expected result. However, this hardly can be called optimal. This not only prevents the optimizer from efficiently using any existing index but it also is a very unnatural way of expressing this request.
Qure can help you rewriting such a query, as you can see in the above screenshot. This is not only a far more natural way of expressing the request, but it can also potentially lead to a dramatically “better” execution plan, when there is a supporting index on a datetime column. In the above case the net effect is 0. That’s because the query is a SELECT * query. We are asking for all columns anyway, and the only chance in the original and the rewritten query to satisfy the query is a clustered index scan.
But this last example demonstrates quite nicely Qure’s integrative optimization approach. For the aforementioned batch there are 3 recommendations available.
- Object identified usage optimization
- SELECT * abuse
- Explicit function call misuse
So far, so good with diving into Qure’s recommendations. We leave the rest up for you to explore how you can benefit from this tool in your individual environment. And yes, there are a few more really cool features to discover such as smart filtering or the optimize feature which is actually the part of Qure that prepares the change and the rollback script.
Last, but not least, as a goodie, Qure allows you to export its analysis as an Excel workbook. This gives you the opportunity to use Excel and the whole MS Office family tools to produce high-quality presentations about how to optimize database performance based on Qure’s recommendations.
Conclusion
Database performance optimization is neither a black art nor rocket science. However, it requires someone who is knowledgeable with the topic and not every company employees such a person and bringing in some consultant is a costly act that bears no guarantee for success.
But it takes more to fully tune a given database. It requires in-depth knowledge of the databases’ purpose, its’ place in the whole environment and, last, but not least, knowledge of the data that is stored in the database. Therefore it is a good alternative to have tools like Qure available that can assist you in a holistic tuning process. Even people who are knowledgeable in performance tuning and optimization can benefit from using Qure, because all too often small details are overlooked and also all too often there is too little time to fully investigate performance issues and in such cases is having a tool that does much of the work automatically for you certainly a big plus. But Qure takes this approach even one step further and helps you in applying generally accepted best practices. This makes Qure a useful tool which is definitely worth a try.
]]>