SQL CLR Web 服务调用:限制开销 [英] SQL CLR Web Service Call: Limiting Overhead

问题描述

我正在尝试提高应用程序的查询性能，但我在逻辑上卡住了.

I'm attempting to improve query performance for an application and I'm logically stuck.

因此该应用程序是专有的，因此我们无法更改应用程序端代码.然而，我们获得了使用底层数据库的许可(令人惊讶的是).该应用程序调用 SQL Server 数据库，因此我们当前运行的想法是创建一个与表同名的视图并重命名基础表.当应用程序访问视图时，视图会调用两个 SQL CLR 函数之一，这两个函数只是调用我们组合在一起的 Web 服务.Web 服务执行所有逻辑，并包含对外部专有 API 的 API 调用，该 API 执行一些额外的逻辑，然后返回结果.

So the application is proprietary and thus we're unable to alter application-side code. We have, however, received permission to work with the underlying database (surprisingly enough). The application calls a SQL Server database, so the current idea we're running with is to create a view with the same name as the table and rename the underlying table. When the application hits the view, the view calls one of two SQL CLR functions, which both do nothing more than call a web service we've put together. The web service performs all the logic, and contains an API call to an external, proprietary API that performs some additional logic and then returns the result.

这一切都有效，但是，在扩展到大型数据集(超过 100,000 行)时，我们遇到了严重的性能问题.很明显的原因是，我们必须使用 Web 服务一次处理一行，其中包括 API 调用，这会产生大量延迟开销.

This all works, however, we're having serious performance issues when scaling up to large data sets (100,000+ rows). The pretty clear source of this is the fact we're having to work on one row at a time with the web service, which includes the API call, which makes for a lot of latency overhead.

对此的显而易见的解决方案是找出一种方法来限制每个查询必须命中 Web 服务的次数，但这就是我被卡住的地方.我已经阅读了一些可能处理此类场景的不同方法，但作为一个数据库新手，我很难掌握在这种情况下什么是合适的.

The obvious solution to this is to figure out a way to limit the number of times that the web service has to be hit per query, but this is where I'm stuck. I've read about a few different ways out there for potentially handling scenarios like this, but as a total database novice I'm having difficulty getting a grasp on what would be appropriate in this situation.

如果有任何想法/建议，我将不胜感激.

If any there are any ideas/recommendations out there, I'd be very appreciative.

推荐答案

这里可能有几件事情需要看:

There are probably a few things to look at here:

1. 您的 SQLCLR TVF 是否将结果流式传输出来(即您是添加到一个集合中然后在最后返回该集合，还是在完成时释放每一行——要么使用 yield return 或构建一个完整的枚举器)?如果不是流式传输，那么您应该这样做，因为它允许立即使用行而不是等待整个过程完成.

1. Is your SQLCLR TVF streaming the results out (i.e. are you adding to a collection and then returning that collection at the end, or are you releasing each row as it is completed -- either with yield return or building out a full Enumerator)? If not streaming, then you should do this as it allows for the rows to be consumed immediately instead of waiting for the entire process to finish.

由于您要用来自 TVF 的视图替换表，因此自 TVF 以来，性能自然会下降:

Since you are replacing a Table with a View that is sourced by a TVF, you are naturally going to have performance degradation since TVFs:

• 不要报告它们的实际行数.T-SQL 多语句 TVF 始终显示为返回 1 行，而 SQLCLR TVF 始终显示为返回 1000 行.
• 不维护列统计信息.从表中选择时，SQL Server 将自动为 WHERE 和 JOIN 条件中引用的列创建统计信息.

• don't report their actual number of rows. T-SQL Multi-statement TVFs always appear to return 1 row, and SQLCLR TVFs always appear to return 1000 rows.
• don't maintain column statistics. When selecting from a Table, SQL Server will automatically create statistics for columns referenced in WHERE and JOIN conditions.

由于这两件事，如果实际行数为 100k，查询优化器将不会轻松生成合适的计划.

Because of these two things, the Query Optimizer is not going to have an easy time generating an appropriate plan if the actual number of rows is 100k.

有多少 SELECT 等同时点击此视图?由于 View 每次都访问相同的 URI，因此您受到 ServicePointManager ( ServicePointManager.DefaultConnectionLimit).并且默认限制是惊人的2！这意味着，对该 URI 的所有其他请求，虽然已经有 2 个活动/打开的 HttpWebRequests，但将耐心地内联等待.您可以通过设置 HttpWebRequest 对象的 .ServicePoint.ConnectionLimit 属性来增加它.

How many SELECTs, etc are hitting this View concurrently? Since the View is hitting the same URI each time, you are bound by the concurrent connection limit imposed by ServicePointManager ( ServicePointManager.DefaultConnectionLimit ). And the default limit is a whopping 2! Meaning, all additional requests to that URI, while there are already 2 active/open HttpWebRequests, will wait inline, patiently. You can increase this by setting the .ServicePoint.ConnectionLimit property of the HttpWebRequest object.

基础数据多久更改一次?由于您切换到视图，因此不需要任何参数，因此您总是会返回所有内容.这为进行一些缓存打开了大门，并且有两个选项(至少):

How often does the underlying data change? Since you switched to a View, that doesn't take any parameters, so you are always returning everything. This opens the door for doing some caching, and there are two options (at least):

1. 在Web Service中缓存数据，如果没有达到特定的时间限制，则返回缓存的数据，否则获取新数据，缓存并返回.
2. 回到使用真正的表格.创建一个 SQL Server 代理作业，每隔几分钟(如果数据不经常更改，则可能更长):启动事务，删除当前数据，通过 SQLCLR TVF 重新填充，并提交事务.这需要额外的 SQL 代理作业，但随后您将获得更准确的统计数据！！

有关使用 SQLCLR 的更多信息，请访问:SQLCLR 信息

For more info on working with SQLCLR in general, please visit: SQLCLR Info

这篇关于SQL CLR Web 服务调用:限制开销的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！