Performance of extended stored procedures in SQL Server 2000

[smauldin]

What is the overhead of using extended stored procedures?

I created a table with 500,000 rows.
1) I ran a select on two columns and it runs in about 5 seconds.
2) I ran a select on one column and called an UDF (it returns a
constant string) and it takes 10 seconds.
3) I ran a select on one column and called a UDF that calls an extended
stored procedure that returns a string and it takes 65 seconds.
I also tried running test 3 with 4 concurrent clients and each client
takes about 120 seconds.

[Erland Sommarskog]

(sm******@ingrian.com) writes:

What is the overhead of using extended stored procedures?

I created a table with 500,000 rows.
1) I ran a select on two columns and it runs in about 5 seconds.
2) I ran a select on one column and called an UDF (it returns a
constant string) and it takes 10 seconds.
3) I ran a select on one column and called a UDF that calls an extended
stored procedure that returns a string and it takes 65 seconds.
I also tried running test 3 with 4 concurrent clients and each client
takes about 120 seconds.

The overhead is apparently significant in this case. And it may well be
typical. By calling an extended stored procedure, you are completely
serializing the processing. If you rewrote the query as a cursor and
called the XP in the extednded stored procedure, you would probably
see a higher value than 65, but not that much higher.

Thus the overhead is not so much in the XP itself, as in the way
it affects the query.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp