x

[Closed] Stored procedure optimization

Which one is the best in following :- Using sub query or function in stored procedure?

more ▼

asked Nov 16, 2009 at 02:21 AM in Default

Chandan tomar gravatar image

Chandan tomar
4 1 1 1

I'm tempted to close this question - the answers below ('it depends') pretty much cover it, and anything more specific should really be another question.
Nov 16, 2009 at 08:06 AM Melvyn Harbour 1 ♦♦
I would tend to agree - might be worth giving the guy a while to clarify though?
Nov 16, 2009 at 08:08 AM Matt Whitfield ♦♦
I agree as well Matt. Leave it up for 24 hours to span timezones and give the requestor time to respond and also check for down votes.
Nov 16, 2009 at 10:23 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

The question has been closed Jul 20, 2010 at 05:55 AM by Grant Fritchey for the following reason:


3 answers: sort voted first

It depends on the result? What do you want the subquery or function to return? If function is well written, and as inline, if runs very fast. If you write your subquery basd, you can end up with some real RBAR.

more ▼

answered Nov 16, 2009 at 04:39 AM

Peso gravatar image

Peso
1.6k 5 6 8

(comments are locked)
10|1200 characters needed characters left

You can use both equally poorly or well.

I could write a function which was lightning quick and a sub-query that was slow, or vice versa.

This question, therefore, can only really be answered for a specific scenario, so if you want some help with a specific requirement, then please let us know that requirement and we'll undoubtedly be happy to help.

more ▼

answered Nov 16, 2009 at 06:34 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

What is your criteria for best? Are you looking for performance or maintainability? Functions 'usually' take a performance hit but are easier to reuse.

more ▼

answered Nov 16, 2009 at 11:37 AM

Jeff Oresik gravatar image

Jeff Oresik
138 4 4 6

(comments are locked)
10|1200 characters needed characters left

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x372
x36

asked: Nov 16, 2009 at 02:21 AM

Seen: 1893 times

Last Updated: Nov 16, 2009 at 04:45 AM