Optimize stored procedure query

Question

I have 2 tables in a Firebird 2.5 db:

SL_SPORS
-----------------
ID_PERS  ID_SUMSP
   10     2
   10     3
   11     2
   

SL_BRUTS
----------------------------------------
ID_PERS  S_SPORS_1  S_SPORS_2  S_SPORS_3 ...
   10     0            50         0
   11     0             0         0

I need to get ID_PERS and ID_SUMSP from first table where S_SPORS_[ID_SUMSP] is 0 in second table,ID_SUMSP is from 1 to 7,in SL_BRUTS there is only one record for a person

I have a stored procedure:

CREATE OR ALTER PROCEDURE SP_VALIDSUM()
 RETURNS (RES VARCHAR(500)) AS
 ....
 FOR SELECT A.ID_PERS,S.ID_SUMSP FROM SL_SPORS INTO :ID_PERS,:ID_SUMSP DO
  BEGIN
    SUMA=0;
   EXECUTE STATEMENT 'SELECT FIRST 1 S_SPORS_'||:ID_SUMSP||' FROM SL_BRUTS WHERE ID_PERS='||:ID_PERS INTO :SUMA;
  IF (SUMA=0) THEN
   BEGIN
    RES='SUM 0 FOR ID_PERS='||:ID_PERS||' AND ID_SUMSP='||:ID_SUMSP;" 
    SUSPEND;
   END
 END

it is working but I want to know if there is a better solution,for example maybe I can load all ID_SUMSP from first table in single query using LIST(..)..or I can check all 1-7 fields from table 2 on a single query

Answer 1

You have to change the structure (Schema) of the second table - read Martin Gruber's "Essential SQL" or any other tutorial on "database normalization". SL_BRUTS table should be re-molded to have three columns: ID_PERS and ID_SUMSP and S_SPORS

• https://en.wikipedia.org/wiki/Database_normalization

Do one-time data conversion and then do your queries over normalized tables. The structure you made in SL_BRUTS would never allow you any efficient queries but most trivial.

Check the QUERY EXECUTION PLAN in the last two queries below:

select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version
     , rdb$character_set_name
from rdb$database;

VERSION | RDB$CHARACTER_SET_NAME                                                                                                      
:------ | :---------------------------------------------------------------------------------------------------------------------------
3.0.5   | UTF8                                                                                                                        

create table SL_SPORS (
  ID_PERS  integer,
  ID_SPORS integer,
  Primary key (ID_PERS, ID_SPORS)
)

✓

insert into SL_SPORS 
select 10, 2 from rdb$database union all
select 10, 3 from rdb$database union all
select 11, 2 from rdb$database union all
select 20, 3 from rdb$database 

4 rows affected

select * from sl_spors

ID_PERS | ID_SPORS
------: | -------:
     10 |        2
     10 |        3
     11 |        2
     20 |        3

-- CAN this sl_spors_NNN data be NULL ? or not ? what is semantics, what is meaning of it???
create table SL_BROKEN (
  ID_PERS  integer primary key,
  s_SPORS_1 integer NOT NULL,  
  s_SPORS_2 integer NOT NULL,
  s_SPORS_3 integer NOT NULL
)

✓

create table SL_RAWS (
  ID_PERS  integer,
  ID_SPORS integer,
  S_SPOR integer NOT NULL,
  Primary key (ID_PERS, ID_SPORS),
  constraint impossible_over_SL_BROKEN 
     FOREIGN KEY(ID_PERS, ID_SPORS)
     REFERENCES SL_SPORS(ID_PERS, ID_SPORS)
)

✓

-- should throw error over non-existing PERSON - but would it ???
insert into SL_BROKEN
values (-100, 20, 30, 40)

1 rows affected

-- should throw error over non-existing PERSON - and it would!
insert into SL_RAWS
values (-100, 20, 30)

violation of FOREIGN KEY constraint "IMPOSSIBLE_OVER_SL_BROKEN" on table "SL_RAWS" Foreign key reference target does not exist Problematic key value is ("ID_PERS" = -100, "ID_SPORS" = 20)

insert into  SL_BROKEN 
select 10, 0, 50, 0 from rdb$database union all
select 11, 0,  0, 0 from rdb$database

2 rows affected

select * from SL_BROKEN 

ID_PERS | S_SPORS_1 | S_SPORS_2 | S_SPORS_3
------: | --------: | --------: | --------:
   -100 |        20 |        30 |        40
     10 |         0 |        50 |         0
     11 |         0 |         0 |         0

delete from SL_BROKEN where ID_PERS < 0

1 rows affected

create view SL_TRANSPOSE as
SELECT 1 as ID_SPORS, ID_PERS, S_SPORS_1 as S_SPOR from SL_BROKEN  
  union all
SELECT 2 as ID_SPORS, ID_PERS, S_SPORS_2 as S_SPOR from SL_BROKEN  
  union all
SELECT 3 as ID_SPORS, ID_PERS, S_SPORS_3 as S_SPOR from SL_BROKEN  

✓

select * from SL_TRANSPOSE

ID_SPORS | ID_PERS | S_SPOR
-------: | ------: | -----:
       1 |      10 |      0
       1 |      11 |      0
       2 |      10 |     50
       2 |      11 |      0
       3 |      10 |      0
       3 |      11 |      0

insert into SL_RAWS (ID_PERS, ID_SPORS, S_SPOR)
select ID_PERS, ID_SPORS, S_SPOR from SL_TRANSPOSE

violation of FOREIGN KEY constraint "IMPOSSIBLE_OVER_SL_BROKEN" on table "SL_RAWS" Foreign key reference target does not exist Problematic key value is ("ID_PERS" = 10, "ID_SPORS" = 1)

insert into SL_SPORS 
select 10, 1 from rdb$database union all
select 11, 3 from rdb$database union all
select 11, 1 from rdb$database 

3 rows affected

insert into SL_RAWS (ID_PERS, ID_SPORS, S_SPOR)
select ID_PERS, ID_SPORS, S_SPOR from SL_TRANSPOSE

6 rows affected

select * from SL_RAWS

ID_PERS | ID_SPORS | S_SPOR
------: | -------: | -----:
     10 |        1 |      0
     11 |        1 |      0
     10 |        2 |     50
     11 |        2 |      0
     10 |        3 |      0
     11 |        3 |      0

select * from SL_RAWS where S_SPOR = 0  -- where S_SPOR IS NULL

ID_PERS | ID_SPORS | S_SPOR
------: | -------: | -----:
     10 |        1 |      0
     11 |        1 |      0
     11 |        2 |      0
     10 |        3 |      0
     11 |        3 |      0

select * from SL_TRANSPOSE where S_SPOR = 0  -- where S_SPOR IS NULL

ID_SPORS | ID_PERS | S_SPOR
-------: | ------: | -----:
       1 |      10 |      0
       1 |      11 |      0
       2 |      11 |      0
       3 |      10 |      0
       3 |      11 |      0

db<>fiddle here