Post reply

how to Eliminate 'IN'

  • March 1, 2012 at 1:18 am

    #252368

    Hi,

    How can i eliminate IN from following query ?

    select * from idata a where isnull(validationstatus,'Y')='Y'

    and srno not in (select srno from IFDetails where eby =1 )

    and edate between '20.02.2012' and '20.02.2012'

    this query takes too long time due to both tables having more then 1.5 million rows.

    thanks

  • March 1, 2012 at 1:47 am

    #1453483

    You could use an OUTER JOIN instead, although I suspect that you may end up with the same execution plan. Have you considered indexes on the srno column in both tables? Also, the BETWEEN operator is not needed since the beginning and end of the range are identical. Use "=" instead.

    John

  • March 1, 2012 at 2:57 am

    #1453512

    Why do you want to eliminate IN? It's usually more efficient than an equivalent join.

    If srno in IFDetails is nullable, you may want to use EXISTS instead. Also your first predicate is not SARGable, so that may result in the query being a little slower than it could be.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 2, 2012 at 12:54 am

    #1454039

    thanks for reply,

    as far as I know that, Join is much faster then IN.

    i am sorry but i can't understand what you mean by saying :

    'Also your first predicate is not SARGable, so that may result in the query being a little slower than it could be.'

    thanks for giving me time.

  • March 2, 2012 at 1:50 am

    #1454056

    There's plenty of information out there. Use your favourite search engine to search for "sargable predicates".

    John

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply