Performance tuning

GeoJul · April 19, 2016, 4:39am

yoneHello ever

I need a help in tuning my query below as it lot of time in fetching the records.

It outputs almost 1,45,000 records in 6 minutes. If the time can be brought down, it will be beneficial.

There is a function called inside this code, it is already tuned. Only the following query, needs to be looked into.

Thanks in advance.

-- Get the SamplesPartner Meta Table Ids
	DECLARE @specimenMetaTableSqlId SMALLINT
	DECLARE @specimenMetaTableIncId INT
 
SELECT @specimenMetaTableSqlId = MT.metaTableSqlId
		,@specimenMetaTableIncId = MT.metaTableIncId
FROM [dbo].[MetaTables] MT
WHERE MT.metaTableName = 'SamplesPartners' AND MT.isDeleted = 0x0
 

create table #temp1
(samplePartnerCode varchar(100),
 samplePartnerIncId INT,
 samplePartnerSqlId INT,
 clientSampleCode varchar(100),
 internalSampleCode varchar(100),
 SampleStatus varchar(100),
 studycode varchar(100),
 activityCode varchar(100),
 Destination varchar(max), 
 sampleTypeName varchar(max), 
 sampleDescription varchar(max),
 StudySponsor NVARCHAR(1000))
	
INSERT INTO #temp1	

SELECT S.samplepartnercode, S.samplePartnerIncId, S.samplePartnerSqlId,
S.clientSampleCode, S.internalSampleCode,PST.sampleStatusName,
ST.studyCode, A.activityCode,
SAFV.txtValue, SMT.sampleTypeName,S.sampleDescription, C.clientName
      from SamplesPartners as S
      INNER JOIN SamplesStatuses PST
      ON S.sampleStatusIncId = PST.sampleStatusIncId AND S.sampleStatusSqlId = PST.sampleStatusSqlId AND PST.isDeleted = 0 
      AND PST.sampleStatusCode IN ('CDISP', 'L')
      LEFT JOIN SampleTypes SMT 
      ON S.sampleTypeIncId = SMT.sampleTypeIncId AND S.sampleTypeSqlId = SMT.sampleTypeSqlId AND SMT.isDeleted = 0 AND S.isDeleted = 0
      
      LEFT JOIN ActivitiesSamplesPartners ASP 
		INNER JOIN Activities A ON A.activityIncId = ASP.activityIncId AND A.activitySqlId = ASP.activitySqlId AND A.isDeleted = 0
	  ON S.samplePartnerIncId = ASP.samplePartnerIncId AND S.samplePartnerSqlId = ASP.samplePartnerSqlId AND S.isDeleted = 0 AND ASP.isDeleted = 0
      LEFT JOIN Studies ST 
      ON A.studyIncId = ST.studyIncId AND A.studySqlId = ST.studySqlId AND ST.isDeleted = 0 
      
		LEFT JOIN samplespartnersadditionalfieldsvalues SAFV 
		  INNER JOIN samplespartnersadditionalfields SAF ON SAF.samplePartnerAdditionalFieldIncId = SAFV.samplePartnerAdditionalFieldIncId
		  AND SAF.samplePartnerAdditionalFieldSqlId = SAFV.samplePartnerAdditionalFieldSqlId
		  AND SAF.isDeleted=0x0 and SAFV.isDeleted=0x0
		  AND SAF.samplePartnerAdditionalFieldCode = 'AF_Dest'  --Destination
		ON SAFV.samplePartnerIncId = S.samplePartnerIncId AND SAFV.samplePartnerSqlId = S.samplePartnerSqlId
		
		LEFT JOIN StudiesClients SC
			INNER JOIN Clients C
			ON SC.clientIncId = C.clientIncId AND SC.clientSqlId = C.clientSqlId AND C.isDeleted = 0 
		ON SC.studyIncId =St.studyIncId AND SC.studySqlId = St.studySqlId AND SC.isDeleted =0 AND St.isDeleted = 0
		AND SC.sponsorRanking = 1
		
    WHERE S.internalSampleCode IS NOT NULL 
    
    
