Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 30 May 2012 @ 10:48:28 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Why a Query with Huge IN List Fails
From:   Anomy Anom

<-- Anonymously Posted: Wednesday, May 30, 2012 04:33 -->


Each of the value in a IN clause are treated as an OR. Suppose you have 'values IN (A,B,C)', it will be translated as 'value = A OR value = B OR value = C'. OR may not seem to be cause a performance issue, but once the list grows, it takes a performance hit.

I am not sure about the limit of the values in IN, but I think once you start hitting a hundred values in the IN clause, you will see performance issue. Better idea will be to use a sub-query or a volatile table.

Also, if the list tends to be very long, you have a chance of hitting the limit allocated to an SQL request (used to be 1 MB earlier, not sure what it is now).


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020