Solved

Optimize the SQL query

Posted on 2011-02-15
14
385 Views
Last Modified: 2012-05-11
I am sharing a SQL query which is taking 25 minutes to execute

Please help me to optimize

ALTER PROCEDURE [dbo].[unbilledMISReport]
	-- Add the parameters for the stored procedure here
	@uploaddate as varchar(20)
AS
BEGIN
	SET NOCOUNT ON;
declare @sql nvarchar(max)
set @sql =
'Select HAR.PersonNo as PersonNo,HAR.EmployeeName as EmployeeName,HAR.Period as Period,'+
'HAR.ProfitCenter as ProfitCenter,HAR.CostCenter as CostCenter,HAR.ProjectTime as ProjectTime,'+
'YTC.Text as Text,Null as Status,Null as Remark,HAR.Account as Account, HAR.ProjectNumber as ProjectNumber,'+
'CONVERT(VARCHAR(10),HAR.Date, 120) as Date,PC.ProjectTLE as ProjectTLE,EMD.BaseLocation as BaseLocation,'+
'ETES.ManagerName as ManagerName,EMD.ORGManager1 as ORGManager1,HAR.Vertical as Vertical,'+
'ICE.Manual_Auto as Manual_Auto,ICE.GDS_NonGDS as GDS_NonGDS,'+
'convert(varchar(10),GPS.ResourceStartDate, 120) as ResourceStartDate,convert(varchar(10),GPS.ResourceEndDate, 120) as ResourceEndDate,'+
'GPS.Status as Status_Act, GPS.BillingKey as BK,PC.SoldToPt as SoldToPt,AM.AccountDescription as AccountDescription,'+
'ETES.EmployeeEmailID as EmployeeEmail,ETES.ManagerEmailID as EtesManagerEmail,'+
'CONVERT(VARCHAR(10),HAR.UploadDate, 120) As UploadDate  '+

'From WeeklyHARReport HAR '+

'left outer Join (select * from (select x.* ,row_number() over (partition by (cast(PersonNo as varchar)+cast(cast(ObjectID as datetime) as varchar)) order by objectid ) as rn from YTCError as x) as y where rn=1) as YTC '+
'on (cast(HAR.PersonNo as varchar) + Cast(cast(HAR.Date as datetime)as varchar)) = '+
'(cast(YTC.PersonNo as varchar) + cast(cast(YTC.ObjectID as datetime) as varchar)) '+

'left outer Join (select * from (select a.* ,row_number() over (partition by ShortIdentification order by ShortIdentification) as rn1 from ProjectConsolidated as a) as b where rn1=1 ) as PC '+
'on HAR.ProjectNumber = PC.ShortIdentification  '+

'left outer Join (select * from (select a.* ,row_number() over (partition by EmpID order by EmpID) as rn1	from ETesMgrEmp as a) as b where rn1=1 ) as ETES '+
'on HAR.PersonNo = ETES.EmpID  '+

'left outer Join (select * from (select a.* ,row_number() over (partition by EmployeeID order by EmployeeID) as rn1	 from EmanageDump as a) as b where rn1=1 ) as EMD '+
'on HAR.PersonNo = EMD.EmployeeID '+

'left outer Join (select * from (select a.* ,row_number() over (partition by SoldToParty order by SoldToParty) as rn1	from ICEntities as a) as b where rn1=1 ) as ICE '+
'on PC.SoldToPt = ICE.SoldToParty '+

