SQLA is STILL in BETA           
          
vote up 3 vote down
star
1

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

flag

2 Answers

vote up 3 vote down
check

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.

link|flag
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 Nov 13 at 5:02
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 :) – Breck Carter Nov 13 at 7:46
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. – Jon Greisz Nov 13 at 13:07
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 <table name>%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. :) – Jon Greisz Nov 13 at 13:18
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. – Jon Greisz Nov 13 at 13:19
show 2 more comments
vote up 2 vote down

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.

link|flag

Your Answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.