Hi Svein.

Thank you very much for your advice.

Here are the results ot the test:

First test, query is quite fast:

SELECT FIRST 10 SKIP 0 T.K_TARGET, T.K_PHONE, T.V_NAME, T.D_CREATION
FROM TARGETS T JOIN TARGET_GROUPS TG ON TG.K_TARGET = T.K_TARGET WHERE
T.K_USER=2262 AND T.C_TYPE=0 AND TG.K_GROUP+0 = 2610 ORDER BY T.V_NAME;

PLAN JOIN (T ORDER NAME INDEX (RDB$53), TG INDEX (RDB$FOREIGN63))

Second test, the same result:

SELECT FIRST 10 SKIP 0 T.K_TARGET, T.K_PHONE, T.V_NAME, T.D_CREATION
FROM TARGETS T JOIN TARGET_GROUPS TG ON TG.K_TARGET = T.K_TARGET+0 WHERE
T.K_USER=2262 AND T.C_TYPE=0 AND TG.K_GROUP+0 = 2610 ORDER BY T.V_NAME;

PLAN JOIN (T ORDER NAME INDEX (RDB$53), TG INDEX (RDB$FOREIGN63))

Third test, the plan is quite similar to FB 1.0.3:

SELECT FIRST 10 SKIP 0 T.K_TARGET, T.K_PHONE, T.V_NAME, T.D_CREATION
FROM TARGETS T JOIN TARGET_GROUPS TG ON TG.K_TARGET = T.K_TARGET+0 WHERE
T.K_USER=2262 AND T.C_TYPE=0 AND TG.K_GROUP = 2610 ORDER BY T.V_NAME;

PLAN JOIN (T ORDER NAME INDEX (RDB$53), TG INDEX (RDB$PRIMARY22))

I guess there are some logics in this tests but now I can't understand
them.

Could you please give us a little explanation about what the "+0" means
inside an SQL query and how to use it to improve performance?

Is it safe to use it thinking in forward and even backward
compatibility?


Thank you very much.

Juan Pedro López

> I don't know Fb 2.0 well enough, but try adding +0 like this (that's at
> least what I do when I've problems with Fb 1.5):
>
> SELECT FIRST 10 SKIP 0 T.K_TARGET, T.K_PHONE, T.V_NAME, T.D_CREATION
> FROM TARGETS T JOIN TARGET_GROUPS TG ON TG.K_TARGET = T.K_TARGET WHERE
> T.K_USER=2262 AND T.C_TYPE=0 AND TG.K_GROUP+0 = 2610 ORDER BY T.V_NAME
>
> Hopefully, this will get a plan that satisfy your requirements. If it
> still insists on having TG as the first table in the plan, try changing
> the JOIN to ON TG.K_TARGET = T.K_TARGET+0 (either in addition to or
> instead of the +0 above).
>
> Report back the result to this list, I'm curious whether this will work
> in Fb 2.0.
>
> HTH,
> Set
>
> Juan Pedro López wrote:
> > I have the following tables:
> >
> > CREATE TABLE TARGETS (
> > K_TARGET INTEGER,
> > K_PHONE VARCHAR(16) NOT NULL COLLATE ES_ES,
> > K_USER INTEGER,
> > D_CREATION TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
> > C_NETWORK VARCHAR(5) NOT NULL COLLATE ES_ES,
> > C_TYPE SMALLINT NOT NULL,
> > V_NAME VARCHAR(40) DEFAULT '' NOT NULL COLLATE ES_ES);
> >
> > CREATE TABLE TARGET_GROUPS (
> > K_TARGET INTEGER,
> > K_GROUP INTEGER,
> > K_USER INTEGER);
> >
> > Primary keys
> >
> > ALTER TABLE TARGETS ADD CONSTRAINT PK_TARGETS PRIMARY KEY (K_TARGET);
> > ALTER TABLE TARGET_GROUPS ADD CONSTRAINT PK_TARGET_GROUPS PRIMARY KEY
> > (K_TARGET, K_GROUP, K_USER);
> >
> > Unique keys
> >
> > ALTER TABLE TARGETS ADD CONSTRAINT UK_TARGETS UNIQUE (K_USER, C_TYPE,
> > K_PHONE);
> >
> > Foreign keys
> >
> > ALTER TABLE TARGETS ADD CONSTRAINT FK_TARGETS FOREIGN KEY (K_USER)
> > REFERENCES USERS (K_USER) ON DELETE CASCADE ON UPDATE CASCADE;
> > ALTER TABLE TARGET_GROUPS ADD CONSTRAINT FK01_TARGET_GROUPS FOREIGN KEY
> > (K_GROUP) REFERENCES GROUPS (K_GROUP) ON DELETE CASCADE ON UPDATE
> > CASCADE;
> > ALTER TABLE TARGET_GROUPS ADD CONSTRAINT FK02_TARGET_GROUPS FOREIGN KEY
> > (K_USER) REFERENCES USERS (K_USER) ON DELETE CASCADE ON UPDATE CASCADE;
> > ALTER TABLE TARGET_GROUPS ADD CONSTRAINT FK_TARGET_GROUPS FOREIGN KEY
> > (K_TARGET) REFERENCES TARGETS (K_TARGET) ON DELETE CASCADE ON UPDATE
> > CASCADE;
> >
> > Additional indices
> >
> > CREATE INDEX NAME ON TARGETS (V_NAME);
> >
> > The following query executed in FB 2.0.1
> >
> > SELECT FIRST 10 SKIP 0 T.K_TARGET, T.K_PHONE, T.V_NAME, T.D_CREATION
> > FROM TARGETS T JOIN TARGET_GROUPS TG ON TG.K_TARGET = T.K_TARGET WHERE
> > T.K_USER=2262 AND T.C_TYPE=0 AND TG.K_GROUP = 2610 ORDER BY T.V_NAME
> >
> > PLAN SORT (JOIN (TG INDEX (RDB$FOREIGN61), T INDEX (RDB$PRIMARY20)))
> >
> > takes much more time to execute than in FB 1.0.3
> >
> > I guess the difference is in the selected plan. In FB 1.0.3 the plan is
> >
> > PLAN JOIN (T ORDER NAME,TG INDEX (RDB$PRIMARY22))
> >
> > so index NAME is used to sort the results. Also TARGET_GROUPS's pk is
> > used.
> >
> > If I set this plan while executing the query in FB 2.0.1, execution time
> > is the same as in FB 1.0.3.
> >
> > Selectivity of NAME index is quite poor, so I tested to replace it with
> > a composite one between V_NAME and K_TARGET fields. In this case the
> > index was unique because K_TARGET is primary key.
> >
> > Setting the query to "ORDER BY T.V_NAME, T.K_TARGET" doesn't show any
> > change in the selected plan.
> >
> > Restoring the original NAME index ant testing the following query
> >
> > SELECT FIRST 10 SKIP 0 T.K_TARGET, T.K_PHONE, T.V_NAME, T.D_CREATION
> > FROM TARGETS T WHERE T.K_USER=2262 AND T.C_TYPE=0 ORDER BY T.V_NAME
> >
> > turned out that the optimizer was using the following plan
> >
> > PLAN (T ORDER NAME INDEX (RDB$53))
> >
> > So in this case the poor selectivity index is used anyway and results
> > are the same as in FB 1.0.3
> >
> > What can I do to improve performance with FB 2.0.1 for the first query?
> >
> > I'm using superserver for Linux.
> >
> > Thank you very much,
> >
> > Juan Pedro López
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>