--- In firebird-support@yahoogroups.com, "menjit_singh"
<menjit_singh@...> wrote:

>
> hello everyone.
>
> I have an application that uses Firebird embedded (fbclient.dll
> v1.5). Some details:
>
> -8 columns: (Stock symbol, stock name, date, open price, close price,
> low price, high price, volume).
> -Data updated daily (about 6000 stock symbol) and covers over 300
> days data.
> - i use index on stock symbol, stock name and date to increase speed
> when doing query.

Is this 1 index or 3?

If it is 1, then you will only get a benefit on a date based query if
you include stock symbol and stock name in your where clause.

>It works fine for 90 days data but is very slow
> when reaching 180 days data.

Does it suddenly get slower, or it is just twice as slow?

> How do i maintain the query speed? I was thinking to load the index
> information in memory so that it is faster. Can it be done? What is
> the command to load index into memory (if any)?

You are dealing with 6000 x 300 = 1,800,000 records, which is not that
much. Forget loading things into memory, what you need is a well
thought out index strategy, let the dbms worry about caching in
memory. People with a lot more experience than us have spent a long
time working out the best way of doing it.

>
> I have not implemented primary key or foreign key simply because i
> dont know how to do it.

Lookup any SQL tutorial. Primary keys and foreign keys are for
consistency, not performance.

>
> Any help will be much appreciated.

Post the query you are trying to run, and the plan it generates.

Adam