Is it better to run 1000-10000 of command in single SQLCLR Context Connection?

Question

I am using the JOB to continuously watch the table data. Inside the JOB i am calling the SQLCLR SP. SQLCLR SP will run. Before the while loop i will open the SQL connection. Inside for loop i will access the database 1000-10000 times in only one connection. I wont close the DB connection untill my work is done.

            SqlConnection connection = null;
            try
            {
                using (connection = new SqlConnection("context connection=true"))
                {
                    connection.Open();

                    DataTable dt;
                    SqlDataAdapter adp=new SqlDataAdapter("select * from tableName",CnStr);
                    DataSet ds=new DataSet();
                    adp.Fill(ds,"TableName");
                    dt= ds[0];

                    //dt.Rows.count may be range from 1000-10000

                    for(i=0;i<dt.Rows.count;i++)
                    {
                        int id = int.Parse(dt.Rows[i][0].ToString());

                        SqlCommand command = new SqlCommand("select * from table1 where IsParsed=0 and Id=" + id, connection);
                        SqlDataReader r1 = command.ExecuteReader();

                        SqlCommand command = new SqlCommand("Insert into table2 (values)", connection);
                        int r2 = command.ExecuteNonQuery();

                        //Always get table1 data which has IsParsed=0. Get those rows manipulate those rows data and 
                        // insert into datatable table2 and update those rows to IsParsed=1

                        SqlCommand command = new SqlCommand("Update table1 set IsParsed=1 where  id=@id", connection);
                        int r3 = command.ExecuteNonQuery();

                        // Run the Billing Logic here

                        // Insert into Billing Table 
                        SqlCommand command = new SqlCommand("Insert into Billing(values)", connection);
                        int r2 = command.ExecuteNonQuery();

                    }
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                connection.close();
            }

Is there any problem with this approach let me know? Is there any issue with using the connection like this? Provide proper suggestion..

I gone through the article better way to Execute multiple commands in single connection

Here I am using the Context Connection and executing the thousands of command in single connection. Is there any consideration of Connection pool in context connection..? How about the performance of single command execution for each connection vs multiple command execution with single connection?

Also I want to know that in both cases like context connection and regular connection yields to same result? because the SP is deployed in DB itself. If I wrong please correct me.

Is this an example of what you are trying to achieve, or the real thing? Why get row by row into code and insert/update that row? Why not do something like this with a single SQL query? I am assuming we are not seeing the full picture in your example? — krembanan, Mar 15 '13 at 10:52
It is jus sample. Actually that is a big logic there. Total 6 tables involved there. 1st Table maintains the RawData which Has IsParsed column. I am retrieving the Rows which have IsParsed=0. I will get set of results. On each row i will run the Parser for RawData. After parsing the Row i ll get the real data. That parsed data i ll insert into Actual Table. On that data i perform Billing based on tariff of that consumer. Once billing done i insert the data into Billing Table. Based on billing Balance i ll send notification to consumer. The notifications i ll insert in another table. — Pravin, Mar 15 '13 at 11:23

score 3 · Accepted Answer · edited Mar 16 '13 at 09:31

There is no problem in executing large number of queries over a single connection. Any how you are using a SQL CLR Procedure with context connection. As mentioned in MSDN it states that:

using the context connection typically results in better performance and less resource usage. The context connection is an in-process–only connection, so it can contact the server "directly" by bypassing the network protocol and transport layers to send Transact-SQL statements and receive results. The authentication process is bypassed, as well.

Please refer this link for more information on context and regular connection.

score 2 · Answer 2 · answered Mar 16 '13 at 05:31

2

No. It is fine to execute a large number of queries over a single connection.

Your code would likely perform worse if you were to open/close a connection to run those three SQL queries for each of those 1000+ rows.

answered Mar 16 '13 at 05:31

Ryan

2,948
3
30
41

3

Thanks Ryan. If u have any other approach/suggestion let me know and Also I want to know that in both cases like context connection and regular connection yields to same performance result? – Pravin Mar 16 '13 at 09:04
1

You should probably post that as a second question, but in general, a context connection would probably perform faster. That and I think a regular connection would require the assembly to have EXTERNAL_ACCESS permissions. – Ryan Mar 16 '13 at 14:59

Is it better to run 1000-10000 of command in single SQLCLR Context Connection?

2 Answers2