Hi Rino-Rene!

I'm just wondering what happens if you change two lines of your view,
i.e.

where (account.ibandate<=SDATE or 2=1)
where (account.ibandate>SDATE or 0=3)

I'm OR-ing to avoid using any SDATE indexes, you don't gain anything
from using an index when a large porportion of the records fulfil your
criteria. Though if you are saying that selecting 35000 records from
the view takes 9 seconds and think this is 'very' slow, then I think
it may be difficult for me to be of much help.

Set

--- In firebird-support@yahoogroups.com, "Riho-Rene Ellermaa" <r.
ellermaa@h...> wrote:> Hi!

>
> I'm having a small problem (dummy as I am in SQL). In our banking
> the account numbers system will changes in certain date (IBANDATE).
> Now, when customer wants to view his transactions over long period
> that contains this date, he/she must see the old or new account
> number depending from transaction date. Also he/she can do search
> over entire table using old or new account number and still getting
> correct results back (depending from SDATE)
>
> I thougth to be clever and created a view (included below). This
> works fine with small data, but when I tried it out with bigger
> number of transactions (35000), then the view is very slow (9 sec).
>
> Should I stop using the ACCID in STAT_HISTORY and use the ACCOUNT
> number. Then I must modify all my filters to use
> WHERE STAT_HISTORY.ACCOUNT=ACCOUNT.ACCOUNT or
> STAT_HISTORY.ACCOUNT=ACCOUNT.OLDACCOUNT
>
> Or are there other and better solutions with view
>
> CREATE TABLE ACCOUNT (
> ID INTEGER NOT NULL,
> ACCOUNT ACCOUNT NOT NULL, //new account string
> OLDACCOUNT ACCOUNT, //old account string
> ....
> IBANDATE DATE,
> PRIMARY KEY (ID)
> );
>
> /* Transactions table */
> CREATE TABLE STAT_HISTORY (
> ACCID INTEGER NOT NULL,
> SDATE DATE NOT NULL,
> ROW INTEGER NOT NULL,
> .........
> PRIMARY KEY (ACCID,SDATE,ROW)
> );
>
> CREATE VIEW V_STAT_HISTORY(ACCID,ACCOUNT,SDATE,ROW,...)
> AS
>
> SELECT accid,account.ACCOUNT ,
> SDATE,
> ROW ,
> ....
> from stat_history
> join account on stat_history.accID=account.id
> where account.ibandate<=SDATE
> union
> SELECT accid,account.OLDACCOUNT ,
> SDATE,
> ROW ,
> ....
> from stat_history
> join account on stat_history.accID=account.id
> where account.ibandate>SDATE