SQL汇编WebResponse和字符串解析非常缓慢

So I am quickly learning the ways of C# (complete noob that inherited this problem); I have written up the following code which calls a web service that returns JSON that is not always well-formed. The mission here is to take the JSON string and break it into array segments which get inserted into an SQL table for further parsing and testing. I.e. if the return string was something like

所以我很快就学会了c#(继承了这个问题的完整noob);我编写了以下代码，该代码调用返回的web服务并不总是格式良好的JSON。这里的任务是获取JSON字符串并将其分解为数组段，将其插入到SQL表中以进行进一步的解析和测试。也就是说，如果返回的字符串是这样的

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

then the rows would be:

那么行是:

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

This is .NET 3.0 and SQL Server 2008 R2 (Legacy stuff). Here is my working code:

这是。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;
        }                   
  }
}

While it works, it is INCREDIBLY slow; 4+ minutes to write 1500 rows to the server. Once daily this will need to write ~60,000 records in; the rest of the time it will be maybe 100 records POSTED and returned (I haven't worked up the POST part yet). I'm sure there are plenty of things I am doing not-so-proper here that are causing issues, but I have absolutely no idea where to even begin. I was excited enough that I could get the right response out of this! Any ideas/thoughts/help/sympathy would be greatly appreciated.

当它起作用的时候，它非常缓慢;将1500行写入服务器需要4+分钟。每天这将需要写~ 60000条记录;剩下的时间里，可能会有100条记录被发布和返回(我还没有对POST部分进行优化)。我确信有很多事情我在这里做的不太恰当，这些都是造成问题的原因，但我完全不知道从哪里开始。我很兴奋，我能从这件事中得到正确的回应!如有任何想法/想法/帮助/同情，我们将不胜感激。

2 个解决方案

#1

There are several problems here, not the least of which is that it appears you have posted your "sa" password to these here public interwebs. Here are the code issues that I see:

这里有几个问题，其中最重要的一点是您似乎已经将您的“sa”密码发送到这些公共网络。以下是我看到的代码问题:

1. While it is possible to do Web Services calls in SQLCLR, it is definitely an advanced topic, full of pitfalls. This is not something that should be undertaken by a novice / beginner in SQLCLR, which itself is already a nuanced subset of regular .NET programming.
2. 虽然可以在SQLCLR中执行Web服务调用，但它绝对是一个高级主题，充满了陷阱。这不是SQLCLR新手/初学者应该做的事情，SQLCLR本身就是常规。net编程的一个微妙的子集。
3. Get rid of the SqlPipe line and the comment line above it. Functions do not pass data back to the caller via SqlPipe; that is for Stored Procedures.
4. 去掉SqlPipe行和上面的注释行。函数不通过SqlPipe将数据返回给调用者;这是存储过程。
5. You probably shouldn't be using WebRequest
6. 你可能不应该使用WebRequest
7. document should be string, not SqlString. You never return document and only ever convert it back to string, so it should just be that.
8. 文档应该是字符串，而不是SqlString。您永远不会返回文档，只会将它转换回字符串，所以应该是这样。
9. Use HttpWebRequest instead of WebRequest. This way you won't have to occasionally cast it into HttpWebRequest.
10. 使用HttpWebRequest而不是WebRequest。这样，您就不必偶尔将它转换为HttpWebRequest。
11. Don't convert the SqlString input parameters into string (e.g. Convert.ToString(uri)). All Sql* types have a Value property that returns the value in the native .NET type. So instead just use uri.Value, and so on.
12. 不要将SqlString输入参数转换为string(例如:Convert.ToString(uri)))。所有Sql*类型都有一个值属性，该属性返回本机. net类型中的值。因此，只需使用uri。值,等等。
13. Don't check for NULL inputs via Convert.ToString(username) != null. All Sql* type have an IsNull property that you can check. So instead use !username.IsNull.
14. 不要通过Convert.ToString(用户名)!= NULL来检查空输入。所有Sql*类型都有一个可以检查的IsNull属性。所以使用! username.IsNull。
15. DO NOT do all of your text processing (especially processing that contacts another system to do row-by-row inserts) while keeping the remote HttpWebRequest connection open. The only thing you should be doing within the using (WebResponse resp = req.GetResponse()) is populating the document variable. Don't do any processing of the contents of document until you are outside of that outer-most using().
16. 不要在保持远程HttpWebRequest连接打开的同时进行所有的文本处理(特别是连接另一个系统进行逐行插入的处理)。在using (WebResponse resp = req.GetResponse())中惟一应该做的事情是填充文档变量。不要对文档的内容进行任何处理，直到您超出了最外层的using()。
17. Don't do individual inserts (i.e. the while (i > 1) loop). They aren't even in a transaction. If you get an error in the middle of the document, you will have loaded partial data (unless that's ok for this process).
18. 不要单独插入(即while (i > 1)循环)。它们甚至不是交易。如果在文档中间出现错误，就会加载部分数据(除非这个过程没问题)。
19. ALWAYS Schema-qualify database objects. Meaning, JSON_DATA should be dbo.JSON_DATA (or whatever Schema is being used if not dbo).
20. 总是Schema-qualify数据库对象。也就是说，JSON_DATA应该是dbo。JSON_DATA(或正在使用的任何模式，如果不是dbo)。
21. In your connectionString you have both Id/Password and Trusted_Connection. Don't use both as they are mutually exclusive options (if you have both, the Id/Password are ignored and only Trusted_Connection is used).
22. 在connectionString中，您同时拥有Id/密码和Trusted_Connection。不要同时使用它们，因为它们是互斥的选项(如果两者都有，那么Id/密码就会被忽略，只使用Trusted_Connection)。
23. Please, please do not log in as sa or have your application log in as sa. That is just begging for a disaster.
24. 请不要以sa的形式登录或以sa的形式登录您的应用程序。这是在乞求一场灾难。
25. Are you connecting to a different instance of SQL Server than this SQLCLR object is running on? If it is the same instance, you might be better off changing this into a SqlProcedure so that you can use Context_Connection=True; as the connection string. That is the in-process connection that attaches to the session that it is being called from.
26. 您连接的SQL Server实例是否与正在运行的SQLCLR对象不同?如果是相同的实例，最好将其更改为SqlProcedure，以便使用Context_Connection=True;连接字符串。这是连接到会话的进程内连接，它被调用。
27. Don't use Parameters.AddWithValue(). Bad idea. Create the SqlParameter with the specific, and appropriate, datatype. Then add is to the Parameters collection via Add().
28. 不要使用Parameters.AddWithValue()。坏主意。使用特定且适当的数据类型创建SqlParameter。然后通过add()将add添加到参数集合中。

There might be other issues, but these were the obvious ones. As I said in point #1, you might be in over your head here. Not trying to be negative, just trying to avoid another poor implementation of SQLCLR that often leads to negative views of this otherwise very useful feature. If you want to pursue this, then please do more research first into how SQLCLR works, best practices, etc. A good place to start is a series that I am writing on this topic on SQL Server Central: Stairway to SQLCLR.

可能还有其他问题，但这些都是显而易见的。正如我在第1点所说的，你可能在这里。不是试图否定，而是试图避免SQLCLR的另一种糟糕实现，这种实现常常导致对这个非常有用的特性的负面看法。如果您想继续研究这个问题，那么请首先对SQLCLR的工作方式、最佳实践等进行更多的研究。

Or, another option is to use the INET_GetWebPages SQLCLR TVF that is available in the Full version of the SQL# SQLCLR library (which I wrote). This option is not free, but it would allow you to simply install the web request piece and then you just need to parse the returned document separately in a SQLCLR scalar UDF (which is probably the best approach anyway, even if you do the web request function / stored procedure on your own). In fact, if you are inserting into a table in the same Instance of SQL Server, you can create a SQLCLR TVF for the document parser and pass each OrderedNest value back using yield return (to stream the results back) and use as follows:

或者，另一种选择是使用INET_GetWebPages SQLCLR TVF，它可以在SQL# SQLCLR库的完整版本中使用(我编写的)。这个选项不是免费的,但它将允许你简单地安装web请求的块,然后你只需要解析返回的文档分别在SQLCLR标量UDF(这可能是最好的方法,即使你的web请求函数自己/存储过程)。事实上，如果您在SQL Server的同一个实例中插入一个表，您可以为文档解析器创建一个SQLCLR TVF，并使用yield return(将结果流回)将OrderedNest的每个值传回，并使用如下方法:

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);

Good luck!

好运！

#2

I am marking this question answered as it has become clear that what was need is a rewrite and rethinking of my original script. @Solomon Rutzky upvoted for providing helpful information which pointed me to this conclusion. For those interested here is the rewrite:

我在这个问题上做了标记，因为它已经变得清楚了，需要的是重写和重新思考我的原始脚本。@Solomon Rutzky投票赞成提供有用的信息，这让我得出了这个结论。对于有兴趣的人，这里是重写:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Globalization;
// Other things we need for WebRequest
using System.Net;
using System.Text;
using System.IO;
using System.Text.RegularExpressions;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ApiParser(SqlString uri, SqlString user, SqlString pwd, SqlString postd)
    {
        // Create an SqlPipe to send data back to the caller
        SqlPipe pipe = SqlContext.Pipe;
        //Make sure we have a url to process
        if (uri.IsNull || uri.Value.Trim() == string.Empty)
        {
            pipe.Send("uri cannot be empty");
            return;
        }
    try
    {
        //Create our datatable and get the table structure from the database
        DataTable table = new DataTable();
        string connectionString = null;
        //connectionString = "Data source= 192.168.0.5; Database=Administration; Trusted_Connection=True;";
        connectionString = "Data Source=(localdb)\\ProjectsV12;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
        using (SqlConnection gts = new SqlConnection(connectionString))
        {
            gts.Open();
            using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT TOP 0 * FROM sp_WebSvcs.dbo.JSON_DATA", gts))
            {
                adapter.Fill(table);
            }
        }

        // Send a message string back to the client.
        pipe.Send("Beginning Api Call...");
        String json = "";
        // Set up the request, including authentication
        WebRequest req = HttpWebRequest.Create(uri.Value);
        if (!user.IsNull & user.Value != "")
        {
            req.Credentials = new NetworkCredential(user.Value, pwd.Value);
        }
        ((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())
            {

                using (StreamReader rdr = new StreamReader(dataStream))
                {
                    json = (String)rdr.ReadToEnd();

                    rdr.Close();
                }

                // Close up everything...
                dataStream.Close();
            }
            resp.Close();

        }//end using resp
        pipe.Send("Api Call complete; Parsing returned data...");
        int i = 0;
        String h = "";
        String l = "";
        int s = 0;
        int p = 0;
        int b = 0;
        int payload = 0;
        foreach (string line in json.Split(new[] { "}," }, StringSplitOptions.None))
        {
            if (line != "")
            {
                l = line;
                i = l.Replace("{", "").Length + 1;
                p = l.LastIndexOf("{");
                if (line.Length > i) //we find this at the beginning of a group of arrays
                {

                    h = line.Substring(0, p - 1);
                    s = Math.Max(0, h.LastIndexOf("{"));
                    if (h.Length > s && s != 0)
                    /*We have a nested array that has more than one level.
                     *This should only occur at the beginning of new array group.
                     *Advance the payload counter and get the correct string from line.*/
                    {
                        payload++;
                        l = line.Substring(s, line.Length - s);
                    }


                    h = (s >= 0) ? h.Substring(0, s) : h;
                    //=============
                    /*At this point 'h' is a nest collection. Split and add to table.*/
                    string[] OrderedNest = h.Split('{');
                    for (int z = 0; z < OrderedNest.Length; z++)
                    {
                        if (OrderedNest[z] != "")
                        {
                            table.Rows.Add(payload, "{" + OrderedNest[z].Replace(":", "").Replace("[","").Replace("]","") + "}");
                        }
                    }
                    //=============

                }
                else
                {
                    h = null;
                }
                //at this point the first character in the row should be a "{"; If not we need to add one.
                if (l[0].ToString() != "{")
                {
                    l = "{" + l;
                }

                if (l.Replace("{", "").Length != l.Replace("}", "").Length) //opening and closing braces don't match; match the closing to the opening
                {
                    l = l.Replace("}", "");

                    b = l.Length - l.Replace("{", "").Length;

                    l = l + new String('}', b);
                }
                table.Rows.Add(payload, l.Replace("\\\"", "").Replace("\\", "").Replace("]","").Replace("[",""));

            }
        }
        //====

        using (SqlConnection cnn = new SqlConnection(connectionString))
        {
            cnn.Open();
            using (SqlBulkCopy copy = new SqlBulkCopy(cnn))
            {
                copy.DestinationTableName = "sp_WebSvcs.dbo.JSON_DATA";
                copy.WriteToServer(table);
            }
        }
        //====

    } //end try
    catch (Exception e)
    {
        pipe.Send("We have a problem!");
        throw new Exception("\n\n" + e.Message + "\n\n");
    }
    pipe.Send("Parsing complete");

}

}

}