RES: [firebird-support] Query optimization mystery

['Fabiano - Desenvolvimento SCI' fabi...@sci10.com.br [firebird-support]]

Maybe.

Try update all index statistics. MAYBE it helps.

In last case MAYBE a backup/restore can help too… (or drop and recreate those 
indexes)

 

De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Enviada em: terça-feira, 13 de maio de 2014 17:15
Para: firebird-support@yahoogroups.com
Assunto: Re: [firebird-support] Query optimization mystery

 

  

Try this:

select * from

(

  select a.User_ID
  from Advocate

  where a.USER_ID=37

) as FILTER1,  supprog sp

Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code

 

 This doesn't quite execute.  I cleaned it up to this:

 

select * from (
select a.User_ID, a.ADVOCATE_CODE
from Advocate a
where a.USER_ID=37
) as FILTER1,supprog sp
Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code

 

It gets the same plan: "PLAN JOIN (SP NATURAL, FILTER1 A INDEX (ADVOCATE_))".  
But it has another problem, too:  ultimately I'm wanting to use this as part of 
a view, so I can't do my filtering inside the query.

 

Why isn't Firebird using the index?  Do the index statistics have anything to 
do with it?

RES: [firebird-support] Query optimization mystery

['Fabiano - Desenvolvimento SCI' fabi...@sci10.com.br [firebird-support]]

Your problem is: 

"PLAN JOIN (SP NATURAL, A INDEX (ADVOCATE_))"

Wish means a full table scan on SUPPROG. It is strange, because you have the 
index 

USV_SUPPROG_ADVOCATE_CODE ON field ADVOCATE_CODE

 

Try this:

select * from

(

  select a.User_ID
  from Advocate

  where a.USER_ID=37

) as FILTER1,  supprog sp

Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code

 

Firebird will first filter the needed data and then join using the index 
USV_SUPPROG_ADVOCATE_CODE at table supprog. This, (at least for me) is always 
de faster way Firebird retrieve data. (Filter and/or order your data and then 
join with other tables)

 

De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Enviada em: terça-feira, 13 de maio de 2014 16:29
Para: firebird-support@yahoogroups.com
Assunto: [firebird-support] Query optimization mystery

 

  

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);