sql-server - 通过使用 SQL CLR 调用 Web 服务将 ElasticSearch 结果提取到 SQL Server

Question

由于性能问题而进行的代码迁移：-

1. SQL Server LIKE 条件（之前）
2. SQL Server 全文搜索 --> 包含（之前）
3. 弹性搜索（当前）

迄今为止取得的成就：-

我们在 ASP.Net Core 中创建了一个网页，该网页具有自动完整下拉列表，其中包含在 Elastic Search 中索引的 2.5+ 百万家公司https://www.99corporates.com/

由于性能问题，我们成功地将代码从 SQL Server 全文搜索转移到了弹性搜索，并在我们的 .Net 代码中使用了 NEST v7.2.1 和 Elasticsearch.Net v7.2.1。

仍在寻找解决方案：-

如果用户没有从自动完成列表中选择一家公司，而只是输入了几个字符并单击 go，那么应该会显示一个列表，我们之前使用 SQL Server 全文搜索完成了 --> CONTAINS

1. 我们可以调用我们使用 SQL CLR 和 SELECT * FROM dbo.Table WHERE Name IN(dbo.SQLWebRequest('') 之类的代码创建的 ASP.Net Web 服务吗？

   [System.Web.Script.Services.ScriptMethod()]
   [System.Web.Services.WebMethod]
   public static List<string> SearchCompany(string prefixText, int count)
   {
   }
   

2. 任何更好或替代的选择

标签： sql-serverelasticsearchnestsqlclrelasticsearch-plugin

Answer 1

虽然该解决方案（即SQL-APIConsumer SQLCLR 项目）“有效”，但它是不可扩展的。它还需要将数据库设置为TRUSTWORTHY ON（存在安全风险），并加载一些程序集UNSAFE，例如Json.NET，如果他们中的任何一个使用静态变量进行缓存，这是有风险的，期望每个调用者被隔离/有自己的应用程序域，因为 SQLCLR 是一个单一的、共享的应用程序域，因此静态变量在所有调用者之间共享，并且多个并发线程可能会导致竞争条件（这并不是说这肯定会发生，因为我还没有看到代码，但是如果您没有查看代码或使用多个并发线程进行测试以确保它不会不构成问题，那么在稳定性和确保可预测的预期行为方面绝对是一场赌博）。

考虑到我确实出售了一个 SQLCLR 库SQL#，我在某种程度上有偏见，其中完整版包含一个也可以执行此操作的存储过程，但是 a) 通过签名正确处理安全性（它不启用）TRUSTWORTHY，b) 允许为了处理可伸缩性，c) 不需要任何UNSAFE程序集，并且 d) 处理更多场景（更好的标头处理等）。它不处理任何 JSON，它只返回 Web 服务响应，OPENJSON如果您愿意，您可以使用或其他方式解压缩该响应。（是的，有一个免费版本的SQL#，但它不包含INET_GetWebPages）。

但是，我认为 SQLCLR 首先不适合这种情况。在这个项目的前两个版本中（使用LIKE和 then CONTAINS）将用户输入直接发送到查询中是有意义的。但是现在您正在使用 Web 服务从该用户输入中获取匹配值的列表，您不再局限于这种方法。您可以而且应该在应用层中单独处理 Web 服务/弹性搜索部分。

您应该执行以下操作，而不是将用户输入传递到查询中，而只是让查询暂停以获取 0 个或更多匹配值的列表：

1. 在执行任何查询之前，直接在应用层获取匹配值列表。
2. 如果没有返回匹配值，您可以完全跳过数据库调用，因为您已经有了答案，并立即响应用户（没有匹配返回时响应时间快得多）

3. 如果存在匹配项，则执行搜索存储过程，通过表值参数 (TVP) 按原样发送匹配列表，该参数成为存储过程中的表变量。使用该表变量对表进行 INNER JOIN，而不是做一个IN列表，因为IN列表不能很好地扩展。此外，请务必使用该方法将 TVP 值发送到 SQL Server ，IEnumerable<SqlDataRecord>而不是DataTable仅浪费 CPU/时间和内存的方法。

   有关如何正确完成此操作的示例代码，请参阅我对将字典传递给存储过程 T-SQL的回答

在 C# 风格的伪代码中，这将类似于以下内容：

List<string> = companies;

companies = SearchCompany(PrefixText, Count);

if (companies.Length == 0)
{
   Response.Write("Nope");
}
else
{
   using(SqlConnection db = new SqlConnection(connectionString))
   {
     using(SqlCommand batch = db.CreateCommand())
     {
        batch.CommandType = CommandType.StoredProcedure;
        batch.CommandText = "ProcName";

        SqlParameter tvp = new SqlParameter("ParamName", SqlDbType.Structured);
        tvp.Value = MethodThatYieldReturnsList(companies);
        batch.Paramaters.Add(tvp);

        db.Open();
        using(SqlDataReader results = db.ExecuteReader())
        {
           if (results.HasRows)
           {
              // deal with results
              Response.Write(results....);
           }
        }
     }
   }
}