April 19, 2016 at 6:36 am
Atif-ullah Sheikh: why would you want to pay the price of building two clustered indexes on (potentially hugely fat) temp tables only to hit them once each? That is almost never worth the effort to build the indexes.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 20, 2016 at 8:28 am
Not sure it will make a difference, but this is what I would do, I also added comments on a few things that looked questionable to me. try and following some simple formatting, this will make it more readable for others.
-- 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.isDeleted = 0x0
AND MT.metaTableName = 'SamplesPartners'
;
create table #temp1
(
samplePartnerCodevarchar(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
ON S.samplePartnerIncId = ASP.samplePartnerIncId
AND S.samplePartnerSqlId = ASP.samplePartnerSqlId
AND S.isDeleted = 0 -- wouldn't this make more sense in the where clause?
AND ASP.isDeleted = 0
INNER JOIN Activities A
ON A.activityIncId = ASP.activityIncId
AND A.activitySqlId = ASP.activitySqlId
AND A.isDeleted = 0
LEFT JOIN Studies ST
ON A.studyIncId = ST.studyIncId
AND A.studySqlId = ST.studySqlId
AND ST.isDeleted = 0
LEFT JOIN samplespartnersadditionalfieldsvalues SAFV
ON SAFV.samplePartnerIncId = S.samplePartnerIncId
AND SAFV.samplePartnerSqlId = S.samplePartnerSqlId
INNER JOIN samplespartnersadditionalfields SAF
ON SAF.samplePartnerAdditionalFieldIncId = SAFV.samplePartnerAdditionalFieldIncId
AND SAF.samplePartnerAdditionalFieldSqlId = SAFV.samplePartnerAdditionalFieldSqlId
AND SAF.isDeleted = 0x0
and SAFV.isDeleted = 0x0-- move to the join to SAFV?
AND SAF.samplePartnerAdditionalFieldCode = 'AF_Dest' --Destination
LEFT JOIN StudiesClients SC
ON SC.studyIncId = St.studyIncId
AND SC.studySqlId = St.studySqlId
AND SC.isDeleted = 0
--AND St.isDeleted = 0 -- this was checked above
INNER JOIN Clients C
ON SC.clientIncId = C.clientIncId
AND SC.clientSqlId = C.clientSqlId
AND C.isDeleted = 0
AND SC.sponsorRanking = 1-- move to join to SC?
WHERE S.internalSampleCode IS NOT NULL
;
-- You are only using 2 fields from #temp1 here, how many rows do you have at this point?
--would it be better to group the the data before the join? Changing to a sub query, removed DISTINCT.
SELECT S.samplepartnercode,
[dbo].eufn_e5_eSM_SE_GetCurrentItemLocation(ActTemp.samplePartnerSqlId, ActTemp.samplePartnerIncId, @specimenMetaTableSqlId, @specimenMetaTableIncId) AS LOCATION
INTO #temp2
FROM SamplesPartners S
INNER JOIN (SELECT samplePartnerIncId, samplePartnerSqlId
FROM #temp1
GROUP BY samplePartnerIncId, samplePartnerSqlId) ActTemp
ON ActTemp.samplePartnerIncId = S.samplePartnerIncId
AND ActTemp.samplePartnerSqlId = S.samplePartnerSqlId
WHERE 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
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
April 20, 2016 at 8:33 am
below86 (4/20/2016)
Not sure it will make a difference, but this is what I would do, I also added comments on a few things that looked questionable to me. try and following some simple formatting, this will make it more readable for others.
-- 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.isDeleted = 0x0
AND MT.metaTableName = 'SamplesPartners'
;
create table #temp1
(
samplePartnerCodevarchar(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
ON S.samplePartnerIncId = ASP.samplePartnerIncId
AND S.samplePartnerSqlId = ASP.samplePartnerSqlId
AND S.isDeleted = 0 -- wouldn't this make more sense in the where clause?
AND ASP.isDeleted = 0
INNER JOIN Activities A
ON A.activityIncId = ASP.activityIncId
AND A.activitySqlId = ASP.activitySqlId
AND A.isDeleted = 0
LEFT JOIN Studies ST
ON A.studyIncId = ST.studyIncId
AND A.studySqlId = ST.studySqlId
AND ST.isDeleted = 0
LEFT JOIN samplespartnersadditionalfieldsvalues SAFV
ON SAFV.samplePartnerIncId = S.samplePartnerIncId
AND SAFV.samplePartnerSqlId = S.samplePartnerSqlId
INNER JOIN samplespartnersadditionalfields SAF
ON SAF.samplePartnerAdditionalFieldIncId = SAFV.samplePartnerAdditionalFieldIncId
AND SAF.samplePartnerAdditionalFieldSqlId = SAFV.samplePartnerAdditionalFieldSqlId
AND SAF.isDeleted = 0x0
and SAFV.isDeleted = 0x0-- move to the join to SAFV?
AND SAF.samplePartnerAdditionalFieldCode = 'AF_Dest' --Destination
LEFT JOIN StudiesClients SC
ON SC.studyIncId = St.studyIncId
AND SC.studySqlId = St.studySqlId
AND SC.isDeleted = 0
--AND St.isDeleted = 0 -- this was checked above
INNER JOIN Clients C
ON SC.clientIncId = C.clientIncId
AND SC.clientSqlId = C.clientSqlId
AND C.isDeleted = 0
AND SC.sponsorRanking = 1-- move to join to SC?
WHERE S.internalSampleCode IS NOT NULL
;
-- You are only using 2 fields from #temp1 here, how many rows do you have at this point?
--would it be better to group the the data before the join? Changing to a sub query, removed DISTINCT.
SELECT S.samplepartnercode,
[dbo].eufn_e5_eSM_SE_GetCurrentItemLocation(ActTemp.samplePartnerSqlId, ActTemp.samplePartnerIncId, @specimenMetaTableSqlId, @specimenMetaTableIncId) AS LOCATION
INTO #temp2
FROM SamplesPartners S
INNER JOIN (SELECT samplePartnerIncId, samplePartnerSqlId
FROM #temp1
GROUP BY samplePartnerIncId, samplePartnerSqlId) ActTemp
ON ActTemp.samplePartnerIncId = S.samplePartnerIncId
AND ActTemp.samplePartnerSqlId = S.samplePartnerSqlId
WHERE 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
Your query is different to the original. This
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
isn't an accident. The position of the ON clauses preserves the left joins.
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
April 20, 2016 at 8:43 am
In my years, I've never seen anyone code like that, sorry, I assumed it was a mistake. Making that a sub query would make more sense to me.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
April 20, 2016 at 8:48 am
There's some discussion of it following this post.
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
April 20, 2016 at 9:03 am
ChrisM@Work (4/20/2016)
There's some discussion of it following this post.
Thanks for the example and I understand now. This would be something I would definitely put a comment on so no one else would make the same assumption I did and think it was a mistake and move the ON. I would still prefer the sub query approach, unless I saw a huge difference in performance.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply