asked on

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

ASKER CERTIFIED SOLUTION

JestersGrind

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Davesm

ASKER

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

JestersGrind

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

Davesm

ASKER

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