Board index » delphi » Delphi 6 - Interbase / Firebird - SQL index Problem Help needed

Delphi 6 - Interbase / Firebird - SQL index Problem Help needed

Dear all,

Here is a interesting problem that someone will be able to help me with.

Here is my SQL

SELECT P.PATIENT_ID,P.FORENAME,P.SURNAME,P.DOB,P.PID
FROM PATIENT P
INNER JOIN PX_MARKETING ON (P.PID = PX_MARKETING.PID)
LEFT OUTER JOIN PX_LETTER ON (PX_MARKETING.PID = PX_LETTER.PID)
WHERE
P.CONTACT_ID <> 3
AND PX_MARKETING.TYPE_ID = 1
AND PX_LETTER.LASTLETTERDATE <= '08/01/03'
AND PX_LETTER.LASTLETTERNO =0
AND P.DOB > '08/01/90'

I have 3 indexes on the patient table.

Index1 on DOB
Index2 on contact_id
Primary key on PID

Now this query using the patient table in it plan

if i remove the "AND P.DOB > '08/01/90' " then no indexes are used on the
patient table

Why and how can i get it to use the Patient table index thought it should
use index2.

Any help would be great

Dean

 

Re:Delphi 6 - Interbase / Firebird - SQL index Problem Help needed


In article <3e2c404...@newsgroups.borland.com>,
i...@thirdeyedevelopment.com says...
Quote
> Any help would be great

        It's difficult to answer your question without seeing the full
metadata and query optimization PLAN for each of the various
configurations of your SQL, but I might point you to the Help for
InterBase PLANalyzer (free; in .signature), which covers such issues in
detail.

        Keep in mind that this Help was written for InterBase, and Firebird
may/may not behave the same way.

        -Craig

--
Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

Re:Delphi 6 - Interbase / Firebird - SQL index Problem Help needed


Thanks for that i am using PLANalyser.

Here is the metadata

Hope this helps

SET SQL DIALECT 3;

CREATE DATABASE '192.168.0.1:\test.gdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096;

/* Generators definitions */

CREATE GENERATOR PATIENT_PID_GEN ;
CREATE GENERATOR PX_MARKETING_ID_GEN ;
SET GENERATOR PATIENT_PID_GEN TO 16818;

SET GENERATOR PX_MARKETING_ID_GEN TO 16299;

/* Tables definitions */

CREATE TABLE PATIENT (
    PID TAUTOINC NOT NULL,
    PATIENT_ID TID NOT NULL,
    PRACTICE_ID TID NOT NULL,
    PXREF TREFERENCE,
    FAMILYGROUP_ID TID,
    RESEARCH_ID TID,
    CONTACT_ID TID,
    RECALL_ID TID,
    GP_ID TID,
    OO_ID TID,
    EVIDENCE_ID TID,
    TITLE TTITLE,
    FORENAME TFIRSTNAME,
    FN_FONETICS TPHONETICS,
    INITIAL TINITIAL,
    SURNAME TSURNAME,
    SN_FONETICS TPHONETICS,
    SUFFIX TSHORTDESCP,
    GENDER TYESNO DEFAULT 'M',
    DOB TDATE,
    TELHOME TPHONE,
    TELWORK TPHONE,
    TELMOBILE TPHONE,
    TELOTHER TPHONE,
    NI TPHONE,
    NHS_NO TPHONE,
    EMAILADDR TMEDDESCP,
    PRINT_LABEL TYESNO DEFAULT 'N',
    PRINT_CARD TYESNO DEFAULT 'N',
    DRIVER TYESNO DEFAULT 'N',
    VDU TYESNO DEFAULT 'N',
    NHS TYESNO DEFAULT 'N',
    CREATEDBY TID,
    CREATEDDATE TDATE,
    MODIFIEDBY TID,
    MODIFIEDDATE TDATE,
    DELETED TYESNO DEFAULT 'N');

CREATE TABLE PX_LETTER (
    PID TID,
    MAILSHOT_ID TID,
    LASTLETTERDATE TDATE,
    LASTLETTERNO TBYTE,
    LASTLETTERSENT TDATE,
    BALANCE TCURRENCY,
    ISACTIVE TYESNO DEFAULT 'N',
    CREATEDBY TID,
    CREATEDDATE TDATE,
    MODIFIEDBY TID,
    MODIFIEDDATE TDATE,
    DELETED TYESNO DEFAULT 'N');

CREATE TABLE PX_MARKETING (
    MARKETING_ID TAUTOINC NOT NULL,
    PID TID,
    TYPE_ID TID,
    LASTDATE TDATE,
    PERIOD TBYTE,
    NEXTDATE TDATE,
    WEEKMONTH TYESNO,
    CREATEDBY TID,
    CREATEDDATE TDATE,
    MODIFIEDBY TID,
    MODIFIEDDATE TDATE,
    DELETED TYESNO DEFAULT 'N');

/* Primary keys definition */

ALTER TABLE PATIENT ADD CONSTRAINT PK_PATIENT PRIMARY KEY (PID);
ALTER TABLE PX_MARKETING ADD CONSTRAINT PK_MARKETING PRIMARY KEY
(MARKETING_ID);

/* Unique keys definition */

ALTER TABLE PATIENT ADD CONSTRAINT UK_PATIENTPRACTICE UNIQUE (PATIENT_ID,
PRACTICE_ID);

/* Indices definition */

CREATE INDEX IDX_PATIENT ON PATIENT (DOB);
CREATE INDEX IDX_PATIENT1 ON PATIENT (CONTACT_ID, PID);
CREATE INDEX IDX_PX_LETTER ON PX_LETTER (LASTLETTERDATE, LASTLETTERNO, PID);
CREATE INDEX IDX_PX_LETTER1 ON PX_LETTER (LASTLETTERDATE);
CREATE INDEX IDX_PX_LETTER2 ON PX_LETTER (PID);
CREATE INDEX IDX_PX_LETTER3 ON PX_LETTER (LASTLETTERNO);
CREATE INDEX IDX_PX_LETTER_1 ON PX_LETTER (PID);
CREATE INDEX IDX_PX_LETTER_2 ON PX_LETTER (MAILSHOT_ID);
CREATE INDEX IDX_PX_LETTER_3 ON PX_LETTER (LASTLETTERDATE);
CREATE INDEX IDX_PX_LETTER_4 ON PX_LETTER (LASTLETTERNO);
CREATE INDEX IDX_PX_LETTER_5 ON PX_LETTER (LASTLETTERDATE, LASTLETTERNO,
PID);
CREATE INDEX IDX_PX_MARKETING ON PX_MARKETING (PID);
CREATE INDEX IDX_PX_MARKETING_1 ON PX_MARKETING (PID);
CREATE INDEX IDX_PX_MARKETING_2 ON PX_MARKETING (TYPE_ID);
CREATE INDEX IDX_PX_MARKETING_4 ON PX_MARKETING (PERIOD);
CREATE INDEX IDX_PX_MARKETING_5 ON PX_MARKETING (TYPE_ID, PID, NEXTDATE);

SET TERM ^ ;

/* Stored procedures definition */

/* Stored Procedure: SP_RECALL */
CREATE PROCEDURE SP_RECALL (
    TYPE_ID INTEGER,
    LASTLETTER DATE,
    FROM_DOB DATE,
    TO_DOB DATE,
    FROM_DATE DATE,
    TO_DATE DATE)
RETURNS (
    LETTERCOUNT INTEGER,
    CONTACT INTEGER,
    LETTERNO INTEGER)
AS
BEGIN

if (:FROM_DOB is not NULL AND :FROM_DATE is NULL and :TO_DATE is NULL) then
begin

       FOR SELECT Count(PX_LETTER.LASTLETTERNO) as
Letters,P.CONTACT_ID,PX_LETTER.LASTLETTERNO
       FROM PX_MARKETING
       RIGHT OUTER JOIN PATIENT P ON (PX_MARKETING.PID = P.PID)
       LEFT OUTER JOIN PX_LETTER ON (P.PID = PX_LETTER.PID)
       WHERE PX_MARKETING.TYPE_ID = :TYPE_ID
             AND PX_LETTER.LASTLETTERDATE <= :LASTLETTER
             AND P.DOB between :FROM_DOB AND :TO_DOB
       GROUP BY P.CONTACT_ID,PX_LETTER.LASTLETTERNO
       INTO :LETTERCOUNT,:CONTACT,:LETTERNO
    DO
       SUSPEND;
end
ELSE if (:FROM_DOB is NULL AND :FROM_DATE is NULL and :TO_DATE is NULL) then
begin

       FOR SELECT Count(PX_LETTER.LASTLETTERNO) as
