Too slow... can't think of a faster way.

Thread View: borland.public.interbase.sql

6 messages

[T] Back to Threads [V] Tree View [G] Group Articles

6 total messages Started by Frederic Gelinas Tue, 03 Jun 2008 14:07

#103

Author: Frederic Gelinas
Date: Tue, 03 Jun 2008 14:07

26 lines
1675 bytes

Here is a quite simple query and it's plan.  It takes 25 to 30 seconds
to complete, which is way too long.  I can't think of another way of
doing it.

The table ARITEM has a lot of rows.  Each ARITEM row can have an
ARTRANSACTION tied to it (only 1.  There is an ARTRANSACTIONID field in
ARITEM).  I need all the ARITEM that have a scandate >= to a specified
date and that there is no ARTRANSACTION linked to it, or, if there is
an ARTRANSACTION, the date on the ARTransaction must be > the same date.

Here is how I did it:

Select sum(amount) from ARITEM a  left outer join ARTRANSACTION art on
art.ID=a.ARTRANSACTIONID
  where a.client=:aClient and a.customer=:aCustomer
  and ((a.SCANDATE <= :aDate)
  and (a.ARTRANSACTIONID is null or art.trandate > :aDate))

Plan:

PLAN JOIN (A INDEX (I_ARITEM_SCANDATE),ART INDEX (RDB$PRIMARY64))

I can't think of another way of doing this query, or speeding it.


--

Re: Too slow... can't think of a faster way.

#104

Author: Frederic Gelinas
Date: Tue, 03 Jun 2008 14:41

15 lines
1370 bytes

Frederic Gelinas wrote:

> Select sum(amount) from ARITEM a  left outer join ARTRANSACTION art on
> art.ID=a.ARTRANSACTIONID
>   where a.client=:aClient and a.customer=:aCustomer
>   and ((a.SCANDATE <= :aDate)
>   and (a.ARTRANSACTIONID is null or art.trandate > :aDate))

That query is not working.  It is not counting any records where
ARITEM.ARTRANSACTIONID is null.  The left outer join automatically
rejects those records.

I'm back to square 1.

--

Re: Too slow... can't think of a faster way.

#105

Author: dan palley
Date: Tue, 03 Jun 2008 17:26

33 lines
2192 bytes

"Frederic Gelinas" <frederic_gelinas@hotmail.com> wrote in message 
news:4845581a$1@newsgroups.borland.com...
> Frederic Gelinas wrote:
>
>> Select sum(amount) from ARITEM a  left outer join ARTRANSACTION art on
>> art.ID=a.ARTRANSACTIONID
>>   where a.client=:aClient and a.customer=:aCustomer
>>   and ((a.SCANDATE <= :aDate)
>>   and (a.ARTRANSACTIONID is null or art.trandate > :aDate))
>
> That query is not working.  It is not counting any records where
> ARITEM.ARTRANSACTIONID is null.  The left outer join automatically
> rejects those records.
>
> I'm back to square 1.

Ok, the sum(amount) is potentially ambiguous.  I would re-write as 
sum(a.amount) just in case.

Your (a.ARTRANSACTIONID is NULL is a bit redundant; I would revise that too:

Select sum(a.amount)
from ARITEM a
  left outer join ARTRANSACTION art on art.ID=a.ARTRANSACTIONID
where a.client=:aClient and
          a.customer=:aCustomer and
          a.SCANDATE <= :aDate and
          (art.trandate is null or art.trandate > :aDate)

The left outer join will not exclude records where a.ARTRANSACTIONID is 
null -- that's the whole point of using a left outer join.

Dan

Re: Too slow... can't think of a faster way.

#106

Author: Frederic Gelinas
Date: Wed, 04 Jun 2008 01:21

11 lines
1267 bytes

dan palley wrote:

> The left outer join will not exclude records where a.ARTRANSACTIONID
> is null -- that's the whole point of using a left outer join.

Yes, it does exclude record with null in a.ARTransactionID.  The left
join would return all the record of ARITEM, even if there is no
matching ARTRANSACTIONID, but it would not consider the records with a
null value in the joined field.  Tested against an IB 7.5 database.

--

Re: Too slow... can't think of a faster way.

#108

Author: dan palley
Date: Wed, 04 Jun 2008 17:41

17 lines
1792 bytes

"Frederic Gelinas" <frederic_gelinas@hotmail.com> wrote in message 
news:4845ee2a$1@newsgroups.borland.com...
> dan palley wrote:
>
>> The left outer join will not exclude records where a.ARTRANSACTIONID
>> is null -- that's the whole point of using a left outer join.
>
> Yes, it does exclude record with null in a.ARTransactionID.  The left
> join would return all the record of ARITEM, even if there is no
> matching ARTRANSACTIONID, but it would not consider the records with a
> null value in the joined field.  Tested against an IB 7.5 database.

If there are records being excluded, it's probably some condition in the 
where clause and not the LEFT JOIN.  Feel free to upload a small sample 
database that demonstrates the problem if you don't agree.

Dan

Re: Too slow... can't think of a faster way.

#109

Author: Frederic Gelinas
Date: Wed, 04 Jun 2008 23:26

14 lines
1463 bytes

dan palley wrote:

> If there are records being excluded, it's probably some condition in
> the where clause and not the LEFT JOIN.  Feel free to upload a small
> sample database that demonstrates the problem if you don't agree.

You are right.  I probably worked too many hours lately.  It was
against my understanding of the outer join.  I tested with with a
simple query, and I got all the records.  I'll investigate on this
query tomorrow.

Thanks for your help.

--

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads