c# - SQL Assembly WebResponse和字符串解析非常慢

Question

因此，我很快就学习了C＃的方法（继承了此问题的完整菜鸟）；我已经编写了以下代码，该代码调用了一个Web服务，该服务返回的JSON格式总是不正确的。这里的任务是获取JSON字符串并将其分成数组段，这些数组段将插入到SQL表中以进行进一步的分析和测试。即如果返回字符串是这样的

   {1234:{5678:{1:{"key":"val","key":"val"},{2:{"key":"val","key":"val"}}}}

那么这些行将是：

{1234}
{5678}
{1:{"key":"val","key":"val"}
{2:{"key":"val","key":"val"}

这是.NET 3.0和SQL Server 2008 R2（旧版资料）。
这是我的工作代码：

 public partial class UserDefinedFunctions
    {
         [Microsoft.SqlServer.Server.SqlFunction(DataAccess = 
    DataAccessKind.Read)]
    public static SqlString TestParse(SqlString uri, SqlString username, SqlString passwd, SqlString postdata)
    {
            //-----
           // The SqlPipe is how we send data back to the caller
       SqlPipe pipe = SqlContext.Pipe;
        SqlString document;
        try
        {
            // Set up the request, including authentication
            WebRequest req = WebRequest.Create(Convert.ToString(uri));
            if (Convert.ToString(username) != null & Convert.ToString(username) != "")
            {
                req.Credentials = new NetworkCredential(
                    Convert.ToString(username),
                    Convert.ToString(passwd));
            }
            ((HttpWebRequest)req).UserAgent = "CLR web client on SQL Server";

            // Fire off the request and retrieve the response.
            using (WebResponse resp = req.GetResponse())
            {

                using (Stream dataStream = resp.GetResponseStream())
                {
                    //SqlContext.Pipe.Send("...get the data");
                    using (StreamReader rdr = new StreamReader(dataStream))
                    {
                        document = (SqlString)rdr.ReadToEnd();
                        rdr.Close();

                        //-----
                        string connectionString = null;
                        string sql = null;
                        connectionString = "Data source= 192.168.0.5; Database=Administration;User Id=Foo;Password=Blah; Trusted_Connection=True;";
                        using (SqlConnection cnn = new SqlConnection(connectionString))
                        {
                            sql = "INSERT INTO JSON_DATA (JSONROW) VALUES(@data)";
                            cnn.Open();
                            using (SqlCommand cmd = new SqlCommand(sql, cnn))
                            {

                                String payload = "";
                                String nestpayload = "";
                                int nests = 0;
                                String json = document.ToString();
                                /*first lets do some housekeeping on our payload; double closing curly braces need to be escaped (with curly braces!) in order to keep them in the string.*/
                                json = json.Replace("\\", "");
                                int i = json.Length;
                                //return new SqlString(json);
                                while (i > 1)
                                {
                                    /*find the first closing "}" in the string and then check to see if there are more than one.
                                    We need to read the data up to each closing brace, pull off that substring and process it for each iteration until the string is gone.*/
                                    int closingbrace = json.IndexOf("}"); //First closing brace
                                    int nextbrace = Math.Max(0, json.IndexOf("{", closingbrace)); //Next opening brace
                                    String ChkVal = json.Substring(closingbrace + 1, Math.Max(1, nextbrace - closingbrace)); //+1 to ignore the 1st closing brace
                                    int checks = Math.Max(0, ChkVal.Length) - Math.Max(0, ChkVal.Replace("}", "").Length);
                                    payload = json.Substring(0, Math.Max(0, (json.IndexOf("}") + 1)));
                                    /*Remove the payload from the string*/
                                    json = json.Substring(payload.Length + 1);

                                    /*"nests" is how many nested levels excluding the opening brace for the closing brace we found.*/
                                    nests = (payload.Length - payload.Replace("{", "").Length);
                                    /*If we have more then one nest level check to see if any of them go with the payload*/

                                    if (nests > 1)
                                    {
                                        /*Break out the nested section and remove it from the payload.*/
                                        nestpayload = payload.Substring(0, payload.LastIndexOf("{"));
                                        payload = payload.Substring(payload.LastIndexOf("{"), payload.Length - payload.LastIndexOf("{"));

                                        while (nests > 1)
                                        {
                                            if (checks > 0) //# of right braces in payload equals number of left-side nests go with the payload
                                            {
                                                // payload = nestpayload.Substring(Math.Max(0, nestpayload.LastIndexOf("{")), Math.Max(0, nestpayload.Length) - Math.Max(0, (nestpayload.LastIndexOf("{")))) + payload;//The second Math.Max defaults to 1; if we got here there is at minimum one "{" character in the substring
                                                payload = nestpayload.Substring(nestpayload.LastIndexOf("{")) + payload;
                                                nestpayload = nestpayload.Substring(0, Math.Max(0, Math.Max(0, nestpayload.LastIndexOf("{"))));
                                                checks--;
                                                nests--;
                                            }
                                            else
                                            {
                                                /*If we got here there are no more pieces of the nested data to append to the payload.
                                                 We use an array and string.split to keep the nest ordering correct.*/
                                                string[] OrderedNest = nestpayload.Split('{');
                                                for (int s = 0; s < OrderedNest.Length; s++)
                                                {
                                                    if (OrderedNest[s] != "")
                                                    {
                                                        cmd.Parameters.AddWithValue("@data", "{" + OrderedNest[s].Replace(":", "}"));
                                                        cmd.ExecuteNonQuery();
                                                        cmd.Parameters.Clear();
                                                    }
                                                }

                                                //cmd.Parameters.AddWithValue("@data", nestpayload.Substring(Math.Max(0,nestpayload.LastIndexOf("{"))).Replace(":","}"));
                                                //cmd.Parameters.AddWithValue("@data", OrderedNest[1].Replace(":","}")+OrderedNest[2]);
                                                // cmd.ExecuteNonQuery();
                                                //cmd.Parameters.Clear();
                                                //nests = Math.Max(0, nests - 1);
                                                nests = 0;
                                                //nestpayload = nestpayload.Substring(0, Math.Max(0, Math.Max(0,nestpayload.LastIndexOf("{"))));

                                            }
                                        }
                                    }


                                    /*At the very end payload will be a single "}"; check for this and discard the last row*/
                                    if (payload != "}")
                                    {
                                        cmd.Parameters.AddWithValue("@data", new SqlChars(payload));
                                        cmd.ExecuteNonQuery();
                                        cmd.Parameters.Clear();
                                    }

                                    /*Get the new string length*/
                                    i = json.Length;
                                    payload = "";

                                }

                            }
                        }
                        //-----

                        /*  }
                          catch (Exception e)
                          {
                              return e.ToString();
                          }*/
                    }

           // Close up everything...
                    dataStream.Close();
                }
                resp.Close();
                // .. and return the output to the caller.

            }//end using
            return ("Finished");
        }
        catch (WebException e)
        {

            throw e;
        }                   
  }
}

当它起作用时，它的速度令人难以置信。 4分钟以上即可将1500行写入服务器。每天一次，这将需要写入约60,000条记录；剩下的时间可能是100条记录已发布并返回（我还没有完成POST部分）。我敢肯定，我在这里做的很多事情都不太正常，这会引起问题，但是我绝对不知道从哪里开始。我很兴奋，可以从中得到正确的回应！任何想法/想法/帮助/同情将不胜感激。

Answer 1

这里存在几个问题，其中最重要的一个问题是您似乎已将“ sa”密码发布到了此处的公共互联网上。这是我看到的代码问题：


尽管可以在SQLCLR中进行Web服务调用，但这绝对是一个高级话题，充满了陷阱。这不是SQLCLR的新手/初学者应该做的事情，它本身已经是常规.NET编程的细微差别。
摆脱SqlPipe行和其上方的注释行。函数不会通过SqlPipe将数据传递回调用方；用于存储过程。
您可能不应该使用WebRequest
document应该是string，而不是SqlString。您永远不会返回document，而只会将其转换回string，因此就应该如此。
使用HttpWebRequest代替WebRequest。这样，您就不必偶尔将其转换为HttpWebRequest。
请勿将SqlString输入参数转换为string（例如Convert.ToString(uri)）。所有Sql*类型都有一个Value属性，该属性返回本机.NET类型的值。因此，只需使用uri.Value，依此类推。
不要通过NULL检查Convert.ToString(username) != null输入。所有Sql*类型都有一个IsNull属性可以检查。因此，请使用!username.IsNull。
在保持远程HttpWebRequest连接打开的同时，请勿进行所有文本处理（尤其是与另一个系统联系以进行逐行插入的处理）。在using (WebResponse resp = req.GetResponse())中唯一要做的就是填充document变量。直到您不在最外面的document之外，才对using()的内容进行任何处理。
不要单独插入（即while (i > 1)循环）。他们甚至都没有交易。如果在文档中间出现错误，则将加载部分数据（除非可以执行此过程）。
始终对数据库对象进行模式限定。意思是，JSON_DATA应该是dbo.JSON_DATA（或者如果不是dbo，则使用任何模式）。
在您的connectionString中，您同时具有ID /密码和Trusted_Connection。不要同时使用这两个选项，因为它们是互斥的选项（如果同时使用，则ID /密码将被忽略，仅使用Trusted_Connection）。
请，请不要以sa登录或让您的应用程序以sa登录。那只是在乞求一场灾难。
您要连接到的SQL Server实例与运行此SQLCLR对象的实例不同吗？如果是同一实例，最好将其更改为SqlProcedure，以便可以将Context_Connection=True;用作连接字符串。这是进程内连接，它附加到从其进行调用的会话。
不要使用Parameters.AddWithValue()。馊主意。使用特定的适当数据类型创建SqlParameter。然后通过Parameters将is添加到Add()集合。


可能还有其他问题，但这是显而易见的问题。正如我在第一点所说的那样，您可能在这里过头了。不要试图成为消极的，而只是想避免另一种不良的SQLCLR实现，这通常会导致对此功能非常有用的消极看法。如果要继续这样做，请首先对SQLCLR的工作方式，最佳实践等进行更多研究。一个不错的起点是我在SQL Server Central上针对该主题编写的一系列文章：Stairway to SQLCLR。

或者，另一个选择是使用SQL# SQLCLR库的完整版（我写的）中提供的INET_GetWebPages SQLCLR TVF。这个选项不是免费的，但是它允许您简单地安装Web请求片段，然后只需要在SQLCLR标量UDF中单独解析返回的文档（即使您执行Web请求，这也是最好的方法）函数/存储过程）。实际上，如果要在同一SQL Server实例中插入表，则可以为文档解析器创建SQLCLR TVF，并使用OrderedNest将每个yield return值传递回（以将结果流回）并用作如下：

DECLARE @JSON NVARCHAR(MAX);

SELECT @JSON = [content]
FROM   SQL#.INET_GetWebPages(@uri, .....);

INSERT INTO dbo.JSON_DATA (JSONROW)
  SELECT [column_name]
  FROM   dbo.MyBrokenJsonFixerUpper(@JSON);

祝好运！