.Net's ExecuteNonQuery versus MS SQL Direct Query

Question

I have a SQL script that will refresh the dependent views of a table once the table has been modified, like adding new fields. The script will be run thru ExecuteNonQuery, see example below.

 Using refreshCommand As New SqlClient.SqlCommand("EXEC RefreshDependentViews  'Customer','admin',0", SqlClient.SqlConnection, SqlClient.SqlTransaction)

    refreshCommand.ExecuteNonQuery()

End Using

The above code when executed will take 4-5 seconds, but when I copy the script only and run it through MS SQL directly, it only takes 2-3 seconds.

My question is, why they have different intervals?

Please note that the MS SQL server is on my PC itself and also the code.

Thanks

Make sure you flush all buffers, caches, etc. before making performance comparisons (e.g. see this [question](http://stackoverflow.com/q/564717/21567)). — Christian.K, Aug 27 '13 at 11:37
How are you timing this? If you use SQL profiler you may find the actual execution of the query may be matching and its just the application code which is adding the extra time. — Squirrel5853, Aug 27 '13 at 11:46

score 0 · Answer 1 · answered Aug 27 '13 at 11:18

SqlClient and SSMS have different connection-level options (SET options) by default, which can sometimes be a factor. I also wonder what the isolation level is for the two things, which could be compounded if you are using TransactionScope etc in your code.There could also simply be different system load at the time. Basically, hard to say just from that: but there are indeed some things that can impact this.

.Net's ExecuteNonQuery versus MS SQL Direct Query

1 Answers1