Do you have any formal or informal standards for reasonably achievable SQL query speed? How do you enforce them? Assume a production OLTP database under full realistic production load of a couple dozen queries per second, properly equipped and configured.
您是否有合理可实现的SQL查询速度的正式或非正式标准?你是如何执行它们的?假设生产OLTP数据库在每秒几十个查询的完全实际生产负载下,正确配备和配置。
Personal example for illustrative purposes (not a recommendation, highly contingent on many factors, some outside your control):
出于说明目的的个人示例(不是推荐,非常依赖于许多因素,有些因素超出您的控制范围):
Expectation:
Each transactional unit (single statement, multiple SQL statements from beginning to end transaction boundaries, or a single stored procedure, whichever is largest) must execute in 1 second or less on average, without anomalous outliers.
每个事务单元(单个语句,从开始到结束事务边界的多个SQL语句,或单个存储过程,以最大者为准)必须在1秒或更短时间内执行,没有异常异常值。
Resolution:
Slower queries must be optimized to standard. Slow queries for reports and other analysis are moved to an OLAP cube (best case) or a static snapshot database.
较慢的查询必须针对标准进行优化。报告和其他分析的慢查询将移动到OLAP多维数据集(最佳情况)或静态快照数据库。
(Obviously some execution queries (Insert/Update/Delete) can't be moved, so must be optimized, but so far in my experience it's been achievable.)
(显然一些执行查询(插入/更新/删除)无法移动,因此必须进行优化,但到目前为止,根据我的经验,这是可以实现的。)
3 个解决方案
#1
I usually go by the one second rule when writing/refactoring stored procedures, although my workplace doesn't have any specific rules about this. It's just my common sense. Experience tells me that if it takes up to ten seconds or more for a procedure to execute, which doesn't perform any large bulk inserts, there are usually serious problems in the code that can easily be corrected.
在编写/重构存储过程时,我通常遵循一秒规则,尽管我的工作场所没有任何特定的规则。这只是我的常识。经验告诉我,如果执行过程需要十秒或更长时间,而不执行任何大型批量插入,则代码中通常存在严重问题,可以轻松纠正。
They way most common problem I encounter in SP:s with poor performance is incorrect use of indexes, causing costly index seek operations.
他们在SP中遇到的最常见问题:性能不佳的问题是索引的使用不正确,导致代价高昂的索引查找操作。
#2
Given that you can't expect deterministic performance on a system that could (at least in theory) be subject to transient load spikes, you want your performance SLA to be probabilistic. An example of this might be:
鉴于您无法预期系统上的确定性性能(至少在理论上)会受到瞬态负载峰值的影响,您希望性能SLA具有概率性。一个例子可能是:
95% of transactions to complete within 2 seconds.
95% of search queries (more appropriate for a search screen) to complete within 10 seconds.
95% of operational reports to complete within 10 seconds.
95%的交易在2秒内完成。 95%的搜索查询(更适合搜索屏幕)在10秒内完成。 95%的运营报告在10秒内完成。
Transactional and search queries can't be moved off transactional system, so the only actions you can take are database or application tuning, or buying faster hardware.
事务性和搜索查询无法从事务系统移出,因此您可以采取的唯一操作是数据库或应用程序调优,或购买更快的硬件。
For operational reports, you need to be ruthless about what qualifies as an operational report. Only reports that absolutely need to have access to up-to-date data should be run off the live system. Reports that do a lot of I/O are very anti-social on a production system, and normalised schemas tend to be quite inefficient for reporting. Move any reports that do not require real-time data off onto a data warehouse or some other separate reporting facility.
对于运营报告,您需要对有资格作为运营报告的内容进行无情的处理。只有绝对需要访问最新数据的报告才能在实时系统上运行。执行大量I / O的报告在生产系统上非常反社交,而规范化模式在报告方面往往效率很低。将不需要实时数据的任何报告移动到数据仓库或其他单独的报告工具中。
#3
O of N is good and anything worse like N^2 will eventually be too slow.
N的O是好的,而N ^ 2之类的东西最终会变得太慢。
#1
I usually go by the one second rule when writing/refactoring stored procedures, although my workplace doesn't have any specific rules about this. It's just my common sense. Experience tells me that if it takes up to ten seconds or more for a procedure to execute, which doesn't perform any large bulk inserts, there are usually serious problems in the code that can easily be corrected.
在编写/重构存储过程时,我通常遵循一秒规则,尽管我的工作场所没有任何特定的规则。这只是我的常识。经验告诉我,如果执行过程需要十秒或更长时间,而不执行任何大型批量插入,则代码中通常存在严重问题,可以轻松纠正。
They way most common problem I encounter in SP:s with poor performance is incorrect use of indexes, causing costly index seek operations.
他们在SP中遇到的最常见问题:性能不佳的问题是索引的使用不正确,导致代价高昂的索引查找操作。
#2
Given that you can't expect deterministic performance on a system that could (at least in theory) be subject to transient load spikes, you want your performance SLA to be probabilistic. An example of this might be:
鉴于您无法预期系统上的确定性性能(至少在理论上)会受到瞬态负载峰值的影响,您希望性能SLA具有概率性。一个例子可能是:
95% of transactions to complete within 2 seconds.
95% of search queries (more appropriate for a search screen) to complete within 10 seconds.
95% of operational reports to complete within 10 seconds.
95%的交易在2秒内完成。 95%的搜索查询(更适合搜索屏幕)在10秒内完成。 95%的运营报告在10秒内完成。
Transactional and search queries can't be moved off transactional system, so the only actions you can take are database or application tuning, or buying faster hardware.
事务性和搜索查询无法从事务系统移出,因此您可以采取的唯一操作是数据库或应用程序调优,或购买更快的硬件。
For operational reports, you need to be ruthless about what qualifies as an operational report. Only reports that absolutely need to have access to up-to-date data should be run off the live system. Reports that do a lot of I/O are very anti-social on a production system, and normalised schemas tend to be quite inefficient for reporting. Move any reports that do not require real-time data off onto a data warehouse or some other separate reporting facility.
对于运营报告,您需要对有资格作为运营报告的内容进行无情的处理。只有绝对需要访问最新数据的报告才能在实时系统上运行。执行大量I / O的报告在生产系统上非常反社交,而规范化模式在报告方面往往效率很低。将不需要实时数据的任何报告移动到数据仓库或其他单独的报告工具中。
#3
O of N is good and anything worse like N^2 will eventually be too slow.
N的O是好的,而N ^ 2之类的东西最终会变得太慢。