Does parameter passing degrade stored procedure performance?

[Former Member]

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

[Breck_Carter · Accepted solution]

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.

[Breck_Carter · Accepted solution]

From a code readability and maintainability point of view, parameter lists are by far the best way to communicate between procedures. Any other mechanism serves to hide what's really going on, including records, and globals (CREATE VARIABLE) might be the worst. In the old days this was called "coupling".

[Breck_Carter · Accepted solution]

Hey, guess what? you can't edit your own comments, at least I can't see how. Anyway, I was thinking my rant about globals might have seemed harsh... I am not as dogmatic as that comment might seem. Question for you: How did you like the experience of using lots of CREATE VARIABLE statements? I have only ever used them for special purposes, like passing parameters to triggers ( go ahead, all you Anti-Global people, tell me there's another way to do that 🙂

[Former Member · Accepted solution]

I'm not a big fan of globals for this, especially with create variable. We've used it in a few other places with success. But error handling leaving needs to be real good, or the variables are left active, so it just adds headaches.

In this case we're just trying to clean up some code where we have a complicated 20 line expression in multiple spots within the code. I've converted it to a stored procedure and it seems to be working fine with the parameters, so I'll take your advice and leave it as is. Thanks, Jon.

[Former Member · Accepted solution]

I didn't answer the Oracle question. I have not performed performance testing, so cannot answer that question. I'd say we use the records mostly for convenience. The ability to declare a record with

%ROWTYPE and <cursor name="">%ROWTYPE is very convenient. Especially with cursors as adding to the select no longer means changing the FETCH statements and declaring new variables. But overall it just seems cleaner, you're right it may hide some code, but parameter lists of 60 parameters just seem wrong or something. 🙂

[Former Member · Accepted solution]

I'd still love to know how the parameters are passed, especially strings, and whether it could cause a performance issue. This particular expression is called hundreds of thousand times, so just want to make sure that I'm not going to need to rewrite it once we start load testing it.

[Breck_Carter · Accepted solution]

Can you put together a test case, a loop with a call to an empty procedure, then just run it to see what happens? As for the "how parameters are passed", I suggest posting a new question asking exactly that, leaving out mention of performance; perhaps the folk(s) responsible for that part of the engine will reveal all... that's who you should aim the question at. I know I would love to know the details.

[Breck_Carter · Accepted solution]

Also... please post another new question with the Title "Product Suggestion: Please implement xxx". Do it quickly, and you will be the first to establish a new SQLA question type! ...which will get described in the FAQ. Provide a business case for why you want it (iAnywhere is a for-profit company), maybe a link to the Oracle docs.

[Former Member]

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.