Post reply

T-SQL question

  • October 18, 2002 at 2:57 pm

    #58401

    Hi,

    I amd using SQL Server 7.0. I wrote two statements to do the same work as follows. The second one is much much faster. Can somebody please explain it?

    The first stmt:

    select claim_number from history where plan_id='MTA' and

    member_id not in (select member_id from member)

    The second stmt:

    select claim_number from history h where plan_id='MTA' and not exists

    (select * from member m where m.member_id = h.member_id)

    The tables history and member have about 200,000 records. The first stmt will run hours but the second one just needs seconds.

    Thanks,

    Yin

  • October 18, 2002 at 3:12 pm

    #438923

    FOr this situation it is faster to compare on the secound simply because as the data moves into the buffer from the external query it checks that value by running a limited query, limited by m.member_id = h.member_id and expects no records to return.

    The other however will read all the records from the member table each record that comes into the buffer to verify it is not there. And I don't believe it uses an index in the NOT IN situation where the other can readily do so.

    So each time a new record out of the outer query is pulled into memory the subquery is executed thus the number of returned records varies based on the way you perform you subquery.

    Hope that makes sense.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • October 21, 2002 at 4:45 am

    #438979

    You can also write this as

    select claim_number

    from history h

    left join member m on m.member_id = h.member_id

    where plan_id='MTA' and

    AND m.member_id is null

    if member_id is the pk of member.

    Generally sub queries are executed on each row returned by the main select statement, and thus are frowned upon. If you find you are using sub queries, you need to look at all the different ways of doing a query and test on you live data volumes.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • October 21, 2002 at 9:53 am

    #439015

    Thanks to all of you. I think I got some ideas.

    Yin

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

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