Performance

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

It is my understanding that in SQL2000, stored procedure are no longer
compiled. If that is true is there really any performance advantage to
using sp's for .Net datasets or datareaders?
 
HI Barry,

Err, what?
I think they are still very much compiled :)
Anyway, even if they weren't there are many advantages using them (you can
limit access to database by granting users access only to sps instead
directly to tables, etc).
 
Thanks for Miha's response.

Hi Barry,

The stored procedure is compiled in SQL Server 2000.

When a stored procedure is first executed, the source is compiled into an
execution plan. If the stored procedure is again executed before the
execution plan is aged from memory, the relational engine detects the
existing plan and reuses it. If the plan has aged out of memory, a new plan
is built.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_
ar_sa_7cmm.asp

As a database is changed by such actions as adding indexes or changing data
in indexed columns, the original query plans used to access its tables
should be optimized again by recompiling them.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm
_8_des_07_6cmd.asp

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
In Visual Studio Magazine, Jan 2004, Write .NET CXCoe in SQL Server by Bob
Beauchemin of Developmentor, he states:

"...Since SQL Server 7, stored procdures and user defined functions (UDFs)
aren't precompile or stored in an intermediat parsed-tree format prior to
use, as in earlier version."

That is where I got the idea they are no longer compiled. I will try to
contact him to understand this better.
 
Hi Barry,

Please notice the word wrap. There are 2 lines for each link. Please
conbine the 2 links for the complete link.

The article says that the stored precedures are not precompile which means
they are not compiled before first use. After first use, there will be a
compiled version stored in the database.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top