467,170 Members | 1,116 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,170 developers. It's quick & easy.

Performance of extended stored procedures in SQL Server 2000

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.

Jul 23 '05 #1
  • viewed: 1544
Share:
1 Reply
(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
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by bsandell@gmail.com | last post: by
1 post views Thread by scanner2001 | last post: by
4 posts views Thread by icebrrrg@gmail.com | last post: by
4 posts views Thread by Frank Villasenor | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.