Two table joining issue

[nandanak]

I'm using Pregress 10.2B; I want to write following quary,

1. There are two tables, Assume TABLE1 and TABLE2
2. Both table have facno feild but there can be facno in TABLE1 without having facno in TABLE2.
3. I want to write a query to get TABLE1 records without having facno in TABLE2.

 

[Cringer]

You want all table1 where there is no table2 for that facno? So:

FOR EACH TABLE1 NO-LOCK:
  FIND FIRST TABLE2 NO-LOCK
    WHERE TABLE2.facno EQ TABLE1.facno NO-ERROR. 
  IF AVAILABLE TABLE2 THEN 
    NEXT.
  <some processing>
END.

 

[RealHeavyDude]

You could also use a query object (static DEFINE QUERY or dynamic CREATE QUERY) with the OUTER-JOIN option. Performance-wise an OUTER-JOIN is always subject to discussion but, IMHO of course, it is way better then doing a FIND in a FOR EACH loop. I would only do this (FIND in a FOR EACH loop) when I have absolutely no other choice. Heavy Regards, RealHeavyDude.

 

[GregTomkins]

Do you have a reason, other than personal preference? ("I think it might be faster" doesn't count).

 

[RealHeavyDude]

Out of experience I can tell that a query even with an outer join will always be faster than an iterative find in a loop.

Of course, depending on what you are doing, how big the result set is and how you are connected to the database the difference in performance will be from almost not noticeable to dramatic. Dramatic when you are connected as remote client via network and a large result set.

Or course, your millage will vary.

Heavy Regards, RealHeavyDude.

 

[GregTomkins]

As logical as that is, I ran a few benchmarks on one of our Unix servers with a 500K records table and the opposite was dramatically true. I might be making a rookie mistake though, I will check it out more carefully later. Also, it's a hassle for me to setup a remote client for a quick benchmark purpose, but I think a self-serve client should be similar or better ... not much worse.

 

[mosfin]

i think this

for each table1
  where not can-find(first table2 where table2.facno = table1.facno) 
  no-lock :
  <some processing>
end.

should be faster (as it does not actually read the whole record)
than this

FOR EACH TABLE1 NO-LOCK:
FIND FIRST TABLE2 NO-LOCK
WHERE TABLE2.facno EQ TABLE1.facno NO-ERROR.
IF AVAILABLE TABLE2 THEN
NEXT.
<some processing>
END.

 

[Cringer]

I disagree as AFAIK in your example Progress doesn't determine indexes well. Having a function on the right side of the query predicate is bad practise IMO.

 

[Rob Fitzpatrick]

I agree with Cringer. The "NOT" in your where clause prevents the compiler from being able to bracket the query. You could prove this to yourself on a test DB by running it both ways and looking at _IndexStat or _UserIndexStat; zero out the stats between runs. ProTop is handy for this.