Left outer join takes a long time to excute

Hi
This is scaled down table structure for testing purpose:

The table bill_transactions contains a large no of records with duplication of batch_no.
table tbl_stock_warehouse contains all the batches.


if the tbl_bill_transactions contains few records like 1000 and tbl_stock_warehouse contains 62170, then the query excutes in 1 min 18 secs else,
if it contains 400,000 records and tbl_stock_warehouse contains 60,000 reords the query runs forever.

The aim of this query was to compute the difference in stock depleted from tbl_stock_warehouse.
Sql server does this operation super fast.
So i'm wondering is it my mistake or does this process take time with firebird or more hardware required to do the operation.


Details:

PLAN JOIN (TBL_STOCK_WAREHOUSE NATURAL, TBL_BILL_TRANSACTIONS NATURAL)


Excution time :00:01:18:0781
Prepare time :00:00:00:0000
Start memory: 9051008
Delta memory :8416
Numbe buffers: 2048
Reads: 1038
Writes: 26
Plan: PLAN JOIN (TBL_STOCK_WAREHOUSE NATURAL, TBL_BILL_TRANSACTIONS NATURAL)
Rows Affected: 62170


Query:
select qty_purchased from
tbl_stock_warehouse left outer join tbl_bill_transactions on tbl_bill_transactions.batch_no=tbl_stock_warehouse.batch_no

Test Table:
Commands CREATE TABLE tbl_stock_warehouse (
batch_no INTEGER NOT NULL,
qty_purchased integer
);

CREATE TABLE tbl_bill_transactions (
batch_no INTEGER NOT NULL,
item_qty integer,
rate integer
);

COMMIT;


CREATE UNIQUE ASC INDEX PK_batch_no ON tbl_stock_warehouse (batch_no);


COMMIT;


Please advise ...
Thanks in advance

That's because you are pulling all rows from stock warehouse and related columns from bill transactions, this makes the only index you defined (PK_batch_no on stock warehouse) unuseful as that table will be fully scanned anyway and there is not an index to be used in comparison on bill transactions.
I added it and see how the plan turns out in my example
Remember to keep your database stats fresh, it will help keeping plans and speed consistent when number of rows in tables and size ratios in a join change.

I'd be curious to see this (exactly this) structure and query in SQLServer and it's explain plan ... I doubt it would be much different.

Note that you should first post your table creation statement, correctly formatted, then you should post your queries (again, formatted) their explain plan and a short description of what you're trying to do.

You should also prepend an alias to columns in your selects, avoild prepending a tbl_ to table names (database knows if those are tables and is willing to give you that information through it's metadata and firebird supports updateable views too) ...

Pabloj,
Thanks for the review, i will try out your suggestions and revert back.

thanks ....everything working perfect after the indexes were in place