Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
Repost: Find records without a link in another table - fast |
Fri, Aug 25 2006 4:59 AM | Permanent Link |
"Ralf Mimoun" | 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 |
Fri, Aug 25 2006 8:58 AM | Permanent Link |
adam | 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 |
Fri, Aug 25 2006 2:35 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Aug 28 2006 6:25 AM | Permanent Link |
Chris Erdal | "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) |
Mon, Aug 28 2006 1:59 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |