Hi , I Have The Below Sp And It Takes 28 Sec To Execute .Can Anyone Let Me Know Further Optimization That Can Help Reduce Execution Time

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

SQL

-- =============================================                   
-- Description: <admin summary="" report="">                    
-- =============================================                    
-- exec  [SPRpt_AdminReportSummary] '2010-02-03','2019-07-07' with RECOMPILE,0,0,'1',0,0,0,0,0,0,0,0,0 with RECOMPILE
-- exec [SPRpt_AdminReportRaw] '2015-02-03','2015-12-20'with RECOMPILE ,1080,0,'2',0,0,1,0,0,0,0,0,0 with RECOMPILE

ALTER PROCEDURE [dbo].[SPRpt_AdminReportSummary]
	@StartDate DATETIME,
	@EndDate DATETIME,
	@ModelID INT = 0,
	@TestID INT = 0,
	@Category varchar(100) = '',
	@SupplierID INT = 0,
	@FactoryID INT = 0,
	@isCosmeticManufacturer BIT = 0,
	@isFunctionalManufacturer BIT = 0,
	@RegionID INT = 0,
	@StateID INT = 0, 
	@ServiceCentreID INT = 0,
	@isCosmeticServiceCenter BIT = 0,
	@isFunctionalServiceCenter BIT = 0,
	@callFromChart bit=0
AS
BEGIN
	
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED   
	   
