CODEWITHSUNDEEP
sql-server
Lanston
Lanston

Reputation: 11844

Direct Sql or combine it in a procedure? which is more efficient

in my recent subject ,I have to do some queries through dynamic SQL,But I'm curious about the efficiency in different ways:

1)combine the sql sentences in my server and then send them to the database ,do the query

2)send my variables to database and combine them in a certain procedure and finally do the query

Hope someone can help
BTW(I use .Net and Sqlserver)

Upvotes: 1

Views: 74

Answers (2)

AdaTheDev
AdaTheDev

Reputation: 147224

Firstly, one of the main things you should do is to parameterise your SQL - whether that be by wrapping it up as a stored procedure in the DB, or by creating the SQL statement in your application code and then firing the whole thing in to the DB. This will mean:

  • prevention against SQL injection attacks by not directly concatenating user-entered values into a SQL statement
  • execution plan reuse (subsequent executions of that query, regardless of parameter values, will be able to reuse the original execution plan) (NB. this could be done if not parameterised yourself, via Forced Parameterisation)

Stored procedures do offer some extra advantages:

  • security ,only need to grant EXECUTE permissions to the stored procedures, you don't need to grant the user direct access to underlying db tables

  • maintainability, a change to a query does not involve an application code change, you can just change the sproc in the DB

  • network traffic, not necessarily a major point but you're sending less over the wire especially if the query is pretty large/complex

Personally, I use stored procedures most of the time. Though the times I need to build up SQL dynamically in application code, it is always parameterised.

Upvotes: 2

Ravi Vanapalli
Ravi Vanapalli

Reputation: 9942

Best is to use stored procedure and pass parameters from your application, as Stored procedures are precompiled queries and have execution plan ready which saves lot of time.

You can refer this url which has details http://mukund.wordpress.com/2005/10/14/advantages-and-disadvantages-of-stored-procedure/

Happy coding!!

Upvotes: 0

Related Questions