t-SQL Optimization

  • Hi Guys,

    Could you please take a look at the query below and let me know how can I optimize it... I will check the indexes. I am pretty new to SQL Server.. wanted to see what changes I can do in the query that is obvious to you guys...

    Thanks,

    Laura

    SELECT TOP 300 p.member_id, p.ssn,

    p.prefix, p.fname, p.mname, p.lname, p.suffix,

    p.birthdate, p.coast_guard_ind, pe.email,

    p.create_date, uv.fname + ' ' + uv.lname AS created_by, p.update_date, uv2.fname + ' ' + uv2.lname AS updated_by

    FROM member p

    LEFT JOIN member_email pe ON pe.member_id = p.member_id

    AND pe.active_ind = 1

    LEFT JOIN vw_users uv ON uv.user_id = p.created_by

    LEFT JOIN vw_users uv2 ON uv2.user_id = p.updated_by

    WHERE 1=1

    AND p.create_date BETWEEN '2011-07-18 00:00:00.0' AND '2011-09-22 23:59:59.999'

    AND

    (

    p.first_association_code = 'GDM'

    OR

    EXISTS

    (

    SELECT 'true'

    FROM member_course pc

    INNER JOIN ps_course c ON pc.course_id = c.course_id

    INNER JOIN ps_association o ON o.org_id = c.org_id

    WHERE p.member_id = pc.member_id

    AND o.sponsor_code = 'GDM'

    AND pc.active_ind = 1

    )

    OR

    EXISTS

    (

    SELECT 'true'

    FROM member_association po

    INNER JOIN ps_association o ON o.org_id = po.association_id

    WHERE p.member_id = po.member_id

    AND o.sponsor_code = 'GDM'

    AND po.active_ind = 1

    )

    )

    AND p.active_ind = 1

    ORDER BY p.lname, p.fname

  • There just is not enough information in your post to begin to be able to help with optimizing this. I assume you want to optimize it because it is running slow?

    Take a look at Gail's article about posting performance problems [/url] to see what information to post for best results.

    ddl, sample data, row count, indexes, ddl for the views (and the tables they use) that in your above queries.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Please post the actual execution plan.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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