Faster Query on my Firebird select procedure

Question

Good day everyone, how to have a faster query on this specific select procedure? Is there any way to make it faster? my select procedure consist of 4 tables and 111 fields.

------ QUERY PERFORMANCE ------

Prepare : 16 ms

Execute : 16 ms

Avg fetch time: 0 ms

----------- MEMORY ------------

Current : 4.47 MB

Max : 4.48 MB

Buffers : 75

------ TABLE OPERATIONS -------

Table name : EMP_TABLE

Idx reads : 0

Non-idx reads : 8869

Updates : 0

Deletes : 0

Inserts : 0

Table name : POSITION_TABLE

Idx reads : 8868

Non-idx reads : 0

Updates : 0

Deletes : 0

Inserts : 0

Table name : SCHOOL_TABLE

Idx reads : 17734

Non-idx reads : 0

Updates : 0

Deletes : 0

Inserts : 0

    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;

Answer 1

Change

ORDER BY EMP_SURNAME || EMP_FIRSTNAME ASC

to

ORDER BY EMP_SURNAME ASC, EMP_FIRSTNAME ASC

And add an index EMP_SURNAME, EMP_FIRST_NAME, EMP_PK

It would save you a sort if nothing else.

But you don't seem to have and selection criteria except EMP_PK != 0 which seems of little use if it is the primary key?

EDIT

CREATE UNIQUE ASC INDEX EMP_NAME_IDX ON EMP_TABLE
(EMP_SURNAME,EMP_FIRST_NAME,EMP_PK)
;

See syntax here