July 7, 2014 at 8:01 am
Hello,
I have the following code
July 7, 2014 at 8:29 am
If you replace the sub-selects to regular JOINs it will certanly clearify a lot and most likely improve performance. To make the query sargeble you also need to remove the RTRIM on column pc.CODE in the JOIN statement (if possible).
SELECT *
INTO #Timesheet
FROM (
SELECT s.Sheet_ID
, s.Sheet_Month
, s.Sheet_Year
, s.Sheet_Period
, s.Date_Start
, s.Date_End
, s.Date_Submit
, s.Date_Approved
, s.PD
, s.Emp_ID
, s.Dept_ID
, s.Emp_Status
, s.Comments
, s.Supervisor_ID
, s.Sheet_Status
, s.Submit_ID
, s.Approve_ID
, (
SELECT SUM(Record_Hours)
FROM Pay_Timesheets_Record sr
WHERE sr.Sheet_ID = s.Sheet_ID
) AS TOTAL_Sheet
, super.Last_Name + ', ' + super.First_Name AS Supervisor_Name
, submit.Last_Name + ', ' + submit.First_Name AS Submit_Name
, submit.Email AS Submit_Email
, approve.Last_Name + ', ' + approve.First_Name AS Approve_Name
, approve.Email AS Approve_Email
, DAY(r.Record_Date) AS Record_Day
, r.Record_Activity
, r.Record_ID
, r.Record_Date
, r.Cost_Center_Charge
, r.Sub_Ledger_No
, r.Record_Hours
, r.Record_Category
, r.Pay_Code
, r.Record_Mark
, pc.Description
FROM Pay_Timesheets_Sheet s
INNER JOIN Pay_Timesheets_Record r ON s.Sheet_ID = r.Sheet_ID
LEFT JOIN qry_Pay_Codes pc ON r.Pay_Code = (RTRIM(pc.CODE))
LEFT JOIN CRS_Master.dbo.qry_CRS_Employee_Info super ON super.Emp_ID = s.Supervisor_ID
LEFT JOIN CRS_Master.dbo.qry_CRS_Employee_Info submit ON submit.Emp_ID = s.Submit_ID
LEFT JOIN CRS_Master.dbo.qry_CRS_Employee_Info approve ON approve.Emp_ID = s.Approve_ID
WHERE s.Sheet_ID = @Sheet_ID
) Timesheet
July 7, 2014 at 8:32 am
Krasavita (7/7/2014)
Hello,I have the following code and creates slow performance what else can I make it simpler? (Thank you very much:
SELECT * INTO #Timesheet
FROM (
SELECT s.Sheet_ID, s.Sheet_Month, s.Sheet_Year, s.Sheet_Period,
s.Date_Start, s.Date_End, s.Date_Submit, s.Date_Approved, s.PD,
s.Emp_ID, s.Dept_ID, s.Emp_Status, s.Comments, s.Supervisor_ID,
s.Sheet_Status, s.Submit_ID, s.Approve_ID,
(SELECT SUM(Record_Hours) FROM Pay_Timesheets_Record sr
WHERE sr.Sheet_ID = s.Sheet_ID) AS TOTAL_Sheet,
(SELECT (sp.Last_Name + ', ' + sp.First_Name) FROM CRS_Master.dbo.qry_CRS_Employee_Info sp
WHERE sp.Emp_ID = s.Supervisor_ID) AS Supervisor_Name,
(SELECT (sp.Last_Name + ', ' + sp.First_Name) FROM CRS_Master.dbo.qry_CRS_Employee_Info sp
WHERE sp.Emp_ID = s.Submit_ID) AS Submit_Name,
(SELECT (sp.Email) FROM CRS_Master.dbo.qry_CRS_Employee_Info sp
WHERE sp.Emp_ID = s.Submit_ID) AS Submit_Email,
(SELECT (sp.Last_Name + ', ' + sp.First_Name) FROM CRS_Master.dbo.qry_CRS_Employee_Info sp
WHERE sp.Emp_ID = s.Approve_ID) AS Approve_Name,
(SELECT (sp.Email) FROM CRS_Master.dbo.qry_CRS_Employee_Info sp
WHERE sp.Emp_ID = s.Approve_ID) AS Approve_Email, DAY(r.Record_Date) AS Record_Day,
r.Record_Activity, r.Record_ID, r.Record_Date, r.Cost_Center_Charge,
r.Sub_Ledger_No, r.Record_Hours, r.Record_Category, r.Pay_Code, r.Record_Mark,
pc.Description
FROM Pay_Timesheets_Sheet s
INNER JOIN Pay_Timesheets_Record r ON s.Sheet_ID = r.Sheet_ID
LEFT OUTER JOIN qry_Pay_Codes pc ON r.Pay_Code = (RTRIM(pc.CODE))
WHERE s.Sheet_ID = @Sheet_ID) Timesheet
Ouch. Let's start with some formatting...
SELECT *
INTO #Timesheet
FROM (
SELECT s.Sheet_ID
,s.Sheet_Month
,s.Sheet_Year
,s.Sheet_Period
,s.Date_Start
,s.Date_End
,s.Date_Submit
,s.Date_Approved
,s.PD
,s.Emp_ID
,s.Dept_ID
,s.Emp_Status
,s.Comments
,s.Supervisor_ID
,s.Sheet_Status
,s.Submit_ID
,s.Approve_ID
,(
SELECT SUM(Record_Hours)
FROM Pay_Timesheets_Record sr
WHERE sr.Sheet_ID = s.Sheet_ID
) AS TOTAL_Sheet
,(
SELECT (sp.Last_Name + ', ' + sp.First_Name)
FROM CRS_Master.dbo.qry_CRS_Employee_Info sp
WHERE sp.Emp_ID = s.Supervisor_ID
) AS Supervisor_Name
,(
SELECT (sp.Last_Name + ', ' + sp.First_Name)
FROM CRS_Master.dbo.qry_CRS_Employee_Info sp
WHERE sp.Emp_ID = s.Submit_ID
) AS Submit_Name
,(
SELECT (sp.Email)
FROM CRS_Master.dbo.qry_CRS_Employee_Info sp
WHERE sp.Emp_ID = s.Submit_ID
) AS Submit_Email
,(
SELECT (sp.Last_Name + ', ' + sp.First_Name)
FROM CRS_Master.dbo.qry_CRS_Employee_Info sp
WHERE sp.Emp_ID = s.Approve_ID
) AS Approve_Name
,(
SELECT (sp.Email)
FROM CRS_Master.dbo.qry_CRS_Employee_Info sp
WHERE sp.Emp_ID = s.Approve_ID
) AS Approve_Email
,DAY(r.Record_Date) AS Record_Day
,r.Record_Activity
,r.Record_ID
,r.Record_Date
,r.Cost_Center_Charge
,r.Sub_Ledger_No
,r.Record_Hours
,r.Record_Category
,r.Pay_Code
,r.Record_Mark
,pc.Description
FROM Pay_Timesheets_Sheet s
INNER JOIN Pay_Timesheets_Record r ON s.Sheet_ID = r.Sheet_ID
LEFT JOIN qry_Pay_Codes pc ON r.Pay_Code = (RTRIM(pc.CODE))
WHERE s.Sheet_ID = @Sheet_ID
) Timesheet
Now that we can actually read this we need some details to help us along.
I would start by just working with your select statement. This should be the same thing with a lot less subqueries.
SELECT s.Sheet_ID
,s.Sheet_Month
,s.Sheet_Year
,s.Sheet_Period
,s.Date_Start
,s.Date_End
,s.Date_Submit
,s.Date_Approved
,s.PD
,s.Emp_ID
,s.Dept_ID
,s.Emp_Status
,s.Comments
,s.Supervisor_ID
,s.Sheet_Status
,s.Submit_ID
,s.Approve_ID
,SUM(ptr.Record_Hours)
, sup.Last_Name + ', ' + sup.First_Name AS Supervisor_Name
, submit.Last_Name + ', ' + sp.First_Name AS Submit_Name
, submit.Email AS Submit_Email
, approve.Last_Name + ', ' + sp.First_Name AS Approve_Name
, approve.Email AS Approve_Email
,DAY(r.Record_Date) AS Record_Day
,r.Record_Activity
,r.Record_ID
,r.Record_Date
,r.Cost_Center_Charge
,r.Sub_Ledger_No
,r.Record_Hours
,r.Record_Category
,r.Pay_Code
,r.Record_Mark
,pc.Description
FROM Pay_Timesheets_Sheet s
INNER JOIN Pay_Timesheets_Record r ON s.Sheet_ID = r.Sheet_ID
LEFT JOIN qry_Pay_Codes pc ON r.Pay_Code = (RTRIM(pc.CODE))
LEFT JOIN Pay_Timesheets_Record ptr on ptr.Sheet_ID = s.Sheet_ID
LEFT JOIN CRS_Master.dbo.qry_CRS_Employee_Info sup on sup.Emp_ID = s.Supervisor_ID
LEFT JOIN CRS_Master.dbo.qry_CRS_Employee_Info submit on submit.Emp_ID = s.Submit_ID
LEFT JOIN CRS_Master.dbo.qry_CRS_Employee_Info approve on approve.Emp_ID = s.Approve_ID
WHERE s.Sheet_ID = @Sheet_ID
group by s.Sheet_ID
,s.Sheet_Month
,s.Sheet_Year
,s.Sheet_Period
,s.Date_Start
,s.Date_End
,s.Date_Submit
,s.Date_Approved
,s.PD
,s.Emp_ID
,s.Dept_ID
,s.Emp_Status
,s.Comments
,s.Supervisor_ID
,s.Sheet_Status
,s.Submit_ID
,s.Approve_ID
,SUM(ptr.Record_Hours)
, sup.Last_Name + ', ' + sup.First_Name
, submit.Last_Name + ', ' + sp.First_Name
, submit.Email
, approve.Last_Name + ', ' + sp.First_Name
, approve.Email
,DAY(r.Record_Date)
,r.Record_Activity
,r.Record_ID
,r.Record_Date
,r.Cost_Center_Charge
,r.Sub_Ledger_No
,r.Record_Hours
,r.Record_Category
,r.Pay_Code
,r.Record_Mark
,pc.Description
If that doesn't work then you will need to post ddl and sample data per the article found at the first link in my signature.
_______________________________________________________________
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/
July 7, 2014 at 8:54 am
After you restructure it as was suggested already, this needs to be addressed:
(RTRIM(pc.CODE))
That will lead to scans which will seriously hurt performance. You shouldn't have functions on columns like that.
"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
July 7, 2014 at 8:59 am
Msg 306, Level 16, State 2, Line 57
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Msg 4104, Level 16, State 1, Line 64
The multi-part identifier "sp.First_Name" could not be bound.
July 7, 2014 at 9:03 am
Grant Fritchey (7/7/2014)
After you restructure it as was suggested already, this needs to be addressed:
(RTRIM(pc.CODE))That will lead to scans which will seriously hurt performance. You shouldn't have functions on columns like that.
Especially since SQL ignores trailing spaces when comparing strings....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2014 at 9:06 am
Krasavita (7/7/2014)
Msg 306, Level 16, State 2, Line 57The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Msg 4104, Level 16, State 1, Line 64
The multi-part identifier "sp.First_Name" could not be bound.
Why are you using (n)text for a First_Name column? That datatype has been deprecated and it total overkill for a first name anyway. Change that column to a (n)varchar(SomeReasonableSize) and this won't be an issue.
_______________________________________________________________
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/
July 7, 2014 at 9:29 am
Here's an alternative using APPLY:
SELECT s.Sheet_ID, s.Sheet_Month, s.Sheet_Year, s.Sheet_Period,
s.Date_Start, s.Date_End, s.Date_Submit, s.Date_Approved, s.PD,
s.Emp_ID, s.Dept_ID, s.Emp_Status, s.Comments, s.Supervisor_ID,
s.Sheet_Status, s.Submit_ID, s.Approve_ID,
TOTAL_Sheet = SUM(r.Record_Hours) OVER(PARTITION BY r.Sheet_ID),
sp1.Supervisor_Name,
sp2.Submit_Name,
sp2.Submit_Email,
sp3.Approve_Name,
sp3.Approve_Email,
DAY(r.Record_Date) AS Record_Day,
r.Record_Activity, r.Record_ID, r.Record_Date, r.Cost_Center_Charge,
r.Sub_Ledger_No, r.Record_Hours, r.Record_Category, r.Pay_Code, r.Record_Mark,
pc.[Description]
INTO #Timesheet
FROM Pay_Timesheets_Sheet s
INNER JOIN Pay_Timesheets_Record r
ON s.Sheet_ID = r.Sheet_ID
LEFT OUTER JOIN qry_Pay_Codes pc
--ON r.Pay_Code = RTRIM(pc.CODE)
ON r.Pay_Code = pc.CODE
OUTER APPLY (
SELECT Supervisor_Name = sp.Last_Name + ', ' + sp.First_Name
FROM CRS_Master.dbo.qry_CRS_Employee_Info sp
WHERE sp.Emp_ID = s.Supervisor_ID
) sp1
OUTER APPLY (
SELECT Submit_Email = sp.Email,
Submit_Name = sp.Last_Name + ', ' + sp.First_Name
FROM CRS_Master.dbo.qry_CRS_Employee_Info sp
WHERE sp.Emp_ID = s.Submit_ID
) sp2
OUTER APPLY (
SELECT Approve_Email = sp.Email,
Approve_Name = sp.Last_Name + ', ' + sp.First_Name
FROM CRS_Master.dbo.qry_CRS_Employee_Info sp
WHERE sp.Emp_ID = s.Approve_ID
) sp3
WHERE s.Sheet_ID = @Sheet_ID
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
July 7, 2014 at 11:16 am
How can I fix it (RTRIM(pc.CODE))?
July 7, 2014 at 11:18 am
Krasavita (7/7/2014)
How can I fix it (RTRIM(pc.CODE))?
Just like Chris showed you above. Just take it out. It doesn't do anything except slow down your code. Two values are equivalent when the only difference is spaces on the right side.
_______________________________________________________________
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/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply