Query Improvement

  • When I executet his query it takes a lot of time...Can anyone plz help in this?

    SELECT id, english_name

    FROM ys_movements

    WHERE is_private =0

    AND id

    IN (

    SELECT object_id

    FROM profiles_useritems

    WHERE content_type_id = (

    SELECT id

    FROM django_content_type

    WHERE name ="movements"

    AND app_label ="yoga")

    AND user_id

    IN (

    SELECT target_user_id

    FROM follow_follow

    WHERE user_id =8

    AND target_user_id NOT

    IN (

    SELECT id

    FROM auth_user

    WHERE is_staff =1

    )))

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Hi,

    What does its execution plan say? Are there any missing indexes? How big are the tables? Check the statistics on them.

    You can replace the IN with EXISTS and NOT EXISTS.

    Regards

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • IgorMi (5/9/2013)


    Hi,

    What does its execution plan say? Are there any missing indexes? How big are the tables? Check the statistics on them.

    You can replace the IN with EXISTS and NOT EXISTS.

    Regards

    IgorMi

    INdexes are there on the columns.. for less data it works fine but when data gets increased it takes more time to execute

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil_kk (5/9/2013)


    IgorMi (5/9/2013)


    Hi,

    What does its execution plan say? Are there any missing indexes? How big are the tables? Check the statistics on them.

    You can replace the IN with EXISTS and NOT EXISTS.

    Regards

    IgorMi

    INdexes are there on the columns.. for less data it works fine but when data gets increased it takes more time to execute

    Post the ACTUAL execution plan as a .sqlplan attachment so folks can analyse it.

    Here's a close equivalent written in a more conventional style for your perusal:

    SELECT y.id, y.english_name

    FROM ys_movements y

    INNER JOIN (

    SELECT DISTINCT p.object_id

    FROM profiles_useritems p

    INNER JOIN django_content_type d

    ON d.id = p.content_type_id

    INNER JOIN follow_follow f

    ON f.target_user_id = d.user_id

    AND f.user_id = 8

    WHERE d.name = 'movements'

    AND d.app_label = 'yoga')

    AND NOT EXISTS (

    SELECT 1

    FROM auth_user a

    WHERE a.is_staff = 1

    AND a.id = d.user_id)

    ) f

    ON f.object_id = y.id

    WHERE y.is_private =0

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (5/9/2013)


    kapil_kk (5/9/2013)


    IgorMi (5/9/2013)


    Hi,

    What does its execution plan say? Are there any missing indexes? How big are the tables? Check the statistics on them.

    You can replace the IN with EXISTS and NOT EXISTS.

    Regards

    IgorMi

    INdexes are there on the columns.. for less data it works fine but when data gets increased it takes more time to execute

    Post the ACTUAL execution plan as a .sqlplan attachment so folks can analyse it.

    Here's a close equivalent written in a more conventional style for your perusal:

    SELECT y.id, y.english_name

    FROM ys_movements y

    INNER JOIN (

    SELECT DISTINCT p.object_id

    FROM profiles_useritems p

    INNER JOIN django_content_type d

    ON d.id = p.content_type_id

    INNER JOIN follow_follow f

    ON f.target_user_id = d.user_id

    AND f.user_id = 8

    WHERE d.name = 'movements'

    AND d.app_label = 'yoga')

    AND NOT EXISTS (

    SELECT 1

    FROM auth_user a

    WHERE a.is_staff = 1

    AND a.id = d.user_id)

    ) f

    ON f.object_id = y.id

    WHERE y.is_private =0

    Kapil, as IgorMi and Chris suggested, EXISTS and NOT EXISTS works way faster that IN and NOT IN. The above query is perfect example of how it should be used. Eventually it gets better as the number of rows grow. cheers. πŸ™‚

  • sqlnaive (5/9/2013)


    ChrisM@Work (5/9/2013)


    kapil_kk (5/9/2013)


    IgorMi (5/9/2013)


    Hi,

    What does its execution plan say? Are there any missing indexes? How big are the tables? Check the statistics on them.

    You can replace the IN with EXISTS and NOT EXISTS.

    Regards

    IgorMi

    INdexes are there on the columns.. for less data it works fine but when data gets increased it takes more time to execute

    Post the ACTUAL execution plan as a .sqlplan attachment so folks can analyse it.

    Here's a close equivalent written in a more conventional style for your perusal:

    SELECT y.id, y.english_name

    FROM ys_movements y

    INNER JOIN (

    SELECT DISTINCT p.object_id

    FROM profiles_useritems p

    INNER JOIN django_content_type d

    ON d.id = p.content_type_id

    INNER JOIN follow_follow f

    ON f.target_user_id = d.user_id

    AND f.user_id = 8

    WHERE d.name = 'movements'

    AND d.app_label = 'yoga')

    AND NOT EXISTS (

    SELECT 1

    FROM auth_user a

    WHERE a.is_staff = 1

    AND a.id = d.user_id)

    ) f

    ON f.object_id = y.id

    WHERE y.is_private =0

    Kapil, as IgorMi and Chris suggested, EXISTS and NOT EXISTS works way faster that IN and NOT IN. The above query is perfect example of how it should be used. Eventually it gets better as the number of rows grow. cheers. πŸ™‚

    Not suggested by me. The performance of EXISTS and IN are very similar. Here's evidence [/url]and an explanation.

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Thanks all for the suggestion πŸ™‚

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Generally, but not always, instead of IN I would be doing a JOIN. The optimizer might interpret it that way any for you. Since you're going for matching records that may be a simpler and more clear approach. Other than that, I'd have to see the actual execution plan to understand where you may be experiencing performance issues.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 8 posts - 1 through 7 (of 7 total)

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