Performance issues are one of the primary reason and organizations are unsatisfied with their existing reports. One of the primary reason of this performance issue could be caused by report queries. Here are some of the most important thing to consider when writing reporting queries.
1. Benchmark
Benchmarking the queries prior to use in reports sets expectation on result set execution and data return time.
2. Avoid using Select *
Avoid using Select * from any table that contains columns/fields that are unnecessary for report creation. Having these fields not only slows the query execution but also creates confusion.
3. Use Comments
It is always the best practice to commenting on query writing. This helps us remember what we are trying to retrieve and helps others to edit the reports and query down the road.
4. Avoid complex calculations
Avoid complex calculations on the SQL statements. Writing SQL statements to get multiple complex result set can lead to performance issues. You can answer most of these problems by creating variables in the report itself.
5. Use DB Views or Stored Procedures
Use database views or Stored Procedure if there are report that used multiples SQL. This may help you to isolate SQL problems with reporting problems. If there is an issue with SQL statement, then you will know there is a problem with you view or stored proc and not with the reports.
6. Avoid selecting multiple fields with the same name
7. Join statements
To avoid performance issues avoid creating unnecessary joins.
8. Use of right commands.
Using right SQL commands makes a huge difference on the performance of your reports Example (When we use WHERE, the command restricts the result set before returning rows and HAVING filters the result set after bringing all the rows. Using WHERE command where possible is a better choice).
9. Subqueries
When Subqueries are used in a sql statements make sure the result set only returns the necessary dataset that does not deviate from the main query.