'left outer Join (select * from (select a.* ,row_number() over (partition by (WBSCode + EmployeeID) order by EmployeeID) as rn1	  from GPSF as a) as b where CONVERT(VARCHAR(10),UploadDate, 120) = '''+  @uploaddate +''') as GPS '+
'on (HAR.PersonNo + PC.ProjectTLE)  = (GPS.WBSCode + GPS.EmployeeID) '+

'left outer Join (select * from (select a.* ,row_number() over (partition by Account order by Account) as rn1	from AccountMaster as a) as b where rn1=1 ) as AM '+
'on HAR.Account = AM.Account '+

'Where  '+

'CONVERT(VARCHAR(10),HAR.UploadDate, 120) = '''+  @uploaddate +'''   and HAR.PersonNo is not null '+

'order by HAR.EmployeeName '

--print @sql


exec sp_executesql @sql,N'@uploaddate varchar(20)',@uploaddate  
END

Open in new window

0
Comment
Question by:ExpertHelp79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
14 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 34894878
use  WITH (NOLOCK) for from tables
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 34894889
0
 
LVL 7

Expert Comment

by:mkobrin
ID: 34894892
It is a bit difficult to optimise the code if you do not know the structure and indexes of all the tables involoved.

First thing to check is that you have an index on the columns referenced in the where clause:
HAR.UploadDate,HAR.PersonNo

Then all the joined tables will also need to be indexed:
YTC.PersonNo, YTC.ObjectID
PC.ShortIdentification  
ETES.EmpID  
ICE.SoldToParty
GPS.WBSCode, GPS.EmployeeID
AM.Account

Also joining two columns in the = clause of a joined table could be very expensive.

Try the indexes(if they don't exist) and if they do exist then rather change this part of the join query:
on (HAR.PersonNo + PC.ProjectTLE)  = (GPS.WBSCode + GPS.EmployeeID)  to:
on (HAR.PersonNo = GPS.WBSCode and  PC.ProjectTLE = GPS.EmployeeID)
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34894917
1. don't use sp_executesql...  just run the query directly.
2. please explain the relationships between the tables... the row_number() Over is probably expensive
3. please explain why the har.person/har.date and ytc.person/ytc.objectiid;s have to be cast and concatenated?
4. similarly why do (HAR.PersonNo + PC.ProjectTLE)  = (GPS.WBSCode + GPS.EmployeeID) need concatenation
5,  what does the contents of the @uploaddte parameter look like?
       CONVERT(VARCHAR(10),HAR.UploadDate, 120) = '''+  @uploaddate +''' could probably be better expressed...


the above are just the immediate areas to look at...
the functions and concatenation will have drastically reduced the ability of the query to use any indexes
efficiently... which will be the next area to look at once the actual query has been reformatted.


0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34895270
1. don't use sp_executesql...  just run the query directly.

I wrote as it shows me the exact query


2. please explain the relationships between the tables... the row_number() Over is probably expensive
Yes that is costly but it helps me to pick only the first row of the duplicate entries


3. please explain why the har.person/har.date and ytc.person/ytc.objectiid;s have to be cast and concatenated?

This we are creating a unique code

4. similarly why do (HAR.PersonNo + PC.ProjectTLE)  = (GPS.WBSCode + GPS.EmployeeID) need concatenation

This we are creating and comparing a unique code


5,  what does the contents of the @uploaddte parameter look like?
       CONVERT(VARCHAR(10),HAR.UploadDate, 120) = '''+  @uploaddate +''' could probably be better expressed...
 it gives us yyyy-mm-dd

the above are just the immediate areas to look at...
the functions and concatenation will have drastically reduced the ability of the query to use any indexes
efficiently... which will be the next area to look at once the actual query has been reformatted.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34895333
we would need the explain plan of the query to see where indexes are missing, eventually.
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34895361
how to make the plan
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34895525
in the query menu, tick "include actual explain plan"
0
 
LVL 7

Expert Comment

by:mkobrin
ID: 34895558
in SQL management studio there is an icon for show excecution plan
excecutionPlan.PNG
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 34895753
1. don't use sp_executesql...  just run the query directly.

I wrote as it shows me the exact query

     Yes but it is not efficient

2. please explain the relationships between the tables... the row_number() Over is probably expensive
Yes that is costly but it helps me to pick only the first row of the duplicate entries

      That is why i am asking you to tell us what the relationships between the tables are....
      we need to understand why you have duplicates on the tables....
       you shouldn't have duplicate data on your tables so we need to understand how that has come about so that the retrieval strategy can be optimised.

3. please explain why the har.person/har.date and ytc.person/ytc.objectiid;s have to be cast and concatenated?

This we are creating a unique code


     Yes that is obvious.... but normally with joins we would expect that it could be expressed as

     on Yar.person=ytc.personid
     and har.date=ytc.objectid

    if it can be expressed in  that manner then we are more likely to be able to use an index efficiently...
    the only reason to concatenate the columns together is if the data within the columns is spread across them differently on the two tables.... is that the case?


    what are the data types of the columns...


4. similarly why do (HAR.PersonNo + PC.ProjectTLE)  = (GPS.WBSCode + GPS.EmployeeID) need concatenation

This we are creating and comparing a unique code

       see above

5,  what does the contents of the @uploaddte parameter look like?
       CONVERT(VARCHAR(10),HAR.UploadDate, 120) = '''+  @uploaddate +''' could probably be better expressed...
 it gives us yyyy-mm-dd


      i am asking what you are passing in the @uploaddate parameter...
      if it is yyyy-mm-dd then the condition should be expressed as

      har.uploaddate = @uploaddate  

      which again is indexable whilst the first "isnt"

      if har.uploaddate contains a time component... then you express it as

      har.uploaddate between @uploaddate + ' 00.00.00.000' and @uploaddate + ' 23.59.59.997'
      which is still indexable as it evaluates to a constant.

the above are just the immediate areas to look at...
the functions and concatenation will have drastically reduced the ability of the query to use any indexes
efficiently... which will be the next area to look at once the actual query has been reformatted.


Please also post structre information for your tables ... (DDL)
right click on the table in management table and script out a create table...
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34897160
rename the files to SQLPlan

 ExPlan1.txt  ExPlan2.txt
0
 
LVL 3

Expert Comment

by:bhoenig
ID: 34907942
There is definitely a problem where clause.  Since you are converting the har.UploadDate, SQL Server doesn't know how to use it in an index.  Therefore, your query will always do an index scan.  It will never be able to do a index seek.  

You need to modify your where clause to:

WHERE har.UploadDate = @UploadDate

Attached are some execution plans that validates my above statement.  The two execution plans are done on a table that has an index on the DateLogged column.  Notice that the only difference in the query is that the second is converting the DateLogged column.
 
 Index Seek
 Index Scan
0
 
LVL 2

Author Closing Comment

by:ExpertHelp79
ID: 35304810
some points were effective but not all
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question