How is writing SQL queries for reports different?

How does writing SQL queries for reports, which often are just run once a week, once a month or some infrequent amount, differ from writing queries for web applications (or applications) which are run many times a day? If a query runs only o开发者_运维百科nce a month, speed and performance isn't a big issue, correct? How about in terms of using aspects of SQL more frequently (temp tables, CTEs, etc)?

They are not different and the same considerations taken with any other query apply.

Whether speed and performance are an issue is not your decision, but that of the report users.

What happens if your monthly report query takes 3 hours, blocking several tables in the process and bringing your other applications to a halt because they don't have access to them?

Speed and performance are often a much bigger issue for reports. Your average CRUD SQL affects often affects only 1 record from an application, a search might take more but typically the results are limited to a set number of rows.

A report might need to aggregate data from millions of records and with 20-30 joins. Improperly written it can slow down the system tremendously and, of course, users get extremely cranky when having to wait 10 hours to get that report they asked for. It can also cause blocking. Then you have the issue of dirty reads which can cause much more problems in a report. There is a reason why data is often warehoused for reporting and that is usually to improve performance and to keep reporting from bothering the users doing the transactional work.

Writing queries for reports is often far more complex and harder to performance tune (due to the complexity) than pretty much any SQL hitting a web application that isn't producing some form of report. I have seen report queries that are over 500 lines long.