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

>
> I am using firebird version 1.5, Delphi 7, bde and try to open

tquery

> with below sql statement.
>
> "Select * From SalesOrder Left Join Customer on C.CustomerCode =
> S.CustomerCode
> order by customername "
>
> the customer records got 100, 000 records and sales order got

around

> one million records.
> It took more than 2 minutes to open the query. as i know indexing

will

> improve a lots the speed
> but indexing for 2 table I never try before.
>

Indexing wont help a lot because you don't use a where clause on a
very large table.

If you do not need to see SalesOrder records for which there are no
Customers (probably not possible), change it to use an inner join
rather than left join like below:

Select * From SalesOrder
Join Customer on C.CustomerCode = S.CustomerCode
order by customername

That gives the optimiser more choice.

Do you really want to see ALL the records at once? It doesn't seem to
me to be a sensible way to operate. Not even a date range filter for
SalesOrder?

BDE is still a bad idea but I know I have warned you of that before.
For testing the speed of the engine, try your query in iSQL to make
sure it is not the inefficient interface holding you down.

There may be some benefit to an index on customername if the
optimiser chooses an indexed sort, but it certainly won't do that
unless you change the join as I have displayed, and even then it may
think it knows better.

With those sorts of quantities of data, you have to stop thinking
about spreadsheets and start thinking about drill-downs.

Adam