Long Queries and the Art of Full Scan

Some queries just can’t run in a fraction of a second, no matter how well written. This does not mean they can’t be optimized. Many practitioners hold that since analytical reports do not have strict response time requirements, it is not important how fast or slow they run. In extreme cases, report developers make no effort to make sure that reports are complete in a reasonable time, giving the excuse that the query only runs once a day or once a week or once a month.
This is a dangerous practice. If report performance is neglected, performance can easily degrade from minutes to hours or more. We have observed reports that run for six days before completion! And when the situation becomes that severe, it is not easy to fix in a limited time frame. Often, when an analytical report is developed, source data volumes are really small, and everything performs well. It is the job of SQL developers to examine execution plans even if queries are running fine now and to be proactive to prevent future performance degradation.
In course of his presentation you will learn:
Which queries are considered long
How to optimize full table scans
How the order of joins affects query performance
How to optimize grouping
How to avoid multiple table scans
Additional techniques for long queries optimization, such as temporary tables, CTEs, and materialized view