how can make this SQL Server query faster

20 pts.
Tags:
SQL Query
SQL Server 2008
SQL stored procedures
T-SQL
All,

how can I make this query faster?

Here is my code of stored procedure:
ALTER procedure [dbo].[GetLMPReport]
(
@typeId tinyint,
@dataType varchar(2),
@date date
)

AS

begin

select distinct p.name, ISNULL (td24.price, 0) as '00', ISNULL (td1.price, 0) as '100', ISNULL (td2.price, 0) as '200', ISNULL (td3.price, 0) as '300', ISNULL (td4.price, 0) as '400', ISNULL (td5.price, 0) as '500', ISNULL (td6.price, 0) as '600', ISNULL (td7.price, 0) as '700', ISNULL (td8.price, 0) as '800', ISNULL (td9.price, 0) as '900', 
ISNULL (td10.price, 0) as '1000', ISNULL (td11.price, 0) as '1100', ISNULL (td12.price, 0) as '1200', ISNULL (td13.price, 0) as '1300', ISNULL (td14.price, 0) as '1400', ISNULL (td15.price, 0) as '1500'
, ISNULL (td16.price, 0) as '1600', ISNULL (td17.price, 0) as '1700', ISNULL (td18.price, 0) as '1800', ISNULL (td19.price, 0) as '1900', ISNULL (td20.price, 0) as '2000', ISNULL (td21.price, 0) as '2100', ISNULL (td22.price, 0) as '2200', ISNULL (td23.price, 0) as '2300',  dbo.LMPReportAvg(p.pointId, @dataType, @date, @date) as AVG, dbo.LMPReportPeakAvg(p.pointId, @dataType, @date, @date) as Peak_AVG, dbo.LMPReportOffPeakAvg(p.pointId, @dataType, @date, @date) as OffPeak_AVG
 from TradeData td1 
full join TradeData td2 on td1.pointId=td2.pointId  and td1.dataType=td2.dataType and datepart(HOUR,td2.timestamp) ='2' 
full join TradeData td3 on td1.pointId=td3.pointId and convert(date,td1.timestamp,101)=convert(date,td3.timestamp,101)
  and td3.dataType=td1.dataType and  datepart(HOUR,td3.timestamp) ='3' 
full join TradeData td4 on td1.pointId=td4.pointId and convert(date,td1.timestamp,101)=convert(date,td4.timestamp,101)
 and td4.dataType=td1.dataType  and datepart(HOUR,td4.timestamp) ='4' 
full join TradeData td5 on td1.pointId=td5.pointId and convert(date,td1.timestamp,101)=convert(date,td5.timestamp,101)
 and td5.dataType=td1.dataType and datepart(HOUR,td5.timestamp) ='5' 
full join TradeData td6 on td1.pointId=td6.pointId and convert(date,td1.timestamp,101)=convert(date,td6.timestamp,101)
and td6.dataType=td1.dataType and datepart(HOUR,td6.timestamp) ='6' 
full join TradeData td7 on td1.pointId=td7.pointId and convert(date,td1.timestamp,101)=convert(date,td7.timestamp,101)
and td7.dataType=td1.dataType and datepart(HOUR,td7.timestamp) ='7' 
full join TradeData td8 on td1.pointId=td8.pointId and convert(date,td1.timestamp,101)=convert(date,td8.timestamp,101)
and td8.dataType=td1.dataType  and datepart(HOUR,td8.timestamp) ='8' 
full join TradeData td9 on td1.pointId=td9.pointId and convert(date,td1.timestamp,101)=convert(date,td9.timestamp,101)
 and td9.dataType=td1.dataType and datepart(HOUR,td9.timestamp) ='9' 
full join TradeData td10 on td1.pointId=td10.pointId and convert(date,td1.timestamp,101)=convert(date,td10.timestamp,101)
and td10.dataType=td1.dataType and datepart(HOUR,td10.timestamp) ='10' 
full join TradeData td11 on td1.pointId=td11.pointId and convert(date,td1.timestamp,101)=convert(date,td11.timestamp,101)
and td11.dataType=td1.dataType and datepart(HOUR,td11.timestamp) ='11' 
full join TradeData td12 on td1.pointId=td12.pointId and convert(date,td1.timestamp,101)=convert(date,td12.timestamp,101)
and td12.dataType=td1.dataType and datepart(HOUR,td12.timestamp) ='12' 
full join TradeData td13 on td1.pointId=td13.pointId and convert(date,td1.timestamp,101)=convert(date,td13.timestamp,101)
and td13.dataType=td1.dataType and datepart(HOUR,td13.timestamp) ='13' 
full join TradeData td14 on td1.pointId=td14.pointId and convert(date,td1.timestamp,101)=convert(date,td14.timestamp,101)
and td14.dataType=td1.dataType and datepart(HOUR,td14.timestamp) ='14' 
full join TradeData td15 on td1.pointId=td15.pointId and convert(date,td1.timestamp,101)=convert(date,td15.timestamp,101)
and td15.dataType=td1.dataType and datepart(HOUR,td15.timestamp) ='15' 
full join TradeData td16 on td1.pointId=td16.pointId and convert(date,td1.timestamp,101)=convert(date,td16.timestamp,101)
and td16.dataType=td1.dataType and datepart(HOUR,td16.timestamp) ='16' 
full join TradeData td17 on td1.pointId=td17.pointId and convert(date,td1.timestamp,101)=convert(date,td17.timestamp,101)
and td17.dataType=td1.dataType and datepart(HOUR,td17.timestamp) ='17' 
full join TradeData td18 on td1.pointId=td18.pointId and convert(date,td1.timestamp,101)=convert(date,td18.timestamp,101)
and td18.dataType=td1.dataType and datepart(HOUR,td18.timestamp) ='18' 
full join TradeData td19 on td1.pointId=td19.pointId and convert(date,td1.timestamp,101)=convert(date,td19.timestamp,101)
 and td19.dataType=td1.dataType and datepart(HOUR,td19.timestamp) ='19' 
full join TradeData td20 on td1.pointId=td20.pointId and convert(date,td1.timestamp,101)=convert(date,td20.timestamp,101)
and td20.dataType=td1.dataType and datepart(HOUR,td20.timestamp) ='20' 
full join TradeData td21 on td1.pointId=td21.pointId and convert(date,td1.timestamp,101)=convert(date,td21.timestamp,101)
  and  td21.dataType=td1.dataType and datepart(HOUR,td21.timestamp) ='21' 
full join TradeData td22 on td1.pointId=td22.pointId and convert(date,td1.timestamp,101)=convert(date,td22.timestamp,101)
and td22.dataType=td1.dataType and datepart(HOUR,td22.timestamp) ='22' 
full join TradeData td23 on td1.pointId=td23.pointId and convert(date,td1.timestamp,101)=convert(date,td23.timestamp,101)
and td23.dataType=td1.dataType and datepart(HOUR,td23.timestamp) ='23' 
full join TradeData td24 on td1.pointId=td24.pointId and convert(date,td1.timestamp,101)=convert(date,td24.timestamp,101)
 and td24.dataType=td1.dataType and datepart(HOUR,td24.timestamp) ='0' 
  join Point p on p.pointId=td1.pointId join Type on Type.typeId=p.typeId
  where convert(date,td1.timestamp,101) = @date and td1.dataType=@dataType
   and Type.typeId=@typeId and p.LMPReport= 1 and datepart(HOUR,td1.timestamp) = '1' 
and convert(date,td1.timestamp,101)=convert(date,td2.timestamp,101)
   
end
						
					
ASKED: December 3, 2008  6:10 PM
UPDATED: October 14, 2013  8:01 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

The convert statements are what will be killing you. They remove SQL Server’s ability to use the indexes.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following