August 4, 2011 at 8:17 am
Hello,
Can someone please help me to re do this select statment,for better performance.
SELECT Mth, Yry, Prd,
(SELECT (RTRIM(e.First_Name) + ' ' + e.Last_Name) FROM vwEmployee_Info e
WHERE e.Emp_ID = s.Emp_ID) AS Emp_Name_Display,
(SELECT Email FROM vwEmployee_Info e
WHERE e.Emp_ID = s.Emp_ID) AS Emp_Email,
(SELECT (RTRIM(e.First_Name) + ' ' + e.Last_Name) FROM vwEmployee_Info e
WHERE e.Emp_ID = s.Submit_ID) AS Submit_Name_Display,
(SELECT Email FROM vwEmployee_Info e
WHERE e.Emp_ID = s.Submit_ID) AS Submit_Email,
(SELECT (RTRIM(e.First_Name) + ' ' + e.Last_Name) FROM vwEmployee_Info e
WHERE e.Emp_ID = s.Supervisor_ID) AS Supervisor_Name_Display,
(SELECT Email FROM vwEmployee_Info e
WHERE e.Emp_ID = s.Supervisor_ID) AS S_Email,
(SELECT (RTRIM(e.First_Name) + ' ' + e.Last_Name) FROM vwEmployee_Info e
WHERE e.Emp_ID = s.Approve_ID) AS Ap_Name_Display,
(SELECT Email FROM vwEmployee_Info e
WHERE e.Emp_ID = s.Approve_ID) AS A_Email
FROM tblInfo s
WHERE s.S_id = @s_id
Thank you
August 4, 2011 at 8:26 am
This code is simplified, not so sure about a performance gain though. You might find more gain from looking into the vw_EmployeeInfo view.
SELECT
s.Mth,
s.Yry,
s.Prd,
RTRIM(e.First_Name) + ' ' + e.Last_Name AS Emp_Name_Display,
e.Email AS Emp_Email,
RTRIM(sub.First_Name) + ' ' + sub.Last_Name AS Submit_Name_Display,
sub.Email AS Submit_Email,
RTRIM(sup.First_Name) + ' ' + sup.Last_Name AS Supervisor_Name_Display,
sup.Email AS S_Email,
RTRIM(app.First_Name) + ' ' + app.Last_Name AS Ap_Name_Display,
app.Email AS A_Email
FROM tblInfo s
LEFT JOIN vwEmployee_Info emp
ON emp.Emp_ID = s.Emp_ID
LEFT JOIN vwEmployee_Info sub
ON sub.Emp_ID = s.Submit_ID
LEFT JOIN vwEmployee_Info sup
ON sup.Emp_ID = s.Supervisor_ID
LEFT JOIN vwEmployee_Info app
ON app.Emp_ID = s.Approve_ID
WHERE
s.S_id = @s_id
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgAugust 4, 2011 at 8:28 am
Please post the actual execution plan and we'll see what we can do.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply