Function vs Procedure
-
Hi All,
Happy Friday to All.
Is this a good practice to write functions with 1000+ lines. I attended one SQLSaturday event and one of the experts from Microsoft was giving advises to use Procs instead of functions. If the function is more than 5-10 lines it is better to replace it with a procedure.thought he did not give any supporting reasons but wanted to know a bit more on this.
If there is any related articles / advise available please do share.
Thanks.
-
I typed "function v stored procedure" into my favourite search engine and got a whole load of results. You could do the same.
I'm not sure what your expert was getting at. It's not about length - it's about which is the more appropriate to use. Sure, if you have a an object definition that's over 1000 lines long, you may find that you can modularise it by breaking it into component parts. This makes it simpler to understand each individual part and reduces the amount of maintenance needed when you need to make a change.
John
- SQL-DBA-01 - Friday, July 21, 2017 7:38 AM
As with all else, "IT DEPENDS". What is even more important is the type of function being used. Scalar will be very bad for this. mTVF will be very, very bad for this. iTVF might be OK depending on how it was written and what it's being used for.
Having looked at many of the system stored procedures, I'd have to say that you should take such advice from MS with a grain of salt.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - SQL-DBA-01 - Friday, July 21, 2017 7:38 AM
I think it's bad practice to write any single chunk of code that is 1000+ lines regardless of what it is, if you can't segment it out at that point you're already doing something wrong.
-
Using a stored procedure will greatly reduce that traffic because the stored procedure call will always be much shorter. Also, stored procedures are easier to trace in Profiler or any other tool. A stored procedure is an actual object in your database. That means it's much easier to get performance statistics on a stored procedure than on an ad-hoc query and, in turn, find performance issues and draw out anomalies.
In addition, stored procedures parameterize more consistently. This means you’re more likely to reuse your execution plans and even deal with caching issues, which can be difficult to pin down with ad-hoc queries. Stored procedures also make it much easier to deal with edge cases and even add auditing or change-locking behavior. A stored procedure can handle many tasks that trouble ad-hoc queries.
Can all the above tasks being performed similarly using functions? When I look at the Execution plan of Function, it looks difficult to read through and the blocks just point about the function name.
Thanks.
- SQL-DBA-01 - Friday, July 21, 2017 8:00 AM
What KIND of function? If you not using an iTVF, you stand a pretty good chance of having performance challenged code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This is one of the questions I ask while interviewing people for a SQL Developer position.
The importance of understanding the difference between a function and a stored procedure is fundamental for development. They both serve different purposes. As mentioned by Jeff, some functions while have a performance hit that you usually want to avoid. But most important, is to understand when to use a hammer and when to use a screwdriver. - SQL-DBA-01 - Friday, July 21, 2017 8:00 AM
This quote seems largely irrelevant to the topic being discussed, because it is comparing stored procs to ad hoc queries, not to functions.
Or are you referring to functions in C# code, rather than SQL Server functions?The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Luis Cazares - Friday, July 21, 2017 10:09 AM
There are also times when you need to use a very special screw-hammer or nail-driver. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Friday, July 21, 2017 1:59 PM
That's why we have the hammer drill, but sometimes you don't want to give power tools to people who don't know how to use them 😛
- Luis Cazares - Friday, July 21, 2017 2:24 PM
Heh... true enough. Otherwise, you might end up with something that looks like this...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply