Best practices when using Sqlcommand? Text or Storedprocedure?

Asked

Viewed 127 times

4

I’m wondering which of the following approaches is best?

1 - Add a file *.sql as Resource, then run the Sqlcommand with the CommandType.Text.

2 - Add a Store Procedure to the Database, then run the Sqlcommand with the CommandType.StoredProcedure.

The Resource with SQL and the Store Procudere have the same query and expect the same parameters.

In both cases, the parameters are added to the list of Parameters of Sqlcommand and we don’t have SQL and C# mixed inside the file *.cs.

In this scenario it is faster to run the Storeprocedude or Text?

c# tsql
  • 1

    You can, or better, should measure to know which is the fastest. There is no technical difference that alone determines which is the fastest. The best performance will depend on what this SQL command does and what you do with the result of it.

  • @Caffé, in case it would just read operations returning a list of objects.

1 answer

3


In this scenario it is faster to run the Storedprocedure or Text?

Storedprodecure is faster. According to that Article, use Text makes the object call sp_executesql, which creates certain overhead to create the parameters and more the order and fill checks the command needs to execute.

In addition, if the parameters are optional, for the case of Text it is necessary to pass all parameters, even if the options are not even filled in.

  • thank you, I was missing the question of the overhead generated by sp_executesql.

Browser other questions tagged c# tsql

You are not signed in. Login or sign up in order to post.