On Wed, Jan 30, 2013 at 12:50 PM, Sergio Garcia
<sergiogarciaruso@...>wrote:

> Hi all. I wonder which of these two variants is optimal, the objective
> is to select data depending on the input parameters:
>
> 1 variant - The selection procedure involves all joins:
>
> 2 variant- Create a view, and use them in the procedure:
>
>
> PD: The selection can include up to ten tables. I use FB SS v2.5.1
>

In theory there should be no difference. Non-aggregate views are
transformed into their components and integrated into the statements which
reference them by Firebird's query compiler. Which reminds me of the old
joke: Q. "What's the difference between theory and practice?" A.."In
theory there is no difference." Or in millennial terms: YMMV. If you
want a conclusive answer, IBPhoenix (www.ibphoenix.com) has experts who can
model your application and run tests under various load conditions.

If what you want is a quick, crowd-sourced answer, a simple question beats
a long example. Not in all cases - if you want to know why your procedure
gets the wrong answer, providing the procedure is important.

Good luck,

Ann




1) SET TERM ^;
CREATE PROCEDURE my_select_proc (
param1 TYPE OF COLUMN table1.field1,
param2 TYPE OF COLUMN table2.field1)
RETURNS (
field1_T1 TYPE OF COLUMN table1.field1,
field2_T1 TYPE OF COLUMN table1.field2,
field1_T2 TYPE OF COLUMN table2.field1,
field2_T2 TYPE OF COLUMN table2.field2)
AS
BEGIN
FOR
SELECT
T1.Field1, T1.Field2, T2.Field1, T2.Field2
FROM
table1 T1
INNER JOIN table2 T2
ON T2.id = T1.id
WHERE
T1.Field1 = param1 AND T2.Field2 = param2
INTO
:field1_T1, :field2_T1, :field1_T2, :field2_T2 DO BEGIN
SUSPEND;
END
END^
SET TERM ;^


2)SET TERM ^;
CREATE VIEW my_view (field1_T1, field2_T1, field1_T2, field2_T2)
AS
SELECT
T1.Field1 field1_T1,
T1.Field2 field2_T1,
T2.Field1 field1_T2,
T2.Field2 field2_T2
FROM
table1 T1
INNER JOIN table2 T2
ON T2.id = T1.id;

CREATE PROCEDURE my_select_proc (
param1 TYPE OF COLUMN table1.field1,
param2 TYPE OF COLUMN table2.field1)
RETURNS (
field1_T1 TYPE OF COLUMN table1.field1,
field2_T1 TYPE OF COLUMN table1.field2,
field1_T2 TYPE OF COLUMN table2.field1,
field2_T2 TYPE OF COLUMN table2.field2)
AS
BEGIN
FOR
SELECT
V.field1_T1,
V.field2_T1,
V.field1_T2,
V.field2_T2
FROM
my_view V
WHERE
V.field1_T1 = param1 AND V.field1_T2 = param2
INTO
:field1_T1, :field2_T1, :field1_T2, :field2_T2 DO BEGIN
SUSPEND;
END
END^
SET TERM ;^


[Non-text portions of this message have been removed]