Geek Answers Handbook

Why would the SQLCLR process run slower than the same client client

I am writing a stored procedure that, upon completion, will be used to scan staging tables for dummy data in column by column.

The first step in the exercise was simply to scan the table - this is what the code below does. The problem is that this code works after 5:45 seconds --- however, the same code as the console application (changing the connection string, of course), runs after about 44 seconds.

using (SqlConnection sqlConnection = new SqlConnection("context connection=true")) { sqlConnection.Open(); string sqlText = string.Format("select * from {0}", source_table.Value); int count = 0; using (SqlCommand sqlCommand = new SqlCommand(sqlText, sqlConnection)) { SqlDataReader reader = sqlCommand.ExecuteReader(); while (reader.Read()) count++; SqlDataRecord record = new SqlDataRecord(new SqlMetaData("rowcount", SqlDbType.Int)); SqlContext.Pipe.SendResultsStart(record); record.SetInt32(0, count); SqlContext.Pipe.SendResultsRow(record); SqlContext.Pipe.SendResultsEnd(); } }

However, the same code (different connection string, of course) runs in the console application after about 44 seconds (which is closer to what I expected on the client side)

What I miss on the SP side, which will lead to its slow launch.

Please note: I fully understand that if I need the number of rows, I should use the aggregation count (*) --- this is not the purpose of this exercise.

+2
source share
2 answers

The type of code you write is very susceptible to SQL Injection. Instead of processing the reader like you, you can simply use the RecordsAffected property to find the number of lines in the reader.

EDIT:

After doing some research, the difference you see is the design difference between contextual connection and regular connection. Peter Debetta wrote about this and wrote:

"The contextual connection is written so that it only selects a row at a time, so for each of the 20 million of some odd lines, the code requested each line separately. However, using a non-contextual connection, it requests 8K lines at a time."

http://sqlblog.com/blogs/peter_debetta/archive/2006/07/21/context-connection-is-slow.aspx

+2
source

Well, it seems the answer in the connection chain is after all.

 context connection=true

vs

 server=(local); database=foo; integrated security=true

For some reason, using an "external" connection, SP runs almost as fast as a console application (still not so fast!) 55 seconds)

Of course, now the assembly should be deployed as external, not secure - and this adds more disappointment.

+1
source

More articles:


All Articles