SELECT DISTINCT S.samplepartnercode,[dbo].eufn_e5_eSM_SE_GetCurrentItemLocation(ActTemp.samplePartnerSqlId, ActTemp.samplePartnerIncId, @specimenMetaTableSqlId, @specimenMetaTableIncId) AS LOCATION
INTO #temp2
FROM SamplesPartners S
INNER JOIN #temp1 ActTemp
ON ActTemp.samplePartnerIncId = S.samplePartnerIncId
AND ActTemp.samplePartnerSqlId = S.samplePartnerSqlId
AND S.isDeleted=0x0
    
    


--Final SELECT statement

select DISTINCT
T.samplePartnerCode AS EsmSpecimenCode, 
T.clientSampleCode AS CustomerSpecimenCode,
T.internalSampleCode AS EurofinsSpecimenCode,
T.SampleStatus AS SampleStatus,
T.studycode AS Study,
T.activityCode AS Activity,
T.Destination AS Destination,
T.sampleTypeName AS SampleType,
T.sampleDescription AS Matrix,
T.StudySponsor AS StudySponsor,
T2.LoCATION AS LoCATION
FROM #temp1 T
LEFT JOIN #Temp2 T2 ON T.samplepartnercode = T2.samplepartnercode
order by T.samplePartnerCode



--DROP TABLE #TEMP1
--DROP TABLE #TEMP2

gbritton · April 19, 2016, 6:46am

the function call in the second select will certainly slow things down. If it cannot be avoided, you might try this approach:

Select the distinct inputs to the function into a separate temp table
update that table, adding the results of the function call
in the second select, add a join to the new table to get the function results

the idea is to minimize calls to the function in the case that there are many duplicate locations. of course if that is not the case this won't help you much

GeoJul · April 19, 2016, 7:21am

Thanks gbritton, can you please post a sample code, that makes it more clear.

GeoJul · April 19, 2016, 2:31pm

I have rewritten my query using the logic gbritton had suggested using CTEs.

But the performance has not increased much. If any better way is available, please suggest.

The rewritten query is as below:

-- Get the SamplesPartner Meta Table Ids
	DECLARE @specimenMetaTableSqlId SMALLINT
	DECLARE @specimenMetaTableIncId INT
 
SELECT @specimenMetaTableSqlId = MT.metaTableSqlId
		,@specimenMetaTableIncId = MT.metaTableIncId
FROM [dbo].[MetaTables] MT
WHERE MT.metaTableName = 'SamplesPartners' AND MT.isDeleted = 0x0
 

create table #temp1
(samplePartnerCode varchar(100),
 samplePartnerIncId INT,
 samplePartnerSqlId INT,
 clientSampleCode varchar(100),
 internalSampleCode varchar(100),
 SampleStatus varchar(100),
 studycode varchar(100),
 activityCode varchar(100),
 Destination varchar(max), 
 sampleTypeName varchar(max), 
 sampleDescription varchar(max),
 StudySponsor NVARCHAR(1000))
	
INSERT INTO #temp1	

SELECT S.samplepartnercode, S.samplePartnerIncId, S.samplePartnerSqlId,
S.clientSampleCode, S.internalSampleCode,PST.sampleStatusName,
ST.studyCode, A.activityCode,
SAFV.txtValue, SMT.sampleTypeName,S.sampleDescription, C.clientName
      from SamplesPartners as S
      INNER JOIN SamplesStatuses PST
      ON S.sampleStatusIncId = PST.sampleStatusIncId AND S.sampleStatusSqlId = PST.sampleStatusSqlId AND PST.isDeleted = 0 
      AND PST.sampleStatusCode IN ('CDISP', 'L')
      LEFT JOIN SampleTypes SMT 
      ON S.sampleTypeIncId = SMT.sampleTypeIncId AND S.sampleTypeSqlId = SMT.sampleTypeSqlId AND SMT.isDeleted = 0 AND S.isDeleted = 0
      
      LEFT JOIN ActivitiesSamplesPartners ASP 
		INNER JOIN Activities A ON A.activityIncId = ASP.activityIncId AND A.activitySqlId = ASP.activitySqlId AND A.isDeleted = 0
	  ON S.samplePartnerIncId = ASP.samplePartnerIncId AND S.samplePartnerSqlId = ASP.samplePartnerSqlId AND S.isDeleted = 0 AND ASP.isDeleted = 0
      LEFT JOIN Studies ST 
      ON A.studyIncId = ST.studyIncId AND A.studySqlId = ST.studySqlId AND ST.isDeleted = 0 
      
		LEFT JOIN samplespartnersadditionalfieldsvalues SAFV 
		  INNER JOIN samplespartnersadditionalfields SAF ON SAF.samplePartnerAdditionalFieldIncId = SAFV.samplePartnerAdditionalFieldIncId
		  AND SAF.samplePartnerAdditionalFieldSqlId = SAFV.samplePartnerAdditionalFieldSqlId
		  AND SAF.isDeleted=0x0 and SAFV.isDeleted=0x0
		  AND SAF.samplePartnerAdditionalFieldCode = 'AF_Dest'  --Destination
		ON SAFV.samplePartnerIncId = S.samplePartnerIncId AND SAFV.samplePartnerSqlId = S.samplePartnerSqlId
		
		LEFT JOIN StudiesClients SC
			INNER JOIN Clients C
			ON SC.clientIncId = C.clientIncId AND SC.clientSqlId = C.clientSqlId AND C.isDeleted = 0 
		ON SC.studyIncId =St.studyIncId AND SC.studySqlId = St.studySqlId AND SC.isDeleted =0 AND St.isDeleted = 0
		AND SC.sponsorRanking = 1
		
    WHERE S.internalSampleCode IS NOT NULL 
    


