Question :
I’m wondering which of the following approaches is best?
1 – Add a file *.sql as Resource , then run SqlCommand with CommandType.Text .
2 – Add a Store Procedure to the Database, then run the SqlCommand with 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 Parameters of the SqlCommand list and we do not have SQL and C # mixed within the *.cs file.
In this scenario, is it faster to run the StoreProcedude or Text ?
Answer :
In this scenario is it faster to execute the StoredProcedure or Text ?
StoredProdecure is faster. >, using Text causes the object to call sp_executesql , which creates certain overhead to create the parameters and more the order and fill checks that the command needs to run.
In addition, if the parameters are optional, in case of Text it is necessary to pass all the parameters, even if the optional ones are not filled .