Hi Kevin,

let me first answer one of your particular questions:

> I don't really
> understand "statistics" but USV_SUPPROG_ADVOCATE_CODE
> has 0.029412 and USV_SCHLHIST_ADVOCATE_CODE
> has 0.000422.

with the first index, you've about 3% of records with the same value in
index (these are going to be searched sequentially), with the second,
only 0.04%. Depending on the number of records (cardinality), this can
make a really big difference.

Next: AFAIK, Firebird changes the order of executing join-members (or
left join members) by selectivity and cardinality. Because procedures
result values can not be narrowed by index by the engine at execution
time, they usually are evaluated at the end of the chain.

If you know, that the stored procedure returns view records, connect it
first and the rest by "left join" (left and straight join don't get
shifted between each other by optimizer). If stored procedures result
can be narrowed down by parameters on select time, try this: the result
of SP never concernces about indicies.

Thomas

Am 13.05.2014 21:29, schrieb Kevin Donn kdonn@... [firebird-support]:

>
>
> I've got a query optimization mystery I need some help with. The short
> version is I've got two tables that are very similar, but when I join
> each of them to a third table, I get different plans - one runs fast and
> the other runs slow. Here are the queries:
>
> select sp.STUDENTSEQ, a.User_ID
> from schlhist sp
> join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code
> where a.USER_ID=37
>
> select sp.STUDENTSEQ, a.User_ID
> from supprog sp
> join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code
> where a.USER_ID=37
>
> The first runs with plan "PLAN JOIN (A INDEX (IDX_ADVOCATE1), SP INDEX
> (USV_SCHLHIST_ADVOCATE_CODE))" which is fast. The second runs with plan
> "PLAN JOIN (SP NATURAL, A INDEX (ADVOCATE_))" which is slow. If I change
> the plan on the second to "PLAN JOIN (A INDEX (IDX_ADVOCATE1), SP INDEX
> (USV_supprog_ADVOCATE_CODE))" it also runs fast. I don't really
> understand "statistics" but USV_SUPPROG_ADVOCATE_CODE
> has 0.029412 and USV_SCHLHIST_ADVOCATE_CODE
> has 0.000422. That seems like a potentially important difference, but
> I'm not sure what it means or what to do about it.
>
> Here's the ddl (I've chopped out a lot fields I didn't think were relevant):
>
> CREATE TABLE ADVOCATE(
> ADVOCATE_CODE varchar(15),
> ADVOCATE varchar(20) COLLATE EN_US,
> TEACHINGCERT varchar(1) COLLATE EN_US,
> GENDATE timestamp,
> MODDATE timestamp,
> CHANGESTATUSFLAG smallint,
> REGIONCODE smallint,
> RETIREDCODE varchar(1),
> USER_ID integer
> );
>
> CREATE UNIQUE INDEX ADVOCATE_ ON ADVOCATE (ADVOCATE_CODE);
> CREATE INDEX ADVOCATE_ADVOCATE ON ADVOCATE (ADVOCATE);
> CREATE INDEX ADVOCATE_REGIONCODE ON ADVOCATE (REGIONCODE);
> CREATE INDEX IDX_ADVOCATE1 ON ADVOCATE (USER_ID);
>
> CREATE TABLE SCHLHIST(
> STUDENTSEQ integer,
> DOMID varchar(2) COLLATE EN_US,
> DBID smallint,
> SHSEQ integer,
> FACILITYID varchar(6) COLLATE EN_US,
> LQMDATE date,
> RESDATE date,
> FUNDINGDATE date,
> ENROLLDATE date,
> WITHDRAWDATE date,
> GENDATE timestamp,
> ADVOCATE_CODE varchar(15),
> "COMMENT" blob sub_type 1
> );
>
> CREATE UNIQUE INDEX SCHLHIST_ ON SCHLHIST (STUDENTSEQ,DOMID,DBID,SHSEQ);
> CREATE INDEX SCHLHIST_FACILITYIDINDEX ON SCHLHIST (FACILITYID);
> CREATE INDEX SCHLHIST_MOSTRECENTINDEX ON SCHLHIST
> (STUDENTSEQ,RESDATE,FUNDINGDATE,GENDATE);
> CREATE INDEX SCHLHIST_STUDENTSEQINDEX ON SCHLHIST (STUDENTSEQ);
> CREATE INDEX SCHLHIST_STUFACINDEX ON SCHLHIST (STUDENTSEQ,FACILITYID);
> CREATE INDEX USV_SCHLHIST_ADVOCATE_CODE ON SCHLHIST (ADVOCATE_CODE);
>
> CREATE TABLE SUPPROG(
> STUDENTSEQ integer,
> DOMID varchar(2) COLLATE EN_US,
> DBID smallint,
> SHSEQ integer,
> SPKEY varchar(15) COLLATE EN_US,
> SPCODE varchar(3) COLLATE EN_US,
> ADVOCATE_CODE varchar(15),
> OWNER_USER_ID integer
> );
>
> CREATE UNIQUE INDEX SUPPROG_ ON SUPPROG (STUDENTSEQ,DOMID,DBID,SHSEQ,SPKEY);
> CREATE INDEX USV_SUPPROG_ADVOCATE_CODE ON SUPPROG (ADVOCATE_CODE);
> CREATE INDEX USV_SUPPROG_OWNER_USER_ID ON SUPPROG (OWNER_USER_ID);
>
>

--
Mit freundlichen Grüßen,

Thomas Beckmann
Diplom-Informatiker


Wielandstraße 14c • 23558 Lübeck
Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
Mail thomas.beckmann@... <mailto:thomas.beckmann@...>

ASSFINET-Logo

*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
info@... <mailto:info@...> • www.assfinet.de
<http://www.assfinet.de/>

Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Registergericht Koblenz HRB 23331

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist
nicht gestattet.