Letters,P.CONTACT_ID,PX_LETTER.LASTLETTERNO
       FROM PX_MARKETING
       RIGHT OUTER JOIN PATIENT P ON (PX_MARKETING.PID = P.PID)
       LEFT OUTER JOIN PX_LETTER ON (P.PID = PX_LETTER.PID)
       WHERE PX_MARKETING.TYPE_ID = :TYPE_ID
             AND PX_LETTER.LASTLETTERDATE <= :LASTLETTER
       GROUP BY P.CONTACT_ID,PX_LETTER.LASTLETTERNO
       INTO :LETTERCOUNT,:CONTACT,:LETTERNO
    DO
       SUSPEND;
end
ELSE if (:FROM_DOB is NULL AND :FROM_DATE is NOT NULL and :TO_DATE is NULL)
then begin

       FOR SELECT Count(PX_LETTER.LASTLETTERNO) as
Letters,P.CONTACT_ID,PX_LETTER.LASTLETTERNO
       FROM PX_MARKETING
       RIGHT OUTER JOIN PATIENT P ON (PX_MARKETING.PID = P.PID)
       LEFT OUTER JOIN PX_LETTER ON (P.PID = PX_LETTER.PID)
       WHERE PX_MARKETING.TYPE_ID = :TYPE_ID
             AND PX_LETTER.LASTLETTERDATE <= :LASTLETTER
             AND PX_MARKETING.NEXTDATE >= :FROM_DATE
       GROUP BY P.CONTACT_ID,PX_LETTER.LASTLETTERNO
       INTO :LETTERCOUNT,:CONTACT,:LETTERNO
    DO
       SUSPEND;
end
ELSE if (:FROM_DOB is NULL AND :FROM_DATE is NULL and :TO_DATE is NOT NULL)
then begin

       FOR SELECT Count(PX_LETTER.LASTLETTERNO) as
Letters,P.CONTACT_ID,PX_LETTER.LASTLETTERNO
       FROM PX_MARKETING
       RIGHT OUTER JOIN PATIENT P ON (PX_MARKETING.PID = P.PID)
       LEFT OUTER JOIN PX_LETTER ON (P.PID = PX_LETTER.PID)
       WHERE PX_MARKETING.TYPE_ID = :TYPE_ID
             AND PX_LETTER.LASTLETTERDATE <= :LASTLETTER
             AND PX_MARKETING.NEXTDATE <= :TO_DATE
       GROUP BY P.CONTACT_ID,PX_LETTER.LASTLETTERNO
       INTO :LETTERCOUNT,:CONTACT,:LETTERNO
    DO
       SUSPEND;
end
ELSE if (:FROM_DOB is NULL AND :FROM_DATE is NOT NULL and :TO_DATE is NOT
NULL) then begin

       FOR SELECT Count(PX_LETTER.LASTLETTERNO) as
Letters,P.CONTACT_ID,PX_LETTER.LASTLETTERNO
       FROM PX_MARKETING
       RIGHT OUTER JOIN PATIENT P ON (PX_MARKETING.PID = P.PID)
       LEFT OUTER JOIN PX_LETTER ON (P.PID = PX_LETTER.PID)
       WHERE PX_MARKETING.TYPE_ID = :TYPE_ID
             AND PX_LETTER.LASTLETTERDATE <= :LASTLETTER
             AND PX_MARKETING.NEXTDATE  BETWEEN :FROM_DATE AND :TO_DATE
       GROUP BY P.CONTACT_ID,PX_LETTER.LASTLETTERNO
       INTO :LETTERCOUNT,:CONTACT,:LETTERNO
    DO
       SUSPEND;
end
ELSE if (:FROM_DOB is NOT NULL AND :FROM_DATE is NOT NULL and :TO_DATE is
NOT NULL) then begin

       FOR SELECT Count(PX_LETTER.LASTLETTERNO) as
Letters,P.CONTACT_ID,PX_LETTER.LASTLETTERNO
       FROM PX_MARKETING
       RIGHT OUTER JOIN PATIENT P ON (PX_MARKETING.PID = P.PID)
       LEFT OUTER JOIN PX_LETTER ON (P.PID = PX_LETTER.PID)
       WHERE PX_MARKETING.TYPE_ID = :TYPE_ID
             AND PX_LETTER.LASTLETTERDATE <= :LASTLETTER
             AND PX_MARKETING.NEXTDATE  BETWEEN :FROM_DATE AND :TO_DATE
             AND P.DOB between :FROM_DOB AND :TO_DOB
       GROUP BY P.CONTACT_ID,PX_LETTER.LASTLETTERNO
       INTO :LETTERCOUNT,:CONTACT,:LETTERNO
    DO
       SUSPEND;
