Issue in IF EXISTS

  • Hi,

    We are facing a strange problem in our stp. The query is as below

    IF EXISTS (SELECT 1 FROM tbl_A a WHERE NOT EXISTS (SELECT 1 FROM #tmpB b where b.Code = b.ParentCode and b.Category in (1,2,3) and a.MainParentId = b.MainParentId) )

    If we execute the query without IF EXISTS then it gets executed immediately but with IF EXISTS it takes long time.

    Also do note that statistics are ON on tbl_A but no primary key or index. and index is defined on #tmpB on all the columns mentioned in the query.
    Also please note, similar checks of IF EXISTS are existing in the queries and they are getting executed immediately

    Please guide.
    Saumik Vora

  • Just to confirm what you are saying: Effectively your question is why does below query run quickly:
    SELECT 1 FROM tbl_A a WHERE NOT EXISTS (SELECT 1 FROM #tmpB b where b.Code = b.ParentCode and b.Category in (1,2,3) and a.MainParentId = b.MainParentId)

    But a query like the below does not?
    IF EXISTS (SELECT 1 FROM tbl_A a WHERE NOT EXISTS (SELECT 1 FROM #tmpB b where b.Code = b.ParentCode and b.Category in (1,2,3) and a.MainParentId = b.MainParentId) )
        SELECT 1;

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • I would use a join on the two tables instead to simplify the logic and it would probably improve performance too.

    The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

  • Hi,

    Yes. thats correct

  • My previous answer was for Thom A. 
    @mark-3 Tillman yes i did change the query, but we would like to know the cause why its getting slow. also i saw irregular behaviour. Sometime it works fine but most of the time it is bad. 

    By the way tbl_A contains 600000 record at a time and #tmpB contains 20000.

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

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