Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Unanswered: Time out error in mssql

    Hi,

    I am getting timeout error while executing following query


    SELECT DISTINCT USER_NAME
    FROM USERS
    WHERE (NOT EXISTS
    (SELECT *
    FROM SESSIONS_HISTORY
    WHERE (USERS.USER_NAME = USER_NAME)))

    users table containg 3600 records
    sessions_history table contains 5lakhs records
    Please help how to avoid or overcome the timeout error.


    Thanks,
    Karthikeyan

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is USER_NAME unique? If so, then remove the DISTINCT.
    Can you show us the execution plan? What is the full DDL for SESSIONS_HISTORY?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2010
    Posts
    2

    timeout error in mssql

    Hi,

    Thanks for your reply.

    I dont know how to find out the DDL, please tell me how to find out DDL.

    User_name is unique in Users table but its not unique in SEssions_history table.
    Session_history table contain 5 lakhs records in that one user may repeted more than time.

    Thanks,

    Karthik

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In SSMS right click the table and select script object to.... Do the same for any indexes for the table.

    Remove the DISTINCT - it is not needed and will slow things down.

    1 lakh = 100,000: correct? Best to stick to English if you can - I had to google that.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    At first, I suspected the part ...where (not exists (select *... is the culprit. However, I did a little test where I found that it outperforms a left join by far. It did get me to wonder what it is that makes the ...not exists... a lot quicker.

    These are the queries that I compared:
    Code:
    -- query 1
    select distinct myuser
    from users
    where (not exists (select * from history where users.myuser = myuser))
    go
    
    -- query 2
    select distinct users.myuser
    from users
    left join history on (users.myuser = history.myuser)
    where history.myuser is null
    go
    So I checked the actual execution plan, the first query runs takes 22% relative to the batch, the other the remaining 78%. After adding a index on the history.myuser, both queries balanced out a little more (45% vs 55%).
    Any first thoughts?

    oh, removing the distinct didn't do too much for me, but there's only 5000 records in the history table, and three in the users table

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Kaiowas View Post
    At first, I suspected the part ...where (not exists (select *... is the culprit. However, I did a little test where I found that it outperforms a left join by far. It did get me to wonder what it is that makes the ...not exists... a lot quicker.
    It uses an anti-semi-join. It is one of (several) reasons, but the only physical one, I never use the LOJ method for existence checking.
    Introduction to Joins - Craig Freedman's SQL Server Blog - Site Home - MSDN Blogs
    The full series on joins is excellent, but this one outlines why anti-semi-joins are optimal for this.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •