Solved

Optimize the SQL query

Posted on 2011-02-15
14
365 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
  • 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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 34894896
0
 
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 142

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 2

Author Comment

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now