Re: Efficiency Issue with "SELECT IN (..)" and "SELECT .. OR

On Fri, 16 May 2003, Richard Huxton wrote:

> > My question is, does anyone have any alternate ideas for how I
> > can do a query like this and have it perform well? The tables I
> > am working with are big enough that a sequential scan is not
> > helpful. Is this a bug I am encountering or an error in my
> > query? Is this a known issue?
>
> Known issue - the usual advice is to rewrite in the form of EXISTS, but I
> can't think how to do that if you have a long list of literal values. You
> could create a temp table to hold your matching values and join against it,
> but I realise that's not a terribly elegant solution. Unless of course, it's
> a search-engine type of situation where it makes a certain amount of sense.

Thanks to everyone for the replies.

The temporary table route is something we did. It's an acceptable
stopgap, but it's still not outstanding in terms of performance
and has lead to other issues. I will examine the 'EXISTS' clause
option depending on the progress of 7.4.

> > I have seen this beahaviour on 7.2.1 and 7.3.2 on both Solaris
> > and Linux platforms.
>
> Supposed to be some improvements in the forthcoming 7.4 but I don't know if
> that will help your particular case.

That's good news.

--
_______________________________________________________________________
Front Logic Inc. Tel: 306.653.2725 x14
226 Pacific Ave or 1.800.521.4510 x14
Saskatoon, SK Fax: 306.653.0972
S7K 1N9 Canada Cell: 306.717.3746
http://www.frontlogic.com mike(dot)winter(at)frontlogic(dot)com

Find out what TYPENGO(tm) N300 Search Technology can do for your
company: http://www.frontlogic.com/interactive/hosts/typengo/index.html