end
ELSE if (:FROM_DOB is NOT NULL AND :FROM_DATE is NULL and :TO_DATE is NOT
NULL) then begin

       FOR SELECT Count(PX_LETTER.LASTLETTERNO) as
Letters,P.CONTACT_ID,PX_LETTER.LASTLETTERNO
       FROM PX_MARKETING
       RIGHT OUTER JOIN PATIENT P ON (PX_MARKETING.PID = P.PID)
       LEFT OUTER JOIN PX_LETTER ON (P.PID = PX_LETTER.PID)
       WHERE PX_MARKETING.TYPE_ID = :TYPE_ID
             AND PX_LETTER.LASTLETTERDATE <= :LASTLETTER
             AND PX_MARKETING.NEXTDATE  <= :TO_DATE
             AND P.DOB between :FROM_DOB AND :TO_DOB
       GROUP BY P.CONTACT_ID,PX_LETTER.LASTLETTERNO
       INTO :LETTERCOUNT,:CONTACT,:LETTERNO
    DO
       SUSPEND;
end
ELSE if (:FROM_DOB is NOT NULL AND :FROM_DATE is NOT NULL and :TO_DATE is
NULL) then begin

       FOR SELECT Count(PX_LETTER.LASTLETTERNO) as
Letters,P.CONTACT_ID,PX_LETTER.LASTLETTERNO
       FROM PX_MARKETING
       RIGHT OUTER JOIN PATIENT P ON (PX_MARKETING.PID = P.PID)
       LEFT OUTER JOIN PX_LETTER ON (P.PID = PX_LETTER.PID)
       WHERE PX_MARKETING.TYPE_ID = :TYPE_ID
             AND PX_LETTER.LASTLETTERDATE <= :LASTLETTER
             AND PX_MARKETING.NEXTDATE  >= :FROM_DATE
             AND P.DOB between :FROM_DOB AND :TO_DOB
       GROUP BY P.CONTACT_ID,PX_LETTER.LASTLETTERNO
       INTO :LETTERCOUNT,:CONTACT,:LETTERNO
    DO
       SUSPEND;
end
END
^

SET TERM ; ^

SET TERM ^ ;

/* Triggers definition */

/* Trigger: AI_PATIENT_PID */
CREATE TRIGGER AI_PATIENT_PID FOR PATIENT ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.PID IS NULL) THEN
    NEW.PID = GEN_ID(PATIENT_PID_GEN, 1);
END
^

/* Trigger: AI_PX_MARKETING_ID */
CREATE TRIGGER AI_PX_MARKETING_ID FOR PX_MARKETING ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.MARKETING_ID IS NULL) THEN
NEW.MARKETING_ID = GEN_ID(PX_MARKETING_ID_GEN, 1);
END
^

SET TERM ; ^

/* Privileges */

GRANT SELECT, UPDATE, DELETE, INSERT, REFERENCES ON PATIENT TO SYSDBA WITH
GRANT OPTION;
GRANT SELECT, UPDATE, DELETE, INSERT, REFERENCES ON PX_LETTER TO SYSDBA WITH
GRANT OPTION;
GRANT SELECT, UPDATE, DELETE, INSERT, REFERENCES ON PX_MARKETING TO SYSDBA
WITH GRANT OPTION;

GRANT EXECUTE ON PROCEDURE SP_RECALL TO SYSDBA;

"Craig Stuntz [TeamB]" <cstu...@nospamplease.com (but use vertexsoftware to

Quote
email)> wrote in message

news:MPG.18962e9de924f1298a66d@newsgroups.borland.com...
Quote
> In article <3e2c404...@newsgroups.borland.com>,
> i...@thirdeyedevelopment.com says...
> > Any help would be great

> It's difficult to answer your question without seeing the full
> metadata and query optimization PLAN for each of the various
> configurations of your SQL, but I might point you to the Help for
> InterBase PLANalyzer (free; in .signature), which covers such issues in
> detail.

> Keep in mind that this Help was written for InterBase, and Firebird

...

read more »

Re:Delphi 6 - Interbase / Firebird - SQL index Problem Help needed


In article <3e2d3...@newsgroups.borland.com>, i...@thirdeyedevelopment.com
says...
Quote
> Here is the metadata

> Hope this helps

        I need to see the PLAN for each version of the statement (the fast
version and the slow version).

        -Craig

--
Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

Re:Delphi 6 - Interbase / Firebird - SQL index Problem Help needed


Fast SQL
    SELECT P.PATIENT_ID,P.FORENAME,P.SURNAME,P.DOB,P.PID
    FROM PX_MARKETING
    RIGHT OUTER JOIN PATIENT P ON (PX_MARKETING.PID = P.PID)
    LEFT OUTER JOIN PX_LETTER ON (PX_LETTER.PID = P.PID)
    WHERE
    P.CONTACT_ID <> 3
    AND PX_MARKETING.TYPE_ID = 1
    AND PX_LETTER.LASTLETTERDATE <= '1/21/03'
    AND PX_LETTER.LASTLETTERNO =0
    AND P.DOB <= '1/21/1990'
Fast PLAN
    PLAN JOIN (JOIN (P INDEX (IDX_PATIENT),PX_MARKETING INDEX
(IDX_PX_MARKETING)),PX_LETTER INDEX (IDX_PX_LETTER2))

Slow SQL
    SELECT P.PATIENT_ID,P.FORENAME,P.SURNAME,P.DOB,P.PID
    FROM PX_MARKETING
    RIGHT OUTER JOIN PATIENT P ON (PX_MARKETING.PID = P.PID)
    LEFT OUTER JOIN PX_LETTER ON (PX_LETTER.PID = P.PID)
    WHERE
    P.CONTACT_ID <> 3
    AND PX_MARKETING.TYPE_ID = 1
    AND PX_LETTER.LASTLETTERDATE <= '1/21/03'
    AND PX_LETTER.LASTLETTERNO =0

Slow PLAN
    PLAN JOIN (JOIN (P NATURAL,PX_MARKETING INDEX
(IDX_PX_MARKETING)),PX_LETTER INDEX (IDX_PX_LETTER2))

as you can see all that is missing in the slow is the last line

as far as i can make out it just does use the Patient Table Index at all

"Craig Stuntz [TeamB]" <cstu...@nospamplease.com (but use vertexsoftware to

Quote
email)> wrote in message

news:MPG.189734818d2d82a98a679@newsgroups.borland.com...
Quote
> In article <3e2d3...@newsgroups.borland.com>, i...@thirdeyedevelopment.com
> says...
> > Here is the metadata

> > Hope this helps

> I need to see the PLAN for each version of the statement (the fast
> version and the slow version).

> -Craig

> --
> Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
> Delphi/InterBase Weblog : http://delphi.weblogs.com
> InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
> InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

Re:Delphi 6 - Interbase / Firebird - SQL index Problem Help needed


In article <3e2d91b...@newsgroups.borland.com>,
i...@thirdeyedevelopment.com says...

Quote
> as you can see all that is missing in the slow is the last line

> as far as i can make out it just does use the Patient Table Index at all

        Indeed, it shouldn't.  The index used in the "fast" query,
IDX_PATIENT, refers only to the DOB field.  Since that doesn't appear in
the WHERE clause of the "slow" query, the index is not useful in
optimizing the "slow" SQL.

        It appears to me that the "fast" query is faster simply becuase you
have told it to look at significantly fewer records (by filtering them on
the birthdate).  IB just has less work to do.

        When optimizing a JOIN, the fastest thing for InterBase to do is to
read one table in storage (sequential) order, and the other using an
index, which is exactly what it's doing.  The only reason you're getting
the IDX_PATIENT index in the PLAN for the "fast" query is that it's being
used to optimize the WHERE clause (not the JOIN).

        So to me this looks correct, from an optimization point of view.  If
it's too slow for your application, then we need to look both at what IB
is doing when it executes the query (fetch statistics) and whether or not
the query is returning more data then your app requires.  The non-use of
IDX_PATIENT in the "slow" query is not in and of itself a problem.

        -Craig

--
Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

Re:Delphi 6 - Interbase / Firebird - SQL index Problem Help needed


In article <3e2d9b6...@newsgroups.borland.com>,
i...@thirdeyedevelopment.com says...
Quote
> OK so on the slow sql what would be the best index to create then in order
> for the patient table to be used . I have created an index based on
> Contact_Id  and another on Contact_Id,PID and another on Contact_id,DOb,PId
> but this still doesnt work cant understand why even though p.contact_id <> 3
> is used there is no index used as well

        The simple index is probably the best.  What is the selectivity of
this index?  Try doing a SET STATISTICS on the index.

        -Craig

--
Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

Other Threads