Question about "The inside of the 'UDF' function"

Hi

I know that when i call the store procedure in the sql server 2000, it will take a long time.
But after, if i call the store procedure again, it will run faster than before.

As i know:
Stored procedures are more efficient in part because the procedure is stored in SQL Server when it is created. Therefore, the content in the procedure runs at the server when the stored procedure is executed. A complex Transact-SQL script contained in a stored procedure is called by a single Transact-SQL statement, rather than by sending hundreds of commands over the network.

Before a stored procedure is created, the command syntax is checked for accuracy. If no errors are returned, the procedure's name is stored in the SysObjects table and the procedure's text is stored in the SysComments table. The first time the stored procedure is run, an execution plan is created and the stored procedure is compiled. Subsequent processing of the compiled stored procedure is faster because SQL Server does not recheck command syntax, re-create an execution plan, or recompile the procedure. The cache is checked first for an execution plan before a new plan is created.

My question is that whether the UDF function is the same as the store procedure ?

Can you please narrow down your question a bit. What product are you trying to use? What version of the product?

Is this question for SQL Server Analysis Services?

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward Melomed:

sql 2000 any version

Not for Server Analysis Services

And i found the answer on www.sql-server-performance.com

That's the answer i think:

///////////////////////////////////////////////////////////////////////////////////////////////////////////////

Question
I work in a SQL Server 2000 environment where we have literally hundreds of stored procedures in one database that are utilized for an OLAP application, and likewise for an OLTP application.

I have seen in many instances where a stored procedure is used solely because one can pass parameters to it and use these parameters in the WHERE portion of a SELECT clause. These stored procedures do nothing more than return a result set.

I was also told that SQL Server has a FIFO queue where only a certain number of pre-compiled stored procedures reside. Furthermore, I was also told that I should consider using user-defined functions that does its SELECT from a view(s) and uses any parameters passed in as delimiters in the WHERE portion of the SELECT clause.

Since functions are not pre-compiled but stored procedures are, is there any performance gain from using user-defined functions with views as opposed to stored procedures?

Answer
Before SQL Server 2000, user-defined functions were unavailable. Because of this, stored procedures were often the only way to emulate what a user-defined function can do now. So the question becomes, are there any advantages of converting my current stored procedures to user-defined functions?

I want to break the answer for this question into two parts: performance and convenience issues. Let's start with performance first.

For the most part, rewriting stored procedures as functions will not give you any performance benefits, and quite possibly, they may cause a performance hit because of the extra overhead they incur as compared to stored procedures. The amount of the performance hit, if any, will depend on how the function is written and what it is doing. Keep in mind that user-defined functions are also pre-optimized and compiled similarly to stored procedures (unlike what you have been told). Even so, they have more overhead than corresponding stored procedures.

From a convenience standpoint, there are some reasons to rewrite some stored procedures as user-defined functions, assuming performance is not a major issue. Some of these include:

The ability for a user-defined function to act like a table gives developers the ability to break out complex logic into shorter code blocks. This will generally provides the additional benefit of making the code less complex, and easier to write and maintain.

If you want to be able to invoke a stored procedure directly from within a query, then rewriting a stored procedure as a user-defined function would be worthwhile.