Does parameter passing degrade stored procedure performance? - SQLA

Does parameter passing degrade stored procedure performance?

2009-11-12T17:02:00Z

I've got a complicated calculation that involves several lines of my stored procedure. This is in several places throughout the stored procedure. So I'd like to encapsulate it, unfortunately it references 30+ variables, which would then need to become parameters.

So my question is how much performance degradation do we see if we call procedures with lots of parameters?

This is one of the reasons that I've been advocating records for several years now. In Oracle I'd create a record with the data elements, and just pass one parameter. Or use a package and package level variables.

In SQL Anywhere the best I could come up with was console variables, and I don't know if they add additional overhead or not.

Thanks, Jon

Answer by Zote for Does parameter passing degrade stored procedure performance?

2009-11-12T19:47:29Z

I think that's an question for sybase/ianywhere people. But in MY opinion, parameters have low cost than insert/select. You can try "table way" using Global Temporary Table.

Answer by Breck Carter for Does parameter passing degrade stored procedure performance?

2009-11-13T04:59:09Z

I'm not Sybase/iAnywhere either, but I agree with Zote. I have written many procedures with many parameters over the years, and run the execution profiler many times, and never, ever have I seen simple parameter passing to be a performance bottleneck.

Yes, CALL statements do frequently show up with peaks in the profiler, but as far as I can tell that's because of a [cough] quirk whereby all the time spent inside the procedure is shown twice, once inside the procedure and again totalled up and allocated to the outer CALL.

In Oracle, do you know whether or not using a record is more, or less, efficient, or makes no difference? From actual testing and measurement?

And was your primary reason for using records one of performance, or convenience?

BTW, I'm guessing records or something like it are on iAnywhere's possible-to-do list... for reasons of convenience rather than performance.

Here's my advice: Write your code in the way that's easiest for you, at the same time being reasonably readable and maintainable, and then do a stress (performance) test. If it runs quickly, stop, don't change anything. If it runs slowly, use the profiler to look for bottlenecks. I will bet that 90% of the time will show up against SELECT, INSERT, UPDATE and DELETE statements and the like... stuff that hits the disk.

If you heavily use proxy tables, then that's where 100% of the time will go ( just kidding! :)

Breck Grinning Running And Ducking

PS ...or stuff that locks-and-blocks, that's a performance killer by definition.