Hi all,

now on hte right frequency!

I have a table, say Articles. I want to list all articles without an order.
So I write:

SELECT A.ID
FROM Articles A
LEFT OUTER JOIN Orders O ON O.ArticleID = A.ID
WHERE O.ID IS NULL

Pretty simple stuff - except that it needs ages to complete. There are about
20k Article records and about 200k Orders records. Any idea hwo I can speed
that up?

Ralf

Dear Ralf,

I am away from my computer on holiday at the moment, so I can't test what I am writing ... so please treat it with a pinch of salt ... but I had a si,ilar
proble, with a query which I massively improved by using a sub-query:



SELECT fields

FROM Table1

WHERE linkfield IN

(
SELECT linkfield

FROM Table2
)



I remember going down from 29 to 0.2 seconds ...
With an index on linkfield in both tables.








"Ralf Mimoun" <nospam@rad-on.de> wrote:

Hi all,

now on hte right frequency!

I have a table, say Articles. I want to list all articles without an order.
So I write:

SELECT A.ID
FROM Articles A
LEFT OUTER JOIN Orders O ON O.ArticleID = A.ID
WHERE O.ID IS NULL

Pretty simple stuff - except that it needs ages to complete. There are about
20k Article records and about 200k Orders records. Any idea hwo I can speed
that up?

Ralf

Ralf,

<< now on hte right frequency!

I have a table, say Articles. I want to list all articles without an order.
So I write:

SELECT A.ID
FROM Articles A
LEFT OUTER JOIN Orders O ON O.ArticleID = A.ID
WHERE O.ID IS NULL

Pretty simple stuff - except that it needs ages to complete. There are
about 20k Article records and about 200k Orders records. Any idea hwo I can
speed that up? >>

Unfortunately, no.  ElevateDB should be faster with joins like this due to
the way the join links are processed, but I don't have any exact figures for
you.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
news:BE71E8A9-37E0-49D6-910D-174A3D2374F3@news.elevatesoft.com:

> Ralf,
>
><< now on hte right frequency!
>
>  I have a table, say Articles. I want to list all articles without an
>  order.
> So I write:
>
>  SELECT A.ID
> FROM Articles A
> LEFT OUTER JOIN Orders O ON O.ArticleID = A.ID
> WHERE O.ID IS NULL
>
>  Pretty simple stuff - except that it needs ages to complete. There
>  are
> about 20k Article records and about 200k Orders records. Any idea hwo
> I can speed that up? >>
>
> Unfortunately, no.  ElevateDB should be faster with joins like this
> due to the way the join links are processed, but I don't have any
> exact figures for you.
>

Won't EXCEPT be any faster?

SELECT A.ID
FROM Articles
EXCEPT
SELECT DISTINCT ArticleID
FROM Orders

(not sure whether "DISTINCT" helps or hinders)

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.24 Build 1)

Chris,

<< Won't EXCEPT be any faster? >>

That might be a toss-up.  I'd have to actually try it to see because the
EXCEPT also has to do a lot of I/O with the DISTINCT.

--
Tim Young
Elevate Software
www.elevatesoft.com