时间戳

首页 > 解决方案 > 优化存储过程查询

firebird - 优化存储过程查询

问题描述

我在 Firebird 2.5 db 中有 2 个表：

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

我需要从第一个表中获取 ID_PERS 和 ID_SUMSP，其中第二个表中 S_SPORS_[ID_SUMSP] 为 0，ID_SUMSP 为 1 到 7，在 SL_BRUTS 中，一个人只有一条记录

我有一个存储过程：

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

它正在工作，但我想知道是否有更好的解决方案，例如，也许我可以使用 LIST(..).. 或者我可以检查表 2 中的所有 1-7 字段单个查询

标签： firebird

解决方案

您必须更改第二个表的结构（架构） - 阅读 Martin Gruber 的“基本 SQL”或任何其他有关“数据库规范化”的教程。SL_BRUTS 表应重新成型为具有三列：ID_PERS 和 ID_SUMSP 和 S_SPORS

进行一次性数据转换，然后对规范化表进行查询。您在 SL_BRUTS 中创建的结构永远不会允许您进行任何有效的查询，但最微不足道。

检查下面最后两个查询中的查询执行计划：

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

版本 | 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 行受影响










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 行受影响










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





违反表“SL_RAWS”上的 FOREIGN KEY 约束“IMPOSSIBLE_OVER_SL_BROKEN”外键引用目标不存在有问题的键值是（“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 行受影响










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 行受影响










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





违反表“SL_RAWS”上的 FOREIGN KEY 约束“IMPOSSIBLE_OVER_SL_BROKEN”外键引用目标不存在有问题的键值是（“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 行受影响










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





6 行受影响










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<>在这里摆弄










推荐阅读