how can make this SQL Server query faster

20 pts.
SQL Query
SQL Server 2008
SQL stored procedures

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



select distinct, 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)

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.

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.

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


Share this item with your network: