Re: Join Table

From: Roland Svensson <roland.svensson_at_localhost.se>
Date: 1998/10/04
Message-ID: <36172d13.2092518@nntpserver.swip.net>#1/1

On Sat, 3 Oct 1998 12:48:00 -0500, "Dan Guzman" <nospamplease_guzmanda_at_mindspring.com> wrote:

>Ronald, I believe set processing is significantly faster than cursor
>processing in SQL Server as well as most other DBMSs. The exception to this
>rule is if the optimizer generates a poor execution plan.
I agree, cursor processing can improve performance only when it is impossible to get a good execution plan. It is a waste of time to trying to outsmart the optimizer, but certain (rare) cases are better solved by a procedural approach.

>However, I do agree with your statement that joins can be difficult to read
>and understand. Most folks, especially programmers, have a tendency to take
>a procedural approach in retrieving data. However, the performance benefits
>make learning 'pure SQL with the trouble. This is especially true for large
>applications.

Yes, and a good programmer should know what methods to apply. Hiding complex SQL in views and stored functions will often get both better performance and maintainability. Surprisingly many programmers have a poor understanding of the capabilitys of SQL and RDBMSs.

>BTW, I think either of the following queries address Ng K C Paul's issue:
>
> SELECT tab1_key, tab2_key
> FROM tab1, tab2
> WHERE tab2_key = (SELECT MAX(tab2_key) FROM tab2 WHERE tab2_key <=
>tab1_key)

 My suggestion was:

  >  select tab1_key, tab2_key from
  > (select r1.tab1_key mx, max(r3.tab1_key) mi 
  >  from tab1 r1, tab1 r3
  >   where r3.tab1_key < r1.tab1_key 
  >   group by r1.tab1_key 
  >  UNION select  min(tab1_key), 0 from tab1 ) t1,
  >   tab1, tab2 
  > where t1.mi < tab2_key and t1.mx >= tab2_key 
  >   and t1.mx = tab1_key;
  >This will join tab2 to tab1 such as tab2_keys will join to 
  >the tab1_key that is the smallest one greater or equal to
  >tab2_key.

They will give slightly different result, what is correct only the original poster will know.
Your query will perform reasonably, my will not, hence my suggestion to match two cursors instead.

-- 
Regards,
Roland

Received on Sun Oct 04 1998 - 00:00:00 CDT