cancel
Showing results for 
Search instead for 
Did you mean: 

Function vs. Stored Procedure

Former Member
0 Kudos

Some of our developers use functions as you would stored procedures, i.e., for database updates/inserts/deletes, the creation of temporary tables, and just plain returning the value of a computation. My question is whether ASA is more efficient at performing DML in a procedure than a function, and if using these two interchangeably is a bad idea. Granted, if OUT variables or a result set is required then a procedure is the only choice. I prefer to keep them separated in their functionality.

Accepted Solutions (1)

Accepted Solutions (1)

former_member188493
Contributor
0 Kudos

When comparing alternatives, only a volume test in a real-world environment will determine which is faster.

Having said that, assuming that neither the function call nor the procedure call is combined with other operations in a single statement, and assuming the function is actually called (function caching does not happen), there should be no difference...

SET @variable = f ( whatever );

versus

CALL p ( whatever );

Those are big assumptions, however... as is the assumption you're using SQL Anywhere 16... tell us more.

Former Member
0 Kudos

Well... using SQL Anywhere 11. I don't have a concern with performance, per se, I'm just wondering if it's an acceptable practice to use functions to do the work of stored procedures. Other DBMS's create more of a distinction between the two.

It all boils down to 'best practices', I guess. We don't have any set guidelines for developing database functions and such; it seems to be at the developers' whim. However, those of us who have come from an Oracle environment tend to keep the two separate.

former_member188493
Contributor
0 Kudos

In SQL Anywhere a block is a block is a block... the code you can write inside a BEGIN END block is pretty much the same whether it is a procedure, function, event, trigger or just a BEGIN END all by itself. For example, you can create a string containing a BEGIN END block in an application program and send it to SQL Anywhere for execution.

There are differences in what special features you can use in triggers versus events versus procedures, etc., but they are tiny differences when compared with the vast and confusing differences in some other DBMS's. In SQL Anywhere they're all in the same league; in some other products, they aren't even playing the same sport :]

My Oracle skills are rusty... let me have a look... one moment please... OK, they're discussed together in the docs, I'm not seeing much in the way of idiosyncratic differences between Oracle procedures and functions; were there big differences in earlier versions? I know lots of Oracle shops have [cough] interesting rules like "never code triggers".

Anyway, choosing between coding a procedure and function in SQL Anywhere is much the same as choosing in an application programming language... you code a function if you need a function, otherwise you code a procedure... with one person's "need" being another person's "want" :]

Answers (0)