SELECT S.samplepartnercode, ActTemp.samplePartnerSqlId, ActTemp.samplePartnerIncId
INTO #temp2
FROM SamplesPartners S
INNER JOIN #temp1 ActTemp
ON ActTemp.samplePartnerIncId = S.samplePartnerIncId
AND ActTemp.samplePartnerSqlId = S.samplePartnerSqlId
AND S.isDeleted=0x0


    
---- Remove duplicates
;with wcte as (
	Select samplepartnercode, samplePartnerSqlId, samplePartnerIncId,
	Row_Number() over (Partition By samplepartnercode, samplePartnerSqlId, samplePartnerIncId Order by samplepartnercode, samplePartnerSqlId, samplePartnerIncId) RNO
	From #temp2
)
Delete from wcte where RNO > 1



--Final SELECT statement

Select 
T.samplePartnerCode AS EsmSpecimenCode, 
T.clientSampleCode AS CustomerSpecimenCode,
T.internalSampleCode AS EurofinsSpecimenCode,
T.SampleStatus AS SampleStatus,
T.studycode AS Study,
T.activityCode AS Activity,
T.Destination AS Destination,
T.sampleTypeName AS SampleType,
T.sampleDescription AS Matrix,
T.StudySponsor AS StudySponsor,
T2.samplePartnerSqlId,
T2.samplePartnerIncId
INTO #temp3
FROM #temp1 T
LEFT JOIN #Temp2 T2 ON T.samplepartnercode = T2.samplepartnercode
order by T.samplePartnerCode

-- Remove Duplicates and apply function to the final result
;with wcte as (
	Select 
	EsmSpecimenCode, 
	CustomerSpecimenCode,
	EurofinsSpecimenCode,
	SampleStatus,
	Study,
	Activity,
	Destination,
	SampleType,
	Matrix,
	StudySponsor,
	samplePartnerSqlId,samplePartnerIncId,
	Row_Number() Over (Partition By EsmSpecimenCode, 
							CustomerSpecimenCode,
							EurofinsSpecimenCode,
							SampleStatus,
							Study,
							Activity,
							Destination,
							SampleType,
							Matrix,
							StudySponsor,samplePartnerSqlId,samplePartnerIncId
						Order by EsmSpecimenCode
						) RNO
	From #temp3
	)
	Select EsmSpecimenCode, 
			CustomerSpecimenCode,
			EurofinsSpecimenCode,
			SampleStatus,
			Study,
			Activity,
			Destination,
			SampleType,
			Matrix,
			StudySponsor,
			[dbo].eufn_e5_eSM_SE_GetCurrentItemLocation(samplePartnerSqlId, samplePartnerIncId, @specimenMetaTableSqlId, @specimenMetaTableIncId) AS LOCATION				
	from wcte where RNO = 1

--DROP TABLE #TEMP1
--DROP TABLE #TEMP2
--DROP TABLE #TEMP3

Kristen · April 19, 2016, 4:06pm

Have you got Covering Indexes on all the JOINs in the query?