SQL DB 性能和短时间间隔的重复查询
如果一个查询不断地以短时间间隔（例如每 5 秒）发送到数据库，那么生成的读取次数是否会导致性能或可用性方面的问题？如果数据库是 Oracle，是否有任何技巧可以用来避免性能下降？如果查询来自应用程序，是否有办法通过软件设计来减少影响？
If a query is constantly sent to a database at short intervals, say every 5 seconds, could the number of reads generated cause problems in terms of performance or availability? If the database is Oracle are there any tricks that can be used to avoid a performance hit? If the queries are coming from an application is there a way to reduce any impact through software design?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
除非您的查询非常密集或编写得非常糟糕，否则每隔几秒运行一次就不会导致任何明显的问题。对于通常以毫秒为单位的查询来说，这种情况并不常见。
不过，您可能仍然想优化它，只是因为有更好的方法可以做到这一点。在 Oracle 和 ADO.NET 中，您可以使用 OracleDependency 作为第一次运行查询的命令，然后订阅其 OnChange 事件，每当底层查询时该事件就会自动调用。数据会导致查询结果发生变化。
Unless your query is very intensive or horribly written then it won't cause any noticeable issues running once every few seconds. That's not very often for queries that are generally measured in milliseconds.
You may still want to optimize it though, simply because there are better ways to do it. In Oracle and ADO.NET you can use an
OracleDependencyfor the command that ran the query the first time and then subscribe to itsOnChangeevent which will get called automatically whenever the underlying data would cause the query results to change.这取决于查询。我认为您想要定期执行它的原因是因为返回的数据会频繁更改。如果是这种情况，那么应用程序级缓存显然不是一个选择。
除此之外，就返回的行数、连接的表、聚合/计算的数据而言，此查询是否“大”？如果是这样，如果出现以下情况，则可能会出现问题：
您的查询速度比执行查询的速度快。如果您每秒调用一次，但运行需要 2 秒，这将成为一个问题。
如果查询涉及大量数据，并且您有许多其他查询访问相同的表，则可能会遇到锁升级问题。
与大多数性能问题一样，唯一真正的答案是测试。在这种情况下，使用数据库中的实际数据进行测试，并与您期望在系统上运行的其他查询负载同时运行此查询。
It depends on the query. I assume the reason you want to execute it periodically is because the data being returned will changed frequently. If that's the case, then application level caching is obviously not an option.
Past that, is this query "big" in terms of the number of rows returned, tables joined, data aggregated / calculated? If so, it could be a problem if:
You are querying faster than it takes to execute the query. If you are calling it once a second, but it takes 2 seconds to run, that's going to become a problem.
If the query is touching a lot of data and you have a lot of other queries accessing the same tables, you could run into lock escalation issues.
As with most performance questions, the only real answer is to test. In this case test with realistic data in the DB and run this query concurrent with the other query load you expect on the system.
按照 Samuel 的建议，Oracle 在 JDBC 中提供了工具来执行 数据库更改通知，以便您的应用程序可以订阅基础数据的更改，而不是每隔几秒重新运行查询。如果数据的更改频率低于运行查询的频率，这可能会带来重大的性能优势。
另一种选择是使用 Oracle TimesTen 作为中间数据的内存缓存层机器。这将减少网络往返，并且将经历非常优化的检索路径。
最后，我会看看如何使用 查询结果缓存让 Oracle 缓存结果。
Along the lines of Samuel's suggestion, Oracle provides facilities in JDBC to do database change notification so that your application can subscribe to changes in the underlying data rather than re-running the query every few seconds. If the data is changing less frequently than you're running the query, this can be a major performance benefit.
Another option would be to use Oracle TimesTen as an in memory cache of the data on the middle tier machine(s). That will reduce the network round-trips and it will go through a very optimized retrieval path.
Finally, I'd take a look at using the query result cache to have Oracle cache the results.