Re: Query takes a long time

On Tuesday 07 October 2003 08:54, DenBaguse MasRodjie wrote:
> Thank you the query is sucessfull, but i have new problem. The query takes
> a long time(over 1 minute) for sub-select like below:
>
>
> "select distinct t.bill_no, t.ref, t.bill_byr, c.name from artagih t,
> arcustomer c, arbill_lns where t.bill_no not in (select bill_no from
> arbill_lns) and t.cust_code=c.cust_code and t.d_r=arbill_lns.d_r and
> t.cust_code='CUST01' order by t.tanggal"
>
> Is there something wrong with my query or do you have any suggestions?

You might want to rewrite your IN clause as EXISTS. There is plenty of
discussion of this in the general/sql list archives.

> If my query is correct, where is the problem? Is it in the postgre server
> or in ODBC?

Nobody can tell you - you haven't given run-times from psql vs odbc. Nobody
can give you advice on the query's performance because you haven't shown the
output of EXPLAIN ANALYSE or mentioned what hardware/configuration settings
you have.

> Is there any OLE DB for Postgre, cause i'm using VB for my front-end
> application.
>
> For comparison if i'm using MSDE for database server, it takes not more
> than 5 seconds for the same query with above.
>
> With MySQL it takes over than 1 minute. I'm so confusing and stuck with
> this problem.

This is a PostgreSQL list - if you are using MySQL there probably isn't much
product-specific advice we can give.

If you are running PG, then I'd recommend running EXPLAIN ANALYSE, and perhaps
posting the results to the performance list.
--
Richard Huxton
Archonet Ltd