Which is faster simple query Or Sql stored Procedure

Posted by Ermahesh2009 under ASP.NET on 7/6/2012 | Points: 10 | Views : 14345 | Status : [Member] | Replies : 9
Dear All

In my new project i want to write query and execute instead of making stored procedure.
my goal is to fill combo box with key_value pair so that's a simple select query
like in country combo box my query is
select country_id,country_name
from countryMaster
so for this purpose which is better for fast execution is procedure or direct query execution .
thanks in advance


Posted by: Kamalakanta.Nayak09 on: 7/6/2012 [Member] Starter | Points: 25

store procedure will run faster


Ermahesh2009, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Johnbhatt on: 7/6/2012 [Member] Starter | Points: 25

Stored Procedure is Command that is stored in Database Directly, Time Taken in case of Simple Text Query, you will send command text from Application to Database+Execution Time+Return time. But In case of Stored Procedure Time Taken for Execution+Return Time. So these Are Fast.

But When you Delete Database, and Create a new your Stored Procedures will be Deleted.

John Bhatt
Glad to Know, Free to Share.....

Ermahesh2009, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patel28rajendra on: 7/6/2012 [Member] Starter | Points: 25


Answer to your question is Store Procedure Because store procedure has already been executed Where as Query first compile and then execute at particular time So Store Procedure is Faster Than Query.


R D Patel

Ermahesh2009, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ajay.Kalol on: 7/6/2012 [Member] Starter | Points: 25

Store Procedure


Ermahesh2009, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 7/6/2012 [Member] Bronze | Points: 25

Always stored procedure is faster than general query.

Thanks and Regards

Ermahesh2009, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muralidosscm on: 7/6/2012 [Member] Starter | Points: 25

Stored procedure is faster than simple query

Reasons for using stored procedures:

Reduce network traffic -- you have to send the SQL statement across the network. With sprocs, you can execute SQL in batches, which is also more efficient.
Caching query plan -- the first time the sproc is executed, SQL Server creates an execution plan, which is cached for reuse. This is particularly performant for small queries run frequently.
Ability to use output parameters -- if you send inline SQL that returns one row, you can only get back a recordset. With sprocs you can get them back as output parameters, which is considerably faster.
Permissions -- when you send inline SQL, you have to grant permissions on the table(s) to the user, which is granting much more access than merely granting permission to execute a sproc
Separation of logic -- remove the SQL-generating code and segregate it in the database.
Ability to edit without recompiling -- this can be controversial. You can edit the SQL in a sproc without having to recompile the application.
Find where a table is used -- with sprocs, if you want to find all SQL statements referencing a particular table, you can export the sproc code and search it. This is much easier than trying to find it in code.
Optimization -- It's easier for a DBA to optimize the SQL and tune the database when sprocs are used. It's easier to find missing indexes and such.
SQL injection attacks -- properly written inline SQL can defend against attacks, but sprocs are better for this protection.

Muralidoss M

Ermahesh2009, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Mallesh on: 7/6/2012 [Member] Starter | Points: 25

Hi friend,
I want to tell something about your question in my point of view as in three aspects.

1. In Maintainability Purpose:
Someone who specializes in database programming may create stored procedures. This allows the application developer to concentrate on the code instead of SQL code. You can modify stored procedures independently of the program source code, the application doesn't have to be recompiled when/if the SQL is altered. If the operation requires a large amount of SQL code or is performed repetitively, stored procedures can be faster than batches of SQL code. According to web application span, stored procedures should be better to integrated with native code in more manageable and maintainable way of manner. But a single instance(line) of query you need not apply any procedures.

2. In Network (Connection) Purpose:
Store procedure execute server side that's why it reduce network traffic. SQL query also execute on server also but if you have big query then it will take more time comparison to Procedure to traverse from client side to server.If you have a slow connection to the sql server, and large queries, you would probably be better off using stored procedures, as they generate less network traffic, and usually less roundtrips between the server and the client.

3. In Execution (Speed) Purpose:
Each time a SQL statement from the client is run, server compiles, optimizes, and executes the statement. Where as stored procedure is compiled when it is submitted for the first time and this compiled content is stored in something called procedure cache, for subsequent calls. Then no compilation is requires that execute the same batch of sql code. Means, the stored procedures are compiled only once, unless specified, and the execution plan is stored, so a restart of the server wouldn't loose it, just execute & getting better performance than query.

4. And, you can grant user permission to execute a stored procedure even if they don't have permission to execute the procedure's statements directly. This provides better security and performance to the application.

I hope you can understand that...!
According to your requirement choose your weapon.

Happy Coding... !
--Malleswara Rao. ?(..)?

== Mallesh

Ermahesh2009, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gaur1982 on: 7/6/2012 [Member] Starter | Points: 25

The historical performance benefit of stored procs have generally been from the following (in no particular order):

Pre-parsed SQL
Pre-generated query execution plan
Reduced network latency
Potential cache benefits

Pre-parsed SQL -- similar benefits to compiled vs. interpreted code, except on a very micro level.

Still an advantage? Not very noticeable at all on the modern CPU, but if you are sending a single SQL statement that is VERY large eleventy-billion times a second, the parsing overhead can add up.

Pre-generated query execution plan. If you have many JOINs the permutations can grow quite unmanageable (modern optimizers have limits and cut-offs for performance reasons). It is not unknown for very complicated SQL to have distinct, measurable (I've seen a complicated query take 10+ seconds just to generate a plan, before we tweaked the DBMS) latencies due to the optimizer trying to figure out the "near best" execution plan. Stored procedures will, generally, store this in memory so you can avoid this overhead.

Still an advantage? Most DBMS' (the latest editions) will cache the query plans for INDIVIDUAL SQL statements, greatly reducing the performance differential between stored procs and ad hoc SQL. There are some caveats and cases in which this isn't the case, so you'll need to test on your target DBMS.

Also, more and more DBMS allow you to provide optimizer path plans (abstract query plans) to significantly reduce optimization time (for both ad hoc and stored procedure SQL!!).

Reduced network latency A) If you are running the same SQL over and over -- and the SQL adds up to many KB of code -- replacing that with a simple "exec foobar" can really add up. B) Stored procs can be used to move procedural code into the DBMS. This saves shuffling large amounts of data off to the client only to have it send a trickle of info back (or none at all!). Analogous to doing a JOIN in the DBMS vs. in your code (everyone's favorite WTF!)

Still an advantage? A) Modern 1Gb (and 10Gb and up!) Ethernet really make this negligible. B) Depends on how saturated your network is -- why shove several megabytes of data back and forth for no good reason?

Potential cache benefits Performing server-side transforms of data can potentially be faster if you have sufficient memory on the DBMS and the data you need is in memory of the server.

Still an advantage? Unless your app has shared memory access to DBMS data, the edge will always be to stored procs.

Of course, no discussion of Stored Procedure optimization would be complete without a discussion of parameterized and ad hoc SQL.

Parameterized / Prepared SQL
Kind of a cross between stored procedures and ad hoc SQL, they are embedded SQL statements in a host language that uses "parameters" for query values, e.g.:

SELECT .. FROM yourtable WHERE foo = ? AND bar = ?

These provide a more generalized version of a query that modern-day optimizers can use to cache (and re-use) the query execution plan, resulting in much of the performance benefit of stored procedures.

Ad Hoc SQL Just open a console window to your DBMS and type in a SQL statement. In the past, these were the "worst" performers (on average) since the DBMS had no way of pre-optimizing the queries as in the parameterized/stored proc method.

Still a disadvantage? Not necessarily. Most DBMS have the ability to "abstract" ad hoc SQL into parameterized versions -- thus more or less negating the difference between the two. Some do this implicitly or must be enabled with a command setting (SQL server: http://msdn.microsoft.com/en-us/library/ms175037.aspx , Oracle: http://www.praetoriate.com/oracle_tips_cursor_sharing.htm).

Lessons learned? Moore's law continues to march on and DBMS optimizers, with every release, get more sophisticated. Sure, you can place every single silly teeny SQL statement inside a stored proc, but just know that the programmers working on optimizers are very smart and are continually looking for ways to improve performance. Eventually (if it's not here already) ad hoc SQL performance will become indistinguishable (on average!) from stored procedure performance, so any sort of massive stored procedure use ** solely for "performance reasons"** sure sounds like premature optimization to me.

Anyway, I think if you avoid the edge cases and have fairly vanilla SQL, you won't notice a difference between ad hoc and stored procedures.

Gaurav Agrawal

Ermahesh2009, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bhupentiwari on: 7/9/2012 [Member] Starter | Points: 25

Offcourse SP.. beacuse it is pre compiled query

Thanks n Regards
Bhupendra Tiwari

Ermahesh2009, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response