为报表编写 SQL 查询有何不同？
为报表编写 SQL 查询（通常每周、每月一次或偶尔运行一次）与为每天运行多次的 Web 应用程序（或多个应用程序）编写查询有何不同？如果查询每月只运行一次，那么速度和性能并不是一个大问题，对吗？更频繁地使用 SQL 的各个方面（临时表、CTE 等）怎么样？
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 once 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)?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
它们没有不同，并且适用于任何其他查询的相同注意事项。
速度和性能是否是一个问题不是您的决定，而是报告用户的决定。
如果您的月度报告查询需要 3 小时，并且阻塞了该过程中的多个表，并导致您的其他应用程序因为无权访问这些表而停止运行，会发生什么情况？
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?
对于报告来说，速度和性能通常是一个更大的问题。您的平均 CRUD SQL 影响通常仅影响应用程序中的 1 条记录，搜索可能需要更多记录，但结果通常仅限于设定的行数。
报告可能需要聚合来自数百万条记录和 20-30 个联接的数据。编写不当会极大地降低系统速度，当然，当用户必须等待 10 个小时才能获得他们要求的报告时，用户会变得非常暴躁。它还可能导致阻塞。然后就会出现脏读问题，这可能会在报告中导致更多问题。通常存储数据用于报告是有原因的，通常是为了提高性能并防止报告打扰执行事务工作的用户。
与几乎任何访问不生成某种形式报告的 Web 应用程序的 SQL 相比，为报告编写查询通常要复杂得多，并且更难进行性能调整（由于复杂性）。我见过长度超过 500 行的报告查询。
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.