April 18, 2016 at 11:47 am
Help needed 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
April 18, 2016 at 12:00 pm
The only things that immediately jump out are the two DISTINCT operations. That's usually a crutch for either bad data or bad data structures. To really improve performance, I'd address those issues first.
If you post the execution plans, preferably what's called the actual plan, we can probably drill down and make better suggestions for other areas of improvement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 18, 2016 at 12:13 pm
Also, you can't just assume that the function is not the problem. Unless it's a inline table-valued function, it quite possibly is the problem and re-writing it to be an inline table-valued function (if it's possible) could very well vastly improve your performance.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 18, 2016 at 12:22 pm
If the function you mention is a Scalar or Multi-StatementTVF UDF then those will void the use of parallelism, which clearly can make things slower with the volume of data you are processing.
However, lets take a step back here. You are putting millions of records into tempdb. That's going to take some time, especially if your IO subsystem is as slow as most of my client's is. 🙂 Do a file IO stall analysis while this is running and see if that isn't at least part of the problem too.
BUT!!! Fixing both the above could actually be worthless if you are truly spooling 1.5MILLION (POTENTIALLY HUGELY FAT) RECORDS BACK TO THE CLIENT!! Here's what you do - declare variables of the proper type for each column. Then run the same code but have that final select simply set the variables equal to each field in that final select. NOW if it takes 6 minutes you know you need to tune (or buy/provision better hardware). I am betting it will NOT take 6 minutes though, in which case network/client latency is the real culprit here.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 18, 2016 at 12:29 pm
Few quick thoughts, why use temporary tables? At the first glance they only seem to add IO and break up the flow.
The function eufn_e5_eSM_SE_GetCurrentItemLocation is a scalar function, what is the logic and can it be converted to an inline code or an inline table valued function?
The distincts suggest there is a problem with the data, how many rows does this code return with and without the distinct?
Why does the ActivitiesSamplesPartners join not have an ON clause or is it just bad code formatting?
😎
April 18, 2016 at 3:11 pm
Eirikur Eiriksson (4/18/2016)
Why does the ActivitiesSamplesPartners join not have an ON clause or is it just bad code formatting?😎
The tables are logically joined in the order of the ON clauses. You do not have to have an ON clause immediately following the corresponding JOIN clause. This query uses that extensively, so, while it may appear that the ON clause is missing, it has just been moved further down in the query.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 18, 2016 at 3:25 pm
drew.allen (4/18/2016)
Eirikur Eiriksson (4/18/2016)
Why does the ActivitiesSamplesPartners join not have an ON clause or is it just bad code formatting?😎
The tables are logically joined in the order of the ON clauses. You do not have to have an ON clause immediately following the corresponding JOIN clause. This query uses that extensively, so, while it may appear that the ON clause is missing, it has just been moved further down in the query.
Drew
Reading it from the phone I missed the ON
😎
Probably because the thing reads more like OFF
April 18, 2016 at 5:31 pm
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
....
INTO #temp2
FROM SamplesPartners S
INNER JOIN #temp1 ActTemp
???
Just to keep the system occupied?
_____________
Code for TallyGenerator
April 18, 2016 at 5:49 pm
Also...
order by T.samplePartnerCode
What's the purpose of this other than to make your query slower? If you truly need a presentation ORDER BY let the application do it.
-- Itzik Ben-Gan 2001
April 19, 2016 at 2:04 am
Many Thanks for your replies.
Can you please post an alternate code (not complete code) , so that it would be helpful to tune my query.
April 19, 2016 at 2:53 am
Can you post the execution plans please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2016 at 2:59 am
Execution can be very helpful. But without plan, I came up with the following code. You can try;
-- 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
----Atif: Removing Distinct and Function Call and adding ActTemp.samplePartnerSqlId, ActTemp.samplePartnerIncId as simple columns
----SELECT DISTINCT S.samplepartnercode,[dbo].eufn_e5_eSM_SE_GetCurrentItemLocation(ActTemp.samplePartnerSqlId, ActTemp.samplePartnerIncId, @specimenMetaTableSqlId, @specimenMetaTableIncId) AS LOCATION
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
---- Atif: Create Index on temp tables. If samplePartnerCode is Unique, Create UNIQUE Clustered Index. Other wise just create a Clustered Index.
CREATE CLUSTERED INDEX IDX_01 on #temp1(samplePartnerCode)
CREATE CLUSTERED INDEX IDX_02 on #temp2(samplePartnerCode)
---------------
--Final SELECT statement
---- Atif: Removing Distinct. Adding T2.samplePartnerSqlId,samplePartnerIncId
--select DISTINCT
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
--T2.LoCATION AS LOCATION
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(ActTemp.samplePartnerSqlId, ActTemp.samplePartnerIncId, @specimenMetaTableSqlId, @specimenMetaTableIncId) AS LOCATION
from wcte where RNO = 1
--DROP TABLE #TEMP1
--DROP TABLE #TEMP2
You will have to add indexes for your main query for Temp1 depending upon the execution plan. So many assumptions in the above code so please accept my apologies if it gets worst :-D.
April 19, 2016 at 4:35 am
I am attaching the actual execution plan available.
The full execution plan is not available as an error message is encountered.
Msg 1105, Level 17, State 2, Line 61
Could not allocate space for object 'dbo.Large Object Storage System object: 422236177039360' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Please see the execution plan attached.
April 19, 2016 at 4:56 am
VSSGeorge (4/19/2016)
I am attaching the actual execution plan available.The full execution plan is not available as an error message is encountered.
Msg 1105, Level 17, State 2, Line 61
Could not allocate space for object 'dbo.Large Object Storage System object: 422236177039360' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Please see the execution plan attached.
Nothing seems to be attached.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 19, 2016 at 6:30 am
VSSGeorge (4/19/2016)
I am attaching the actual execution plan available.The full execution plan is not available as an error message is encountered.
Msg 1105, Level 17, State 2, Line 61
Could not allocate space for object 'dbo.Large Object Storage System object: 422236177039360' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Please see the execution plan attached.
Can you post the structure of the temp table this query is inserting into please?
First observations:
- few of your existing indexes support this query.
- there are a heck of a lot of implicit conversions.
- hash matches everywhere for relatively small rowcounts.
At least some of those implicit conversions are to match output columns with the datatypes of your #temp table. Try selecting into #temp instead. Check the execution plan - what's left will be implicit conversions to support the query. Investigate those first.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply