Solved

How much faster are stored procedures really? Are ad-hoc queries a better engineered method?

Posted on 2004-10-13
5
327 Views
Last Modified: 2008-03-10
My question relates to stored procedures on SQL Server 2000

Hi, I am studying for an MCAD qualification and have been experimenting with SQL server access from ASP.NET. The books I am studying and stuff I have read elsewhere all say that I should use stored procedures rather than ad-hoc queries in almost all cases as the performance is better.

Does anyone know of any benchmark data that measures how significant this performance increase is? Reading the books, they suggest that the difference is because the T-SQL statements can be precompiled. Compiling a few lines of SQL doesn't sound to me to be a hugel processing task anyway. I am wondering if the performance increase from stored procedures is something likely to make a noticeable difference to an app, or is just one of thse performance tweaks where the difference is pretty immeasurable.

Just looking at it as a newby, it seems to me that there is a disadvantage to stored procedures from an engineering complexity point of view as it means splitting the business logic between ASP.NET components and the database. I guess it also means rewriting big chunks of an application if we ever changed brands of database.  

If the performance increase from using stored procedures is only marginal, shouldn't we use adhoc queries instead.

Anybody agree / disagree , thoughts?
0
Comment
Question by:molar
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12296213
Ok, couple of points to note here...

1)  The compilation process in SQL server is unlike that of a 3GL.  Compilation not only involves physically intepretting the commands but also formulating a strategy for executing those commands ( it is the latter that takes the time).

2) ", shouldn't we use adhoc queries instead."  Absolutely not.   Why?   A number of reasons, but prodominantly Increased security because with SPs you dont need to grant table permissions, and client abstraction (ie:  the client doesnt need to know about the specifics of how to get the data, joins etc.) thus meaning that the underlying database schema chan be modified without having to modify and redistribute the client application.
0
 
LVL 13

Expert Comment

by:danblake
ID: 12296250
Ok, let me help you out here from some known items....

Does anyone know of any benchmark data that measures how significant this performance increase is?
The largest amount of performance increase would be in the following areas:
1) Reuse of the query plan (/data as in sitting in memory -- easier to retrieve on demand via sp than dynamic sql) -- compilation and storage of data used for query
2) Not requiring to send the dynamic sql from ASP.NET web server -> database server. (Smaller overhead by just passing sp_Execute <sp_name> @params)
3) Nice easy, clean debugging supported.  With dynamic sql it can be a pain to translate and fix the procedures involved.
4) Secure.  Why ? You are not sending all the dynamic sql direct back to the database for operations, this is hidden within the sproc.  Permissions are targeted at the sproc level.
5) The query plan is known every time, and can be saved easily and debugged with relative ease and checked between bug-fixs/sps to ensure a high-level of performance is still present

Reading the books, they suggest that the difference is because the T-SQL statements can be precompiled. Compiling a few lines of SQL doesn't sound to me to be a hugel processing task anyway.
--> (It does also depend on how big your dynamic sql-text is, this may involve significant bandwidth if you are sending all your commands down the line and then the time required to compile the dynamic sql -- this can easily take 30s to compile a complex command)
I am wondering if the performance increase from stored procedures is something likely to make a noticeable difference to an app, or is just one of thse performance tweaks where the difference is pretty immeasurable.

Just looking at it as a newby, it seems to me that there is a disadvantage to stored procedures from an engineering complexity point of view as it means splitting the business logic between ASP.NET components and the database. I guess it also means rewriting big chunks of an application if we ever changed brands of database.  
--> How often do you really change databases anyway ?... You can always conform to ANSI-92 to ensure that your code logic and sps will transfer from one database to another. -- most customers I am aware of have changed from VB4 ...VB6, VB.NET faster than they change databases.   If you do change databases anyway you normally have to translate the whole thing as datatypes/structures may have to be changed as they are not the same from one database version to another.

If the performance increase from using stored procedures is only marginal, shouldn't we use adhoc queries instead.
--> Depends on exactly what you are doing, what your architecture is, and what resources are available to you.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12296325
" it seems to me that there is a disadvantage to stored procedures from an engineering complexity point of view as it means splitting the business logic between ASP.NET components and the database"

I would say that this point is only valid if you wish to take a pure theoretical point of view on a 3 tier system.      Lets face it, its a fact of life that Theorists come up with lots of wonderful ideas which slow thing down.  Take for example Object orientation,   Great idea and has its uses but it sucks for many things.  

Real successful systems are developed by practitioners not theorists.
0
 
LVL 2

Author Comment

by:molar
ID: 12306060
Hmmm.

"The largest amount of performance increase would be in the following areas:
1) Reuse of the query plan (/data as in sitting in memory -- easier to retrieve on demand via sp than dynamic sql) -- compilation and storage of data used for query"

Are you saying that using a stored procedure allows the server to use cached data in memory. If so I can see that has a real performance boost. If not, are you saying that it takes a long time to create the query plan?

Regarding the points about network bandwidth and security, I suppose I'm thinking of an ASP.NET scenario here where the database "client" is really the Web Server. In this kind of situation I would probably expect the SQL Server and Web server to be a few feet apart in a secure server room with maybe a gigabit connection between them, so the case for better security / less bandwidth would seem to be considerably weaker than in a classic client/server environment. If we had a 500 byte SQL query rather than a 20 byte procedure name, we're only talking about one extra TCP/IP packet here after all.

Maybe I'm being naive about the size of stored procedures in real world apps here. I had really only imagined stored procedures being 2 or 3 lines of SQL. Are they typically much larger than that?

0
 
LVL 13

Accepted Solution

by:
danblake earned 125 total points
ID: 12306097
Most normal SPs I have come across are at least 20-30 lines long, many exceeding 2000 lines or sub-calling other SPs.
When you then consider you require 10,000 connections or 300,000 connections bandwidth could becomes an issue if you are sending the dynamic sql all the time and the query results back to the web-client interface.  Like said, it depends on what exactly you are designing and for what purpose.


Are you saying that using a stored procedure allows the server to use cached data in memory
--> Yes a SP does.  It also places the SPs results and caches them into memory for reuse (as long as your parameters don't significantly change).
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now