September 27, 2011 at 12:38 pm
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
September 27, 2011 at 12:44 pm
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/
September 27, 2011 at 12:56 pm
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