首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >更快地查询我的Firebird select过程

更快地查询我的Firebird select过程
EN

Stack Overflow用户
提问于 2017-10-26 08:15:38
回答 1查看 309关注 0票数 0

大家好,如何在这个特定的select过程上有一个更快的查询?有没有办法让它更快?我的select过程由4个表和111个字段组成。

--查询性能

准备: 16毫秒

执行时间: 16毫秒

平均获取时间:0毫秒

-内存

当前:4.47MB

最大:4.48MB

缓冲区数: 75

-表操作

表名: EMP_TABLE

Idx读取数:0

非idx读取数: 8869

更新:0

删除数:0

插入:0

表名: POSITION_TABLE

Idx阅读量: 8868

非idx读取数:0

更新:0

删除数:0

插入:0

表名: SCHOOL_TABLE

Idx阅读量: 17734

非idx读取数:0

更新:0

删除数:0

插入:0

代码语言:javascript
复制
    CREATE PROCEDURE EMP_TABLE_MAINLIST_COMPLETE
RETURNS(
  EMP_PK SMALLINT,
  EMP_SURNAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_FIRSTNAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_MIDDLENAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_SUFFIXNAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  FULLNAME VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  POSITION_PK SMALLINT,
  WHAT_POSITION VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  POSITION_SHORT VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  POSITION_CLASS_REF SMALLINT,
  POSITION_CLASS VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  POSITION_SG SMALLINT,
  POSITION_INCRMNT VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  POSITION_ANNUALSALARY DECIMAL(12, 2),
  POSITION_MNTHLYSLRY DECIMAL(12, 2),
  EMP_STATUS VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_FUNDSRC VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  SCHOOL_PK1 SMALLINT,
  SCHOOLASSGND_NAME VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  SCHOOLASSGND_DISTRICT VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  SCHOOL_PK2 SMALLINT,
  SCHOOLPLNTLLA_NAME VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  SCHOOLPLNTLLA_DISTRICT VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_LEVEL VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_MAJOR VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_TRACK VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_STRAND VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_SUBJTTAUGHT VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_ORIGPLNTLANUM VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_ORIGAPPDAY SMALLINT,
  EMP_ORIGAPPMONTH SMALLINT,
  EMP_ORIGAPPYEAR SMALLINT,
  DATE_ORIGAPPNTMNT VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_PLNTLLANUM VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_APPDAY SMALLINT,
  EMP_APPMONTH SMALLINT,
  EMP_APPYEAR SMALLINT,
  DATE_APPNTMNT VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_BDAY SMALLINT,
  EMP_BMONTH SMALLINT,
  EMP_BYEAR SMALLINT,
  DATE_BIRTH VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_BPLACE VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_SEX VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_CIVILSTAT VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_CTZNSHP VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_RELIGION VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_ETHNICITY VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_HEIGHT VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_WEIGHT VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_BLOOD VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_GSISID VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_PAGIBIGID VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_PHILHEALTH VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_SSS VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_RESDNTIALPROV VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_RESDNTIALMUNCITY VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_RESDNTIALBRGY VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_RESDNTIALPRKSTRT VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  ADDRESSRESDNTIAL VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_RESDNTIALZIPCODE VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_RESDNTIALTEL VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_PERMNENTPROV VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_PERMNENTMUNCITY VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_PERMNENTBRGY VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_PERMNENTPRKSTRT VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  ADDRESSPERMNENT VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_PERMNENTZIPCODE VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_PERMNENTTEL VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_EMAILADD VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_CELLPHONE VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_AGENCYEMPNO VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_TIN VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_SPOUSESURNAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_SPOUSEFIRSTNAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_SPOUSEMDDLNAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_SPOUSEOCCUPATION VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_SPOUSEEMPLOYER VARCHAR(100) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_SPOUSEBUSADD VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_SPOUSETELNO VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_FATHERSURNAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_FATHERFIRSTNAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_FATHERMIDDLENAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_MOTHERSURNAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_MOTHERFIRSTNAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_MOTHERMIDDLENAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_CTCNO VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_CTCISSUEDAT VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_CTCISSUEDON DATE,
  EMP_36ACON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_36ARSN VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_36BCON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_36BRSN VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_37ACON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_37ARSN VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_37BCON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_37BRSN VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_38CON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_38RSN VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_39CON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_39RSN VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_40CON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_40RSN VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_41ACON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_41ARSN VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_41BCON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_41BRSN VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_41CCON VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_41CRSN VARCHAR(200) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  EMP_DATEACC DATE,
  EMP_UPDATETIME TIMESTAMP)
AS
BEGIN
  FOR
    SELECT
      A.EMP_PK,
      A.EMP_SURNAME,
      A.EMP_FIRSTNAME,
      A.EMP_MIDDLENAME,
      A.EMP_SUFFIXNAME,
      A.EMP_SURNAME || ', ' || A.EMP_FIRSTNAME || ' ' || A.EMP_MIDDLENAME || ' '||A.EMP_SUFFIXNAME,
      A.POSITION_PK,
      D.POSITION_NAME,
      D.POSITION_SHORT,
      D.POSITION_CLASS_REF,
      D.POSITION_CLASS,
      D.POSITION_SG,
      D.POSITION_INCRMNT,
      D.POSITION_ANNUALSALARY,
      D.POSITION_MNTHLYSLRY,
      A.EMP_STATUS,
      A.EMP_FUNDSRC,
      A.SCHOOL_PK1, 
      B.SCHOOL_NAME,
      B.SCHOOL_DISTRICT,
      A.SCHOOL_PK2,
      C.SCHOOL_NAME,
      C.SCHOOL_DISTRICT,
      A.EMP_LEVEL,
      A.EMP_MAJOR,
      A.EMP_TRACK,
      A.EMP_STRAND,
      A.EMP_SUBJTTAUGHT,    
      A.EMP_ORIGPLNTLANUM,
      A.EMP_ORIGAPPDAY,
      A.EMP_ORIGAPPMONTH,
      A.EMP_ORIGAPPYEAR,
      A.EMP_ORIGAPPDAY || '/' || A.EMP_ORIGAPPMONTH || '/' || A.EMP_ORIGAPPYEAR,
      A.EMP_PLNTLLANUM,
      A.EMP_APPDAY,
      A.EMP_APPMONTH,
      A.EMP_APPYEAR,
      A.EMP_APPDAY || '/' || A.EMP_APPMONTH || '/' || A.EMP_APPYEAR,
      A.EMP_BDAY,
      A.EMP_BMONTH,
      A.EMP_BYEAR,
      A.EMP_BDAY || '/' || A.EMP_BMONTH || '/' || A.EMP_BYEAR,
      A.EMP_BPLACE,
      A.EMP_SEX,
      A.EMP_CIVILSTAT,
      A.EMP_CTZNSHP,
      A.EMP_RELIGION,
      A.EMP_ETHNICITY,
      A.EMP_HEIGHT,
      A.EMP_WEIGHT,
      A.EMP_BLOOD,
      A.EMP_GSISID,
      A.EMP_PAGIBIGID,
      A.EMP_PHILHEALTH,
      A.EMP_SSS,
      A.EMP_RESDNTIALPROV,
      A.EMP_RESDNTIALMUNCITY,
      A.EMP_RESDNTIALBRGY,
      A.EMP_RESDNTIALPRKSTRT,
      A.EMP_RESDNTIALPRKSTRT || ', ' || A.EMP_RESDNTIALBRGY || ', ' || A.EMP_RESDNTIALMUNCITY || ', ' || A.EMP_RESDNTIALPROV,
      A.EMP_RESDNTIALZIPCODE,
      A.EMP_RESDNTIALTEL,
      A.EMP_PERMNENTPROV,
      A.EMP_PERMNENTMUNCITY,
      A.EMP_PERMNENTBRGY,
      A.EMP_PERMNENTPRKSTRT,
      A.EMP_PERMNENTPRKSTRT || ', ' || A.EMP_PERMNENTBRGY || ', ' || A.EMP_PERMNENTMUNCITY || ', ' || A.EMP_PERMNENTPROV,
      A.EMP_PERMNENTZIPCODE,
      A.EMP_PERMNENTTEL,
      A.EMP_EMAILADD,
      A.EMP_CELLPHONE,
      A.EMP_AGENCYEMPNO,
      A.EMP_TIN,
      A.EMP_SPOUSESURNAME,
      A.EMP_SPOUSEFIRSTNAME,
      A.EMP_SPOUSEMDDLNAME,
      A.EMP_SPOUSEOCCUPATION,
      A.EMP_SPOUSEEMPLOYER,
      A.EMP_SPOUSEBUSADD,
      A.EMP_SPOUSETELNO,
      A.EMP_FATHERSURNAME,
      A.EMP_FATHERFIRSTNAME,
      A.EMP_FATHERMIDDLENAME,
      A.EMP_MOTHERSURNAME,
      A.EMP_MOTHERFIRSTNAME,
      A.EMP_MOTHERMIDDLENAME,
      A.EMP_CTCNO,
      A.EMP_CTCISSUEDAT,
      A.EMP_CTCISSUEDON,
      A.EMP_36ACON,
      A.EMP_36ARSN,
      A.EMP_36BCON,
      A.EMP_36BRSN,
      A.EMP_37ACON,
      A.EMP_37ARSN,
      A.EMP_37BCON,
      A.EMP_37BRSN,
      A.EMP_38CON,
      A.EMP_38RSN,
      A.EMP_39CON,
      A.EMP_39RSN,
      A.EMP_40CON,
      A.EMP_40RSN,
      A.EMP_41ACON,
      A.EMP_41ARSN,
      A.EMP_41BCON,
      A.EMP_41BRSN,
      A.EMP_41CCON,
      A.EMP_41CRSN,
      A.EMP_DATEACC,
      A.EMP_UPDATETIME

    FROM EMP_TABLE A, SCHOOL_TABLE B, SCHOOL_TABLE C, POSITION_TABLE D
    WHERE A.EMP_PK != 0 AND A.SCHOOL_PK1 = B.SCHOOL_PK AND A.SCHOOL_PK2 = C.SCHOOL_PK
    AND A.POSITION_PK = D.POSITION_PK 
    ORDER BY EMP_SURNAME || EMP_FIRSTNAME ASC
    INTO
      :EMP_PK,
      :EMP_SURNAME,
      :EMP_FIRSTNAME,
      :EMP_MIDDLENAME,
      :EMP_SUFFIXNAME,
      :FULLNAME,
      :POSITION_PK,
      :WHAT_POSITION,  
      :POSITION_SHORT, 
      :POSITION_CLASS_REF,
      :POSITION_CLASS,
      :POSITION_SG,
      :POSITION_INCRMNT,
      :POSITION_ANNUALSALARY,
      :POSITION_MNTHLYSLRY,
      :EMP_STATUS,
      :EMP_FUNDSRC,
      :SCHOOL_PK1,
      :SCHOOLASSGND_NAME,
      :SCHOOLASSGND_DISTRICT,
      :SCHOOL_PK2,
      :SCHOOLPLNTLLA_NAME,
      :SCHOOLPLNTLLA_DISTRICT,
      :EMP_LEVEL,
      :EMP_MAJOR,
      :EMP_TRACK,
      :EMP_STRAND,
      :EMP_SUBJTTAUGHT,
      :EMP_ORIGPLNTLANUM,
      :EMP_ORIGAPPDAY,
      :EMP_ORIGAPPMONTH,
      :EMP_ORIGAPPYEAR,
      :DATE_ORIGAPPNTMNT,
      :EMP_PLNTLLANUM,
      :EMP_APPDAY,
      :EMP_APPMONTH,
      :EMP_APPYEAR,
      :DATE_APPNTMNT,
      :EMP_BDAY,
      :EMP_BMONTH,
      :EMP_BYEAR,
      :DATE_BIRTH,
      :EMP_BPLACE,
      :EMP_SEX,
      :EMP_CIVILSTAT,
      :EMP_CTZNSHP,
      :EMP_RELIGION,
      :EMP_ETHNICITY,
      :EMP_HEIGHT,
      :EMP_WEIGHT,
      :EMP_BLOOD,
      :EMP_GSISID,
      :EMP_PAGIBIGID,
      :EMP_PHILHEALTH,
      :EMP_SSS,
      :EMP_RESDNTIALPROV,
      :EMP_RESDNTIALMUNCITY,
      :EMP_RESDNTIALBRGY,
      :EMP_RESDNTIALPRKSTRT,
      :ADDRESSRESDNTIAL,
      :EMP_RESDNTIALZIPCODE,
      :EMP_RESDNTIALTEL,
      :EMP_PERMNENTPROV,
      :EMP_PERMNENTMUNCITY,
      :EMP_PERMNENTBRGY,
      :EMP_PERMNENTPRKSTRT,
      :ADDRESSPERMNENT,
      :EMP_PERMNENTZIPCODE,
      :EMP_PERMNENTTEL,
      :EMP_EMAILADD,
      :EMP_CELLPHONE,
      :EMP_AGENCYEMPNO,
      :EMP_TIN,
      :EMP_SPOUSESURNAME,
      :EMP_SPOUSEFIRSTNAME,
      :EMP_SPOUSEMDDLNAME,
      :EMP_SPOUSEOCCUPATION,
      :EMP_SPOUSEEMPLOYER,
      :EMP_SPOUSEBUSADD,
      :EMP_SPOUSETELNO,
      :EMP_FATHERSURNAME,
      :EMP_FATHERFIRSTNAME,
      :EMP_FATHERMIDDLENAME,
      :EMP_MOTHERSURNAME,
      :EMP_MOTHERFIRSTNAME,
      :EMP_MOTHERMIDDLENAME,
      :EMP_CTCNO,
      :EMP_CTCISSUEDAT,
      :EMP_CTCISSUEDON,
      :EMP_36ACON,
      :EMP_36ARSN,
      :EMP_36BCON,
      :EMP_36BRSN,
      :EMP_37ACON,
      :EMP_37ARSN,
      :EMP_37BCON,
      :EMP_37BRSN,
      :EMP_38CON,
      :EMP_38RSN,
      :EMP_39CON,
      :EMP_39RSN,
      :EMP_40CON,
      :EMP_40RSN,
      :EMP_41ACON,
      :EMP_41ARSN,
      :EMP_41BCON,
      :EMP_41BRSN,
      :EMP_41CCON,
      :EMP_41CRSN,
      :EMP_DATEACC,
      :EMP_UPDATETIME
  DO
    BEGIN
      SUSPEND;
    END
END;
EN

回答 1

Stack Overflow用户

发布于 2017-10-26 09:31:30

变化

代码语言:javascript
复制
ORDER BY EMP_SURNAME || EMP_FIRSTNAME ASC

代码语言:javascript
复制
ORDER BY EMP_SURNAME ASC, EMP_FIRSTNAME ASC

并添加索引EMP_SURNAME、EMP_FIRST_NAME、EMP_PK

如果没有其他事情,它将为您节省一个排序。

但是您似乎没有and选择标准,除了EMP_PK != 0,如果它是主键,那么它似乎没有什么用处?

编辑

代码语言:javascript
复制
CREATE UNIQUE ASC INDEX EMP_NAME_IDX ON EMP_TABLE
(EMP_SURNAME,EMP_FIRST_NAME,EMP_PK)
;

请参阅syntax here

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/46943910

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档