Jeremy Lim 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.

Actually Jeremy, 1 million records in two minutes means more than 8000
records per second (assuming every sales order matches none or one
customer). And that is good enough.

Your problem is that you are not thinking client/server. No-one can
benefit from a result set of 1 million records. A result set should
normally be small. Only occasionally do I ever need a result set of more
than 10000 records. Though my main tables contain between 1 and 12.5
million records each (it took about 1 minute to count the records in the
biggest table).

Let's say your shop is a place that is open 300 days a year, each day
the same amount of orders is placed and the database contains the last
year of orders. Then, what would normally be interesting is to handle
todays orders - i.e.

Select * From SalesOrder S
Left Join Customer C on C.CustomerCode = S.CustomerCode
where S.ORDER_DATE = '21.06.2006'
order by C.customername "

(also look at Adam's comments, I completely agree with what he wrote).

That way, you would get a bit more than 3000 records, which - with
decent indexing - would take 1 second to retrieve.

By the way, there's no way to "index two tables". Just create separate
indexes on each table, in your query above the useful index would be on
Customer.CustomerCode and then some indexes on whatever you have in your
where-clause.

HTH,
Set


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