Sasa Mihajlovic wrote:

> Hi,
>
> I have two tables robfaktura (invoice header) and robfakturas
> (invoice items), and wiev based on these two tables:
>
> SELECT Z.ID, Z.SESIJA, Z.UIP, Z.TIP, Z.OBJEKAT, Z.KOMITENTI,
> Z.KOMOBJEKTI, Z.KOMITENTIP, Z.BROJ, Z.BROJS, Z.DATUM,
> Q.NVREDNOST, Q.SVREDNOST, Q.RVREDNOST, Q.OVREDNOST,
> Q.PVREDNOST, Q.KVREDNOST, Q.NVVREDNOST, Q.UVREDNOST
> FROM ROBFAKTURA Z
> JOIN ( SELECT X. ID, SUM(Y.NVREDNOST) AS NVREDNOST, SUM(Y.SVREDNOST)
> AS SVREDNOST, SUM(Y.RVREDNOST) AS RVREDNOST, SUM(Y.OVREDNOST) AS
> OVREDNOST,
> SUM(Y.PVREDNOST) AS PVREDNOST, SUM(Y.KVREDNOST) AS
> KVREDNOST, SUM(Y.NVVREDNOST) AS NVVREDNOST, SUM(Y.KVREDNOST -
> X.POPUSTVR - X.AVANS1 - X.AVANS2 - X.AVANS3) AS UVREDNOST
> FROM ROBFAKTURA X
> LEFT OUTER JOIN ROBFAKTURAS Y ON Y.ROBFAKTURA = X.ID
> GROUP BY X.ID) Q ON Q.ID = Z.ID
>
> I have one simple question for Firebird experts, how to optimize this
> viev (sql statement) if header table have more then milion records
> and everytime when I call this view I must wait about 5-10 minutes
> even I get empty table as resultset?
> Or someone have smarter solution than mine?
>
> Thnaks in advance...
>
>
>

Did you tried without your in-line view ?

something on this line:

SELECT
Z.ID, Z.SESIJA, Z.UIP, Z.TIP, Z.OBJEKAT, Z.KOMITENTI,
Z.KOMOBJEKTI, Z.KOMITENTIP, Z.BROJ, Z.BROJS, Z.DATUM,
SUM(Y.NVREDNOST) AS NVREDNOST, SUM(Y.SVREDNOST) AS SVREDNOST,
SUM(Y.RVREDNOST) AS RVREDNOST, SUM(Y.OVREDNOST) AS OVREDNOST,
SUM(Y.PVREDNOST) AS PVREDNOST, SUM(Y.KVREDNOST) AS KVREDNOST,
SUM(Y.NVVREDNOST) AS NVVREDNOST,
SUM(Y.KVREDNOST - X.POPUSTVR - X.AVANS1 - X.AVANS2 - X.AVANS3) AS
UVREDNOST
FROM
ROBFAKTURA Z LEFT JOIN
ROBFAKTURAS Y ON Y.ROBFAKTURA = Z.ID
GROUP BY
Z.ID, Z.SESIJA, Z.UIP, Z.TIP, Z.OBJEKAT, Z.KOMITENTI,
Z.KOMOBJEKTI, Z.KOMITENTIP, Z.BROJ, Z.BROJS, Z.DATUM

I don't know how well in-line views are optimized, and you should take
into account that your in-line view use an aggregate, perhaps it should
be completely materialized before the joining to the first table.

But I think the main question is:
How do you select your view ? Because some queries would be quite slow...

Post your queries and plans...


see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br