--change by mohan as charts need to all category data
	if(@callFromChart=1)
	     set  @Category='' ;  
		      
	
	DECLARE @cosmeticTestAttributeID int;
	DECLARE @functionalTestAttributeID int;
	SET @cosmeticTestAttributeID = (select AttributeID from LookupMaster where AttributeType = 'IsCosmeticOrFunctional' and code ='cosmetic');
	SET @functionalTestAttributeID = (select AttributeID from LookupMaster where AttributeType = 'IsCosmeticOrFunctional' and code ='functional');
	
	
	/*Set usertype as service centre type user because service centre report only for tests by service-centre user
	DECLARE @userType int;
	SET @userType = (select AttributeID from LookupMaster where AttributeType = 'usertypeid' and code ='service')*/

	
	
	create table #ReportTable
	([TestResultCycleID] int,[Model Number] nvarchar(50),[Commercial Name] nvarchar(50),[Source]varchar(14),
	[Supplier]nvarchar(50),[Factory]nvarchar(100),[Region]nvarchar(50),[State]nvarchar(50),
	[Service Centre]nvarchar(50),[IMEI 1]nvarchar(20),[IMEI 2]nvarchar(20),[Date]nvarchar(4000),
	[Time]nvarchar(10),[Age]int,[Cycle Result]nvarchar(50),	[Repeat]nvarchar(3),
	[TestName]nvarchar(75),[TestResult]int,[TestID]int,[UserTypeID]int,
	[SupplierID]int,[ServiceCenterID]int,[RegionID]int,[StateID]int,[LineID]int,
	[FactoryID]int,[ModelId]int,[TestingCycleTypeID]int,[ProductionQuantity]int,
	[DefectQuantity]int,[TestFilterType]nvarchar(23),[CosmeticFunctionalOrder]int,[OriginalTestName]nvarchar(50),
	[CountOrder]int,[Production/Service Date Range]nvarchar(4000),[% Defect Rate]decimal)

	CREATE CLUSTERED INDEX IDX_ReportTable_TRCI ON #ReportTable ([TestResultCycleID])

	CREATE NONCLUSTERED INDEX [NCI1]
	ON #ReportTable ([Model Number])
	INCLUDE ([Source],[Supplier],[Region],[State],[Service Centre],[ModelId]); 
	
	IF(@callFromChart=1)
	BEGIN 
			Insert into #ReportTable 
			SELECT *, (FORMAT(@StartDate,'dd-MMM-yyyy') + ' to ' + FORMAT(@EndDate,'dd-MMM-yyyy')) [Production/Service Date Range], 
			(CASE WHEN ProductionQuantity>0 THEN CAST((DefectQuantity*1.0/ProductionQuantity * 100) AS decimal(18,2)) ELSE 0 END) [% Defect Rate]
			FROM fnAdminReportSummary(@StartDate, @EndDate) mr
			WHERE (TRY_CAST(mr.Date as date) between TRY_CAST(@StartDate as date) and TRY_CAST(@EndDate as date))
	END
	ELSE
	BEGIN
		Insert into #ReportTable
		SELECT *, (FORMAT(@StartDate,'dd-MMM-yyyy') + ' to ' + FORMAT(@EndDate,'dd-MMM-yyyy')) [Production/Service Date Range], 
		(CASE WHEN ProductionQuantity>0 THEN CAST((DefectQuantity*1.0/ProductionQuantity * 100) AS decimal(18,2)) ELSE 0 END) [% Defect Rate]
		 FROM fnAdminReportSummary(@StartDate, @EndDate) mr
		WHERE ( 
		isnull(mr.RegionID, 0) = (CASE WHEN @RegionID > 0 THEN @RegionID ELSE isnull(mr.RegionID, 0) END)
		AND isnull(mr.FactoryID, 0) = (CASE WHEN @FactoryID > 0 THEN @FactoryID ELSE isnull(mr.FactoryID, 0) END)
		AND isnull(mr.SupplierID, 0) = (CASE WHEN @SupplierID > 0 THEN @SupplierID ELSE isnull(mr.SupplierID, 0) END)
		AND isnull(mr.RegionID, 0) = (CASE WHEN @RegionID > 0 THEN @RegionID ELSE isnull(mr.RegionID, 0) END)
		AND isnull(mr.StateID, 0) = (CASE WHEN @StateID > 0 THEN @StateID ELSE isnull(mr.StateID, 0) END)
		AND isnull(mr.ServiceCenterID, 0) = (CASE WHEN @ServiceCentreID > 0 THEN @ServiceCentreID ELSE isnull(mr.ServiceCenterID, 0) END)
		AND isnull(mr.ModelID, 0) = (CASE WHEN @ModelID > 0 THEN @ModelID ELSE isnull(mr.ModelID, 0) END)
		AND isnull(mr.TestFilterType, '0') in (
			(CASE WHEN (@isCosmeticManufacturer > 0) THEN 'ManufacturerCosmetic' END),
			(CASE WHEN (@isFunctionalManufacturer > 0) THEN 'ManufacturerFunctional' END),
			(CASE WHEN (@isCosmeticServiceCenter > 0) THEN 'ServiceCentreCosmetic' END),
			(CASE WHEN (@isFunctionalServiceCenter > 0) THEN 'ServiceCentreFunctional' END),
			(CASE WHEN @isCosmeticManufacturer = 0 AND @isFunctionalManufacturer = 0 AND 
					  @isCosmeticServiceCenter = 0 AND @isFunctionalServiceCenter = 0 THEN isnull(mr.TestFilterType, '0') END))
		AND TRY_CAST(mr.Date as date) between TRY_CAST(@StartDate as date) and TRY_CAST(@EndDate as date)
		AND isnull(mr.TestID, 0) = (CASE WHEN @TestID > 0 THEN @TestID ELSE isnull(mr.TestID, 0) END)
		AND (CASE WHEN @Category <> '' THEN mr.TestingCycleTypeID  ELSE 0 END) in (select [data] from dbo.split(@Category, ',')))
	END 
	
 
	/*Generate Column names */
	DECLARE @columns     NVARCHAR(MAX);

	SET @columns = N''; 
	SELECT @columns += ', ' + QUOTENAME(TestName)
		FROM   (
				select distinct 
					/*(CASE WHEN [IsFunctionalOrCosmetic]= @functionalTestAttributeID THEN 'Functional#' 
							WHEN [IsFunctionalOrCosmetic] = @cosmeticTestAttributeID 
							THEN 'Cosmetic#' else '' END) +*/ TestName /* as 'TestName', [IsFunctionalOrCosmetic]*/
			 , CosmeticFunctionalOrder, [countorder], [OriginalTestName] from #ReportTable WITH (NOLOCK)
			   ) As temp
		ORDER BY 
			   CosmeticFunctionalOrder, [OriginalTestName], [countorder]; 
	
	print @columns;

	
	SET @columns = STUFF(@columns, 1, 2, '')
	print @columns;
	/*********************************************/

	/*Generate pivoted result table*/
	DECLARE @sql NVARCHAR(MAX);
	SET @sql = '      
		SELECT ' + @columns +' , [Model Number], ModelId, Source, Supplier, Region, State, [Service Centre] INTO #tableTests
		FROM                   
		(                  
			SELECT [Model Number], ModelId, Source, Supplier, Region, State, [Service Centre], 
			 /*(CASE WHEN [IsFunctionalOrCosmetic]= 14 THEN ''Functional#'' 
			when [IsFunctionalOrCosmetic] = 13 THEN ''Cosmetic#'' else '''' END) + Test AS*/ TestName, TestResult
			FROM #ReportTable WITH (NOLOCK) 
		) AS j   PIVOT (sum(TestResult) FOR TestName IN (' + @columns + ')) AS p             

			CREATE CLUSTERED INDEX IDX_tableTests_TRCI ON #tableTests ([Model Number])

			CREATE NONCLUSTERED INDEX [NCI2]
			ON #tableTests (Source)
			INCLUDE ([Supplier],[Region],[State],[Service Centre],[ModelId]); 
		
		--select * from #ReportTable order by testresultcycleid, [model number], [commercial name];

		SELECT distinct RT.[Model Number] [Model], 
		(CASE WHEN RT.[Source] = ''Manufacturer'' THEN RT.Supplier WHEN RT.[Source] = ''Service Centre'' THEN RT.[Service Centre] END) [ODM],
		RT.[Source] [Category], RT.[Region], RT.[State], RT.[Service Centre], RT.[Production/Service Date Range],
		isnull(RT.[ProductionQuantity], 0) [Production Qty/Service Call], RT.[Age], isnull(RT.[DefectQuantity],0) [Total Defect Quantity], [% Defect Rate], '+@columns+'
		FROM #tableTests TT WITH (NOLOCK) 
		INNER JOIN #ReportTable RT WITH (NOLOCK) on 
		isnull(TT.[Model Number],'''')=isnull(RT.[Model Number],'''') and isnull(TT.Source,'''')=isnull(RT.Source,'''') and isnull(TT.Supplier,'''')=isnull(RT.Supplier,'''') 
		and isnull(TT.Region,'''') = isnull(RT.Region,'''') and isnull(TT.State,'''')=isnull(RT.State,'''') 
		and isnull(TT.[Service Centre],'''') = isnull(RT.[Service Centre],'''') and RT.ModelId = TT.ModelId
	
		/*SELECT distinct RT.[Model Number], RT.[Commercial Name], RT.[Source], RT.[Region], RT.[State], RT.[Service Centre], 
		RT.[Supplier], RT.[Factory],  RT.[IMEI 1], RT.[IMEI 2], RT.[Date], RT.[Time], RT.[Age], RT.[Repeat], RT.[Cycle Result], RT.[ProductionQuantity], RT.[DefectQuantity], '+@columns+'
		INTO tblSummary
		FROM #tableTests TT WITH (NOLOCK)
		INNER JOIN #ReportTable RT WITH (NOLOCK) on TT.TestResultCycleID = RT.TestResultCycleID*/
		
		DROP TABLE #tableTests;
		
		/*select * from tblSummary 
		drop table tblSummary
		group by [Model Number]*/ '
		
	print @sql;
	exec sp_executesql @sql
	
	
	DROP TABLE #ReportTable;
	/*********************************************/
   
END</admin>

Posted 13-Aug-15 19:58pm

Member 11908491

Updated 13-Aug-15 20:23pm

Patrice T

v2

Add a Solution

Comments

Mehdi Gholam 14-Aug-15 2:40am

Don't create temp tables, create a table and insert rows once and query from that.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2015-08-13T20:57:00

Advice, more than a definitive response.

Identifying where and why your code is slow is not an easy task, you need to narrow the search.
To narrow the search, you have to get to know where time is consumed.

Optimization principle:
The standard tool to use is a profiler, the profiler tells you which part of this code takes time to execute. you will have to concentrate your efforts on this part.

If you don't have a profiler, you have to time your code manually.
Since this code is essentially sequential, you have to find a way to insert checkpoints in this code.
For each checkpoint, you need to identify the part of code and the time.
By analysing the checkpoints report, you will see which part is eating time and needs work.

When you know which part is guilty, Improve question by adding a comment saying what you found.