Re: Large # Items in IN () Very Slow

From: Pablo Sanchez <pablo_at_dev.null>
Date: 29 Oct 2002 21:28:38 -0600
Message-ID: <Xns92B6D1468CCA3pingottpingottbah@209.189.89.243>

Jonathan.Trumbull_at_abbott.com (Jonathan D. Trumbull) wrote in news:266c2da3.0210291539.4d11f1db_at_posting.google.com:

> Hi,
>
> I have searched and searched but I can't find even a hint of a
> reference on this.
>
> Basically I have a query in the form:
>
> SELECT ColumnA,ColumnB
> FROM SomeTable
> WHERE ColumnA IN ('Item1','Item2',....'ItemN')
>
> If I have many items, say >1000 my query slows to a crawl.

                            ^^^^^^

That's a bit ... absurd! <g> What seems to be happening is that you have a full in-memory tablescan.

Have you considered bulk loading the data into a 'scratch' table, then doing a join back to SomeTable? I suspect it'll be tons faster!

Thx!

-- 
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com

Received on Tue Oct 29 2002 - 21:28:38 CST