Alexandre:

Thanks for your answer.

Really, what I do later is:

select sum(cantidad) from vw_items_facturas_no_anuladas where sucursal = 1
and fecha > '2007-04-30' and codigo_producto = 31344

(called from inside SP).

It tooks aprox 15 secs for each "codigo_producto", and I've about 35000 ...
so it takes too much.

Adding a fecha index doesnt make it faster.

If you have some other suggestion, I'll glad to read them.

Regards

2007/6/27, Alexandre Benson Smith <iblist@...>:

>
> Jorge Andres Brugger wrote:
> > Hi all!
> >
> > Two tables:
> >
> > CREATE TABLE FACTURAS (
> > SUCURSAL D_CODIGO_SUCURSAL NOT NULL /*
> > D_CODIGO_SUCURSAL = SMALLINT */,
> > ID_FACTURA INTEGER NOT NULL,
> > FECHA D_FECHA /* D_FECHA = DATE */,
> > ANULADA INTEGER DEFAULT 0 NOT NULL
> > );
> >
> > ALTER TABLE FACTURAS ADD CONSTRAINT PK_FACTURAS PRIMARY KEY (SUCURSAL,
> > ID_FACTURA);
> >
> >
> > CREATE TABLE ITEMS_FACTURAS (
> > SUCURSAL D_CODIGO_SUCURSAL NOT NULL /*
> > D_CODIGO_SUCURSAL = SMALLINT */,
> > ID_FACTURA INTEGER NOT NULL,
> > NUMERO_ITEM SMALLINT NOT NULL,
> > CODIGO_PRODUCTO D_CODIGO_PRODUCTO NOT NULL /*
> > D_CODIGO_PRODUCTO = INTEGER NOT NULL CHECK (value between 1 and 999999)
> */,
> > CANTIDAD SMALLINT NOT NULL
> > );
> >
> > ALTER TABLE ITEMS_FACTURAS ADD CONSTRAINT PK_ITEMS_FACTURAS PRIMARY KEY
> > (SUCURSAL, ID_FACTURA, NUMERO_ITEM);
> >
> > ALTER TABLE ITEMS_FACTURAS ADD CONSTRAINT FK_ITEMS_FACTURAS FOREIGN KEY
> > (SUCURSAL, ID_FACTURA) REFERENCES FACTURAS (SUCURSAL, ID_FACTURA) ON
> UPDATE
> > CASCADE;
> >
> > Now I want to create a View:
> >
> > CREATE VIEW VW_ITEMS_FACTURAS_NO_ANULADAS(
> > SUCURSAL,
> > ID_FACTURA,
> > FECHA,
> > NUMERO_ITEM,
> > CODIGO_PRODUCTO,
> > CANTIDAD)
> > AS
> > SELECT FACTURAS.SUCURSAL, FACTURAS.ID_FACTURA, FACTURAS.FECHA,
> > ITEMS_FACTURAS.NUMERO_ITEM, ITEMS_FACTURAS.CODIGO_PRODUCTO,
> > ITEMS_FACTURAS.CANTIDAD
> > FROM FACTURAS INNER JOIN ITEMS_FACTURAS ON (FACTURAS.SUCURSAL =
> > ITEMS_FACTURAS.SUCURSAL) and (FACTURAS.ID_FACTURA =
> > ITEMS_FACTURAS.ID_FACTURA)
> > where facturas.anulada = 0;
> >
> > Plan for this view is:
> >
> > PLAN JOIN (FACTURAS NATURAL, ITEMS_FACTURAS INDEX (FK_ITEMS_FACTURAS))
> >
> > ¿Why facturas plan is natural is there is a PK with the components used
> in
> > join?
> >
> > ¿Is there a way to "fix" this?
> >
> > Thanks!
> >
> >
> >
>
> Hi Jorge !
>
> As far as I can see there is no fix ! That plan is the best that FB
> could to to a select on that view without a filter criteria.
>
> I suppose you got this plan using this query:
>
> Select * from VW_ITEMS_FACTURAS_NO_ANULADAS
>
> Since there is no WHERE clause to filter out the records all facturas
> should be read.
>
> You could add an index on Facturas.Anulada, theorically this would be a
> *very bad* index since it would have just to values, but if you have a
> small number of facturas with anuladas = 0 it could help. (I don't think
> it would give any performance benefit, but you could try.
>
> The Facturas table would use an indexed (if you have defined the proper
> index of course) plan if you put something on the WHERE clause like this:
>
> Select * from VW_ITEMS_FACTURAS_NO_ANULADAS where Fecha between
> '2007-06-01' and '2007-01-30'
>
> The plan should be something similar to this:
>
> PLAN JOIN (FACTURAS INDEX(IDX_FECHA), ITEMS_FACTURAS INDEX
> (FK_ITEMS_FACTURAS))
>
>
> see you !
>
> --
> Alexandre Benson Smith
> Development
> THOR Software e Comercial Ltda
> Santo Andre - Sao Paulo - Brazil
> www.thorsoftware.com.br
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
>
>
>
>

[Non-text portions of this message have been removed]