TSQL Optimisation of code in SQL 2008

Hi  Does anyone know of software or of sites that teach u to optimise code to get best performances either CTE's or optimising the From Clause etc  

Is there anyone at expert exchange that could show or go through each step  
DavesmAsked:
Who is Participating?
 
JestersGrindConnect With a Mentor Commented:
Here is an article from Microsoft about query tuning.  

http://msdn.microsoft.com/en-us/library/ms176005.aspx

If you have specific examples or questions, you can ask them here and experts will be able to help you more.

Greg

0
 
DavesmAuthor Commented:
Hi greg

The attached is code i am using to get workdays betwen statuses. This code is slowing down the query badly is the another way i can code this or prehaps put it in a temp table.

Please can you give me some solutions


,WorkHours_CAPSTART_CAPCOMP = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (2) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (3) and a1.application_id = a.application_id)) 
                                         
       ,WorkHours_CAPCOMP_VALCOMP = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (3) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (17) and a1.application_id = a.application_id)) 
                                         
       ,WorkHours_VALCOMP_AIP = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (17) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (24,26,124) and a1.application_id = a.application_id))
     
      ,WorkHours_AIP_VALREQ = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (24,26,124) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (36) and a1.application_id = a.application_id))                                 
                                         
       ,WorkHours_VALREQ_VALRECD = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (36) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (37) and a1.application_id = a.application_id))
        
        ,WorkHours_VALRECD_FINDECS = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (37) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (47,48,117,125) and a1.application_id = a.application_id)) 
                                         
       ,WorkHours_FINDESC_CUSTACCFINDECS = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (47,48,117,125) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (127) and a1.application_id = a.application_id))
                                         
       ,WorkHours_CUSTACCFINDECS_ElECTINST = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (127) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (301,54) and a1.application_id = a.application_id))                                  
                                                               
       ,WorkHours_ELECINST_BONDREG = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (301,54) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (114,316) and a1.application_id = a.application_id))
       
       ,WorkHours_BONDREG_PAYOUT = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (114,316) and a1.application_id = a.application_id),
                                         (select min(Payout_EntryDate) 
                                         from nas..Payout p with(nolock) 
                                         where p.application_id = a.application_id))

Open in new window

0
 
JestersGrindCommented:
You could do all of your aggregation up front and put that in a temp table.  Then pivot the temp table to make the join easier.  I'm not sure if this will be faster or not.  I don't have any way to test it.  Please let me know how it works out.

Greg


CREATE TABLE #ApplicationStatus(Application_ID INTEGER, ApplicationStatusChange_Date DATETIME, ApplicationStatusChange VARCHAR(10))

INSERT INTO #ApplicationStatus
SELECT 
	Application_ID, 
	MIN(ApplicationstatusChange_Date), 
	CASE ApplicationStatusChange_New_Status 
		WHEN ApplicationStatusChange_New_Status = 2 THEN 'CAPSTART'
		WHEN ApplicationStatusChange_New_Status = 3 THEN 'CAPCOMP'
		WHEN ApplicationStatusChange_New_Status = 17 THEN 'VALCOMP'
		WHEN ApplicationStatusChange_New_Status IN(24,26,124) THEN 'AIP'
		WHEN ApplicationStatusChange_New_Status = 36 THEN 'VALREQ'
		WHEN ApplicationStatusChange_New_Status = 37 THEN 'VALRECD'
		WHEN ApplicationStatusChange_New_Status IN (47,48,117,125) THEN 'FINDECS'
		WHEN ApplicationStatusChange_New_Status = 127 THEN 'CUSTACCFINDECS'
		WHEN ApplicationStatusChange_New_Status IN(301,54) THEN 'ElECTINST'
		WHEN ApplicationStatusChange_New_Status IN(114,316) THEN 'BONDREG'
	END
FROM 
	YourTable
GROUP BY 
	CASE ApplicationStatusChange_New_Status 
		WHEN ApplicationStatusChange_New_Status = 2 THEN 'CAPSTART'
		WHEN ApplicationStatusChange_New_Status = 3 THEN 'CAPCOMP'
		WHEN ApplicationStatusChange_New_Status = 17 THEN 'VALCOMP'
		WHEN ApplicationStatusChange_New_Status IN(24,26,124) THEN 'AIP'
		WHEN ApplicationStatusChange_New_Status = 36 THEN 'VALREQ'
		WHEN ApplicationStatusChange_New_Status = 37 THEN 'VALRECD'
		WHEN ApplicationStatusChange_New_Status IN (47,48,117,125) THEN 'FINDECS'
		WHEN ApplicationStatusChange_New_Status = 127 THEN 'CUSTACCFINDECS'
		WHEN ApplicationStatusChange_New_Status IN(301,54) THEN 'ElECTINST'
		WHEN ApplicationStatusChange_New_Status IN(114,316) THEN 'BONDREG'
	END			

INSERT INTO #ApplicationStatus
SELECT 
	Application_ID, 
	MIN(Payout_EntryDate),
	'PAYOUT'
FROM 
	YourTable
GROUP BY 
	Application_ID

SELECT
WorkHours_CAPSTART_CAPCOMP = mis.dbo.mis_Workhours((b.CAPSTART, b.CAPCOMP)
,WorkHours_CAPCOMP_VALCOMP = mis.dbo.mis_Workhours(b.CAPCOMP, b.VALCOMP) 
,WorkHours_VALCOMP_AIP = mis.dbo.mis_Workhours(b.VALCOMP, b.AIP)
,WorkHours_AIP_VALREQ = mis.dbo.mis_Workhours(b.AIP, b.VALREQ)
,WorkHours_VALREQ_VALRECD = mis.dbo.mis_Workhours(b.VALREQ, b.VALRECD)
,WorkHours_VALRECD_FINDECS = mis.dbo.mis_Workhours(b.VALRECD, b.FINDECS) 
,WorkHours_FINDESC_CUSTACCFINDECS = mis.dbo.mis_Workhours(b.FINDESC, b.CUSTACCFINDECS)
,WorkHours_CUSTACCFINDECS_ElECTINST = mis.dbo.mis_Workhours(b.CUSTACCFINDECS, b.ElECTINST)
,WorkHours_ELECINST_BONDREG = mis.dbo.mis_Workhours(b.ElECTINST, b.BONDREG)
,WorkHours_BONDREG_PAYOUT = mis.dbo.mis_Workhours(b.BONDREG, b.PAYOUT)
FROM 
	YourTable a INNER JOIN 
	(
		SELECT Application_ID, [CAPSTART],[CAPCOMP],[VALCOMP],[AIP],[VALREQ],[VALRECD],[FINDECS], [CUSTACCFINDECS], [ElECTINST], [BONDREG], [PAYOUT] 
		FROM #ApplicationStatus
		PIVOT (MIN (ApplicationstatusChange_Date) 
			FOR ApplicationStatusChange 
			IN ([CAPSTART],[CAPCOMP],[VALCOMP],[AIP],[VALREQ],[VALRECD],[FINDECS], [CUSTACCFINDECS], [ElECTINST], [BONDREG], [PAYOUT])) AS [Status]
	) b ON a.Application_ID = b.Application_ID

Open in new window

0
 
DavesmAuthor Commented:
Hi  I cant get this to run

Please can you walk me throught this query i can get the #ApplicationStatus
part to work but when i try run the second part it does not work
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.