Re: Efficiency question with PL/SQL code.

Sean wrote:
>
> Here is the scenario I can not figure out which is best. I want to
> create a PL/SQL stored procedure/pkg for performance gains. I have a
> table named foobar with columns A B C and D (for lack of a better
> example):
>
> foobar
> ------------
> A
> B
> C
> D
>
> I want to be able to execute the procedure passing in parameters
> telling the procedure WHICH columns to return like if I wanted just two
> columns (say A and C) vs three columns (A C and D).
>
> I could create a SQL statement and use the Dynamic SQL package, but is
> there a better way to do this. In C++, you can pass parameters in
> (like &H000 & H0001) and find out what the user was specifying.
>
> Is there a slick way to do this?
> Thanks for you help,

I think this is something that a lot of programmers don't understand. If you have a table with four columns, there is no performance gain by only selecting one column because the engine still has to do as much work as if you requested all four columns.

What I would probably do in this type of situation is have a user-defined recordset anchored on the table/view if it only involves a single table/view or on the cursor if the cursor is a join. When rows are fetched from the cursor the values would be placed in this recordset and then I'd programatically determine which columns I want to use at any discrete point in my program. The big advantage to this is that if the algorithm changes in the future it will be easier to maintain the program. Received on Fri Mar 31 2000 - 06:46:30 CST