stored procedures
-
Anyone:
Are stored procedures precompiled or NOT? Someone told be they sit memory but still need to be parsed every time? What's the deal? Should I just use C++?
rgrds
-
Stored procedures are "precompiled". From BoL:
[Stored Procedures] allow faster execution.
If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are created, and an in-memory version of the procedure can be used after the procedure is executed the first time. Transact-SQL statements repeatedly sent from the client each time they run are compiled and optimized every time they are executed by SQL Server.
------------------------------- And --------
Even standard Transact-SQL statements with no business logic component gain a performance advantage when packaged as stored procedures with parameters. Transact-SQL statements compiled into stored procedures can save a significant amount of processing at execution time.
---------------
The other person is incorrect, unless your stored procedure uses Dynamic SQL (e.g.
Set @SQL = 'Select top ' + @Rows + ' FROM ' + @Table + ' ORDER BY ' @OrderBy
EXEC @SQL -
yes, but it sounds like they still need to be parsed. checkout where this guy says: "On the other hand, it isn't compiled prior to use. Each query or line of T-SQL code runs under an interpreter, making it less than ideal for complex number crunching or other manipulations that benefit from compilation. Since SQL Server 7, stored procedures and user-defined functions (UDFs) aren't precompiled or stored in an intermediate parsed-tree format prior to use, as in earlier versions."
Follow this link for the full article:
http://www.ftponline.com/vsm/2004_01/magazine/features/beauchemin/
In general, if it needs to be interpreted it's not compiled.
________________________________________
quote:
Stored procedures are "precompiled". From BoL:[Stored Procedures] allow faster execution.
If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are created, and an in-memory version of the procedure can be used after the procedure is executed the first time. Transact-SQL statements repeatedly sent from the client each time they run are compiled and optimized every time they are executed by SQL Server.
------------------------------- And --------
Even standard Transact-SQL statements with no business logic component gain a performance advantage when packaged as stored procedures with parameters. Transact-SQL statements compiled into stored procedures can save a significant amount of processing at execution time.
---------------
The other person is incorrect, unless your stored procedure uses Dynamic SQL (e.g.
Set @SQL = 'Select top ' + @Rows + ' FROM ' + @Table + ' ORDER BY ' @OrderBy
EXEC @SQL -
Yes procedures need to be compiled prior to execution .. but such a compiled plan (query plan) will stay in the procedure cache and will be reused or aged out used depending on the calls to the procedure , a single pre-compiled plan does not work for procedures because the query plan needs to address factors like changing index statistics , in fact once the query plan is loaded in memory , there is no guarantee that the plan will not be recompiled once in the cache.
Stored procedures or T-SQL in general cannot be beaten when it comes to retrieving data , whereas langauges like C++ will be always faster in processing data .
-
and yes,
The procedural code within sp's is interpreted , the query plan only contains the execution tree for the SQLs (SELECT ,INSERT etc)
-
Just a couple of thoughts.
Whether stored procedures are precompiled or not is not relevant.
It *might* be that the first execution of a procedure takes a little bit longer than the following, because it needs to be cached. But once it is in cache it will be readily available for subsequent use. For further reading I suggest 'Executing Batches or What's Stored About a Stored Procedure?' in Inside SQL Server or even better you should ask this question on the MS newsgroups, Kalen Delaney hangs out there and might give you some kind of ultimate answer.
I'm curious how you will implement a set based approach in C++, rather than row by row processing. SQL is the premier choice today when it comes to talk to databases. It's not all about performance. Stored Procedures offer other advantages which outweight by far the *maybe existing* lack of processing power.
I have developed a lot of stuff in C++, but I have never been so crazy to develop a db app or even an extended stored procedure in C++. I always valued my finger (and their health) more than writing in a cool language.
But Yukon and the use of .Net languages will surely change this. For better or worse.
And the age-old discussion whether compiled code is superior to interpreted code...
We won't solve this here.
Years ago you heard: 'Use C or C++ to have rather unlimited processing power'.
???
Today I doubt this. I doubt this since VB 5 came out.
And with the average desktop computer or server I doubt you will notice a significant difference, if at all.
So, enough for now..time for the second cup of coffee
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply