Re: Left Outer Join and CONTAINS - performance question...

Tech-Archive recommends: Fix windows errors by optimizing your registry

netspam_at_shic.co.uk
Date: 10/27/04 

Date: 27 Oct 2004 08:03:49 -0700

Thanks for your reply - though I'm not 100% sure I understand it
properly.

To clarify...

All three tables have an appropriate primary key (a simple way to meet
the requirements of text indexing.) I've been intentionally vague as
to whether the primary key is defined as being the column named ID in
each table - as I am interested in an answer both where this is the
case and where it is not.

I need to keep the three distinct tables A,B and C - while putting
all the data in a single table would obviously make this specific
performance problem go away it is entirely impractical due to other
binding constraints on my project.

I believe my query does work - and I get exactly the results I
want... your suggestion:

Select * from
A left outer join B on a.bid=b.id and contains (B.*,'word')
left outer join C on a.cid=c.id and contains (C.*,'word')
where
contains(A.*, word)

Is an interesting construction (because it is very likely to be
computationally cheaper) - but completely useless for my purposes as it
returns a different result set. My query returns the outer join of A
with B and C filtered to only contain those rows which use 'word'
in any of the free-text columns. Your query returns every row in A
containing 'word' and associates that with those records in B and C
where the records in B and C also contain word. If 'word' was
present only in a record in B or C, but not in the associated record in
A - then your query would not return this row... mine would have
done.

I am not concerned by "3 trips to the full-text engine" per se - as
this doesn't represent a significant scalability issue - If I can put
up with three trips when I have a thousand records I can likely put up
with it when I have a million or more. Conversely if every lookup in
both outer joins needs to be computed then I am facing a dire situation
performance wise. I can see alternative ways to solve my problem -
they are complicated and would be very tedious to implement in my
project. Firstly I would like to be certain how this query is
optimised [NB my initial experiments on SQLServer 2000 suggest
inefficiently - however my evidence is entirely inconclusive.] Second
I would like to know if I can improve performance for this query by
defining indices in a particular way, or making some other simple
change (having rejected putting all the data in one table as
impractical) - and if so - how would this affect the optimisation for
this query.

Relevant Pages

  • Re: SQL Subquery on Max(date)
    ... The overall query I'm trying to insert this select into ... LEFT OUTER JOIN ... CONSTRAINT pk_oncd_company ... PRIMARY KEY, ...
    (microsoft.public.access.formscoding)
  • Left Outer Join: Index Seek not providing all index columns
    ... We have a left outer join query which is intended to prove ... The wrinkle is that the SQL Server 2000 query optimizer is generally ... left outer join entextractitems eei ...
    (microsoft.public.sqlserver.server)
  • Re: Left Outer Join and CONTAINS - performance question...
    ... All three tables have an appropriate primary key (a simple way to meet ... I believe my query does work - and I get exactly the results I ... A left outer join B on a.bid=b.id and contains ... impractical) - and if so - how would this affect the optimisation for ...
    (microsoft.public.sqlserver.server)
  • Re: Left Outer Join and CONTAINS - performance question...
    ... All three tables have an appropriate primary key (a simple way to meet ... I believe my query does work - and I get exactly the results I ... A left outer join B on a.bid=b.id and contains ... impractical) - and if so - how would this affect the optimisation for ...
    (microsoft.public.sqlserver.server)
  • Re: Comparing datasheets
    ... your own query and check the results of the query to determine what the ... You need to use a full outer join on the primary key. ... FROM tblEquipment AS Eq RIGHT JOIN tblNewEquip AS NE ...
    (microsoft.public.access.modulesdaovba)