how to overcome from the tempdb spill over ?

Question

how to overcome from the tempdb spill over ?

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

Luis Cazares (8/19/2015)
Ed Wagner (8/19/2015)
4. The subquery in the JOIN to dbo.BenefitPlanDeterminant. Needs DDL and data to test this. Given the subject, I know that can't happen.
If you refer to this part
AND EXISTS (Select 'A' RS UNION ALL Select 'D' WHERE D.RowState = RS)
It should be rewritten as this:
AND D.RowState IN('A', 'D')
This option is my how I assume the query was intended, but would generate an error if the column RS doesn't exist in the Determinant or BenefitPlanDeterminant tables.

That's what I was thinking - move it out of the subquery in the join. It's far less convoluted.

Luis Cazares (8/19/2015)
If the column actually exists in one of the tables, the condition will always return true because the WHERE clause will only affect the second row and the first row would always exist.

Now that's a valid point I didn't notice. I've been busted by that situation where a column I meant in a subquery existed in the outer query and wasn't working the way I expected. The lesson I learned that day was to qualify everything.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 2

After reading through all this excellent advice, the only thing I'd add is a question. Do you really need DISTINCT? That's adding quite a lot of overhead on top of all the rest. If you do need DISTINCT, it's usually an indication of a structural problem within your database, or possibly a logic problem within the code. Why are you getting duplications such that you need to run an aggregation function to eliminate them.

"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

squvi.87 Default port Points: 1450 More actions · Answer 3

Ed Wagner (8/19/2015)
Luis Cazares (8/19/2015)
Ed Wagner (8/19/2015)
4. The subquery in the JOIN to dbo.BenefitPlanDeterminant. Needs DDL and data to test this. Given the subject, I know that can't happen.
If you refer to this part
AND EXISTS (Select 'A' RS UNION ALL Select 'D' WHERE D.RowState = RS)
It should be rewritten as this:
AND D.RowState IN('A', 'D')
This option is my how I assume the query was intended, but would generate an error if the column RS doesn't exist in the Determinant or BenefitPlanDeterminant tables.
That's what I was thinking - move it out of the subquery in the join. It's far less convoluted.
Luis Cazares (8/19/2015)
If the column actually exists in one of the tables, the condition will always return true because the WHERE clause will only affect the second row and the first row would always exist.
Now that's a valid point I didn't notice. I've been busted by that situation where a column I meant in a subquery existed in the outer query and wasn't working the way I expected. The lesson I learned that day was to qualify everything.

Sorry I was trying to replace the in conditions into exists. mistakenly forgot to rollback that part. AND D.RowState IN('A', 'D') is correct

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Have you replaced the splitter?

Can you share the execution plan and performance results with the new splitter?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

squvi.87 Default port Points: 1450 More actions · Answer 5

replaced with the new spliter but it tooks more time

squvi.87 Default port Points: 1450 More actions · Answer 6

squvi.87

Default port

Points: 1450

August 20, 2015 at 7:53 am

#1821397

PFA execution plan with new spliter

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

squvi.87 (8/20/2015)
replaced with the new spliter but it tooks more time

Did you try the code I posted? Don't be shy about asking for explanations and further assistance.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Eric M Russell SSC Guru Points: 125562 More actions · Answer 8

Eric M Russell (8/19/2015)
squvi.87 (8/19/2015)
...
Yes it produces correct result only, it took 30 mins. If I remove the [SplitString] part from the query it took only 5-10 seconds.
OUTER APPLY (
SELECT items
FROM dbo.SplitString(ISNULL(DAV.Value1, ''), ',')
) TEMP
This is why multi-valued columns are a bad data modeling design pattern. You can try a more efficient splitter for an order of magnitude better performance, but this multi-valued column is costing you several orders of magnitude. Perhaps take a stab at splitting items into a #temp table keyed on DeterminantAttributeID.

Did you try the suggestion of first loading your split items into a #temp table, and then joining on that? It doubt it will ever run in 3 seconds, your pooch is screwed by that abnormal multi-valued column, but joining on a pre-split temp table may be orders of magnitude less than 30 minutes.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

squvi.87 (8/20/2015)
PFA execution plan with new spliter

Where?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

squvi.87 Default port Points: 1450 More actions · Answer 10

ChrisM@Work (8/20/2015)
squvi.87 (8/20/2015)
replaced with the new spliter but it tooks more time
Did you try the code I posted? Don't be shy about asking for explanations and further assistance.

Yes I have tried, but showing some error. like out of scope errors for some columns

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

squvi.87 (8/20/2015)
ChrisM@Work (8/20/2015)
squvi.87 (8/20/2015)
replaced with the new spliter but it tooks more time
Did you try the code I posted? Don't be shy about asking for explanations and further assistance.
Yes I have tried, but showing some error. like out of scope errors for some columns

If you post up the code which you attempted to run and the error message you got, then someone will assist you with getting it to work. By remaining silent, you may well be throwing away a perfectly good solution.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

squvi.87 Default port Points: 1450 More actions · Answer 12

ChrisM@Work (8/21/2015)
squvi.87 (8/20/2015)
ChrisM@Work (8/20/2015)
squvi.87 (8/20/2015)
replaced with the new spliter but it tooks more time
Did you try the code I posted? Don't be shy about asking for explanations and further assistance.
Yes I have tried, but showing some error. like out of scope errors for some columns
If you post up the code which you attempted to run and the error message you got, then someone will assist you with getting it to work. By remaining silent, you may well be throwing away a perfectly good solution.

Thanks every one, I have splited the query into 2 parts,

1. join

2. cross apply

now its running in 2 sec.

🙂

SELECT DA.DeterminantID,DA.DeterminantGroupID,D.ModifiedDate, DAV.TypeID ,DA.IncludeDataFormat , DA.ExcludeDataFormat ,DAV.VALUE1,DAV.VALUE2,DA.AddDate ,( CASE

WHEN DF.TblName = 'Codes'

THEN CASE

WHEN DF.DeterminantField = 'CPT Codes'

THEN 1

WHEN DF.DeterminantField = 'Revenue Codes'

THEN 0

WHEN DF.DeterminantField = 'HCPCS Codes'

THEN 2

WHEN DF.DeterminantField = 'Non Standard Codes'

THEN 3

ELSE ''

END

ELSE ''

END

) AS SERVICE_ITEM_TYPE

,0 AS CLAIM_FLAG,

D.DeterminantID_PK,DA.DeterminantAttributeID,DAV.DeterminantAttributeValueID,DF.DeterminantFieldID ,Users.UserName USER_ID

INTO #TT

FROM Healthplan25.dbo.Determinant D

LEFT JOIN Healthplan25.dbo.DeterminantAttribute DA ON D.DeterminantID_PK = DA.DeterminantID

AND D.DeterminantTypeID = 2

AND NOT EXISTS (SELECT 1 WHERE D.Rowstate ='R' )

AND NOT EXISTS (SELECT 1 WHERE DA.Rowstate ='R' )

AND D.DeterminantTypeID = 2

AND DA.DeterminantFieldID IN ( 12--CPT Codes

,21--HCPCS Codes

,50--Revenue Codes

,76--Non Standard Codes

)

JOIN Healthplan25.dbo.DeterminantAttributeValue DAV ON DAV.DeterminantAttributeID = DA.DeterminantAttributeID

JOIN Healthplan25.dbo.DeterminantField DF ON DF.DeterminantFieldID = DA.DeterminantFieldID AND NOT EXISTS (Select 1 where DF.Rowstate ='R' )

JOIN Healthplan25.dbo.[User] Users WITH (NOLOCK) ON Users.UserID = ISNULL(DA.ModifiedBy, DA.AddBy)

WHERE EXISTS (

SELECT BPD.BenefitPlanDeterminantID

FROM Healthplan25.dbo.BenefitPlan BP JOIN Healthplan25.dbo.BenefitPlanDeterminant BPD ON BP.BenefitPlanID = BPD.BenefitPlanID AND D.DeterminantID_Pk = BPD.DeterminantID AND BP.LineOfBusinessID = @lobid AND NOT EXISTS (SELECT 1 WHERE BP.RowState= 'R')

left JOIN Healthplan25.dbo.BenefitIndex BI ON BP.BenefitPlanID = BI.BenefitPlanID AND NOT EXISTS (SELECT 1 WHERE BI.RowState= 'R')

LEFT JOIN Healthplan25.dbo.BenefitIndexItem BII ON BII.BenefitIndexID = BI.BenefitIndexID AND NOT EXISTS (SELECT 1 WHERE BII.RowState= 'R')

LEFT JOIN Healthplan25.dbo.BenefitIndexAction BIA ON BIA.BenefitIndexItemID = BII.BenefitIndexItemID

AND BIA.ClaimFormTypeID IN ( 488 --HCFA (Claim Form Type in Benefit Index under Benefits Module)

,490 --Both (Claim Form Type in Benefit Index under Benefits Module)

)

AND NOT EXISTS (SELECT 1 WHERE BIA.RowState= 'R')

)

INSERT INTO DataExtract_HAXGDEV.dbo.BEN_CAT_CPT (

[BEN_CAT_ID]

,[BEN_CAT_EXT]

,[CPT_FROM]

,[CPT_TO]

,[CPT_MOD]

,[EXCLUDE]

,[USER_ID]

,[DATE_TIMESTAMP]

,[PAYER_ID]

,[CMS_CONTRACT_NO]

,[INSERT_DATETIME]

,[UPDATE_DATETIME]

,[SERVICE_ITEM_TYPE]

,[CLAIM_FLAG]

)

SELECT distinct DeterminantID AS BEN_CAT_ID

, DENSE_RANK() OVER ( PARTITION BY DeterminantID ORDER BY DeterminantGroupID ) - 1 AS BEN_CAT_EXT

,CASE

WHEN TypeID = 145 -- Include (Log type in determianant log)

THEN CASE

WHEN IncludeDataFormat IN( 'T','S')

THEN LTRIM(RTRIM(REPLACE(ISNULL(LEFT(ISNULL(TEMP.items, ''), CASE

WHEN CHARINDEX(':', ISNULL(TEMP.items, '')) <> 0

THEN CHARINDEX(':', ISNULL(TEMP.items, '')) - 1

ELSE LEN(ISNULL(TEMP.items, ''))

END), ''), '''', '')))

ELSE LTRIM(RTRIM(REPLACE(VALUE1, '''', '')))

END

WHEN TypeID = 146 --Exclude (Log type in determianant log)

THEN CASE

WHEN ExcludeDataFormat IN( 'T','S')

THEN LTRIM(RTRIM(REPLACE(ISNULL(LEFT(ISNULL(TEMP.items, ''), CASE

WHEN CHARINDEX(':', ISNULL(TEMP.items, '')) <> 0

THEN CHARINDEX(':', ISNULL(TEMP.items, '')) - 1

ELSE LEN(ISNULL(TEMP.items, ''))

END), ''), '''', '')))

ELSE LTRIM(RTRIM(REPLACE(VALUE1, '''', '')))

END

END AS CPT_FROM

,CASE

WHEN TypeID = 145 -- Include (Log type in determianant log)

THEN CASE

WHEN IncludeDataFormat IN( 'T','S')

THEN LTRIM(RTRIM(REPLACE(ISNULL(RIGHT(ISNULL(TEMP.items, ''), CASE

WHEN CHARINDEX(':', ISNULL(TEMP.items, '')) <> 0

THEN LEN(ISNULL(TEMP.items, '')) - CHARINDEX(':', ISNULL(TEMP.items, ''))

ELSE ''

END), ''), '''', '')))

ELSE LTRIM(RTRIM(REPLACE(VALUE2, '''', '')))

END

WHEN TypeID = 146 --Exclude (Log type in determianant log)

THEN CASE

WHEN ExcludeDataFormat IN( 'T','S')

THEN LTRIM(RTRIM(REPLACE(ISNULL(RIGHT(ISNULL(TEMP.items, ''), CASE

WHEN CHARINDEX(':', ISNULL(TEMP.items, '')) <> 0

THEN LEN(ISNULL(TEMP.items, '')) - CHARINDEX(':', ISNULL(TEMP.items, ''))

ELSE ''

END), ''), '''', '')))

ELSE LTRIM(RTRIM(REPLACE(VALUE2, '''', '')))

END

END AS CPT_TO

,NULL AS CPT_MOD

,CASE TypeID

WHEN 146 -- Exclude (Log type in determianant log)

THEN 1

ELSE 0

END AS EXCLUDE

,[USER_ID]

,AddDate AS DATE_TIMESTAMP

,@LineOfBusiness AS PAYER_ID

,@CmsContractID AS CMS_CONTRACT_NO

,GETDATE() AS INSERT_DATETIME

,ModifiedDate UPDATE_DATETIME

,SERVICE_ITEM_TYPE

,CLAIM_FLAG

from #tt

cross apply (select * from dbo.[DelimitedSplit8K](value1,',') )temp

where items is not null and items <> ''

Luis Cazares SSC Guru Points: 183706 More actions · Answer 13

Not a big deal, but you can change this:

cross apply (select * from dbo.[DelimitedSplit8K](value1,',') )temp

To this:

cross apply dbo.[DelimitedSplit8K](value1,',') temp

With the exact same results.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

squvi.87 Default port Points: 1450 More actions · Answer 14

Luis Cazares (8/21/2015)
Not a big deal, but you can change this:
cross apply (select * from dbo.[DelimitedSplit8K](value1,',') )temp
To this:
cross apply dbo.[DelimitedSplit8K](value1,',') temp
With the exact same results.

ok thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 15

Some generic reformatting:

SELECT

DA.DeterminantID, DA.DeterminantGroupID, D.ModifiedDate, DAV.TypeID, DA.IncludeDataFormat, DA.ExcludeDataFormat, DAV.VALUE1, DAV.VALUE2, DA.AddDate,

CASE WHEN DF.TblName = 'Codes'

THEN CASE

WHEN DF.DeterminantField = 'CPT Codes' THEN 1

WHEN DF.DeterminantField = 'Revenue Codes' THEN 0

WHEN DF.DeterminantField = 'HCPCS Codes' THEN 2

WHEN DF.DeterminantField = 'Non Standard Codes' THEN 3

ELSE '' END

AS SERVICE_ITEM_TYPE,

0 AS CLAIM_FLAG,

D.DeterminantID_PK, DA.DeterminantAttributeID, DAV.DeterminantAttributeValueID, DF.DeterminantFieldID, Users.UserName USER_ID

INTO #TT

FROM Healthplan25.dbo.Determinant D

LEFT JOIN Healthplan25.dbo.DeterminantAttribute DA

ON D.DeterminantID_PK = DA.DeterminantID

AND DA.Rowstate <> 'R'

AND DA.DeterminantFieldID IN ( 12--CPT Codes

,21--HCPCS Codes

,50--Revenue Codes

,76--Non Standard Codes

)

JOIN Healthplan25.dbo.DeterminantAttributeValue DAV

ON DAV.DeterminantAttributeID = DA.DeterminantAttributeID

JOIN Healthplan25.dbo.DeterminantField DF

ON DF.DeterminantFieldID = DA.DeterminantFieldID

AND DF.Rowstate <> 'R'

JOIN Healthplan25.dbo.[User] Users WITH (NOLOCK)

ON Users.UserID = ISNULL(DA.ModifiedBy, DA.AddBy)

WHERE D.DeterminantTypeID = 2

AND D.Rowstate <> 'R'

AND D.DeterminantTypeID = 2

AND EXISTS (

SELECT 1 --BPD.BenefitPlanDeterminantID

FROM Healthplan25.dbo.BenefitPlan BP

JOIN Healthplan25.dbo.BenefitPlanDeterminant BPD

ON BP.BenefitPlanID = BPD.BenefitPlanID

AND D.DeterminantID_Pk = BPD.DeterminantID -- outer reference

left JOIN Healthplan25.dbo.BenefitIndex BI

ON BP.BenefitPlanID = BI.BenefitPlanID

AND BI.RowState <> 'R'

LEFT JOIN Healthplan25.dbo.BenefitIndexItem BII

ON BII.BenefitIndexID = BI.BenefitIndexID

AND BII.RowState <> 'R'

LEFT JOIN Healthplan25.dbo.BenefitIndexAction BIA

ON BIA.BenefitIndexItemID = BII.BenefitIndexItemID

AND BIA.ClaimFormTypeID IN ( 488 --HCFA (Claim Form Type in Benefit Index under Benefits Module)

,490 --Both (Claim Form Type in Benefit Index under Benefits Module)

)

AND BIA.RowState <> 'R'

WHERE BP.LineOfBusinessID = @lobid

AND BP.RowState <> 'R'

) -- exists

INSERT INTO DataExtract_HAXGDEV.dbo.BEN_CAT_CPT (

[BEN_CAT_ID]

,[BEN_CAT_EXT]

,[CPT_FROM]

,[CPT_TO]

,[CPT_MOD]

,[EXCLUDE]

,[USER_ID]

,[DATE_TIMESTAMP]

,[PAYER_ID]

,[CMS_CONTRACT_NO]

,[INSERT_DATETIME]

,[UPDATE_DATETIME]

,[SERVICE_ITEM_TYPE]

,[CLAIM_FLAG]

)

SELECT distinct -- check why DISTINCT is required

DeterminantID AS BEN_CAT_ID

, DENSE_RANK() OVER ( PARTITION BY DeterminantID ORDER BY DeterminantGroupID ) - 1 AS BEN_CAT_EXT

,CASE

WHEN TypeID IN (145,146) -- Include (Log type in determianant log) / --Exclude (Log type in determianant log)

THEN CASE WHEN IncludeDataFormat IN ( 'T','S') THEN temp.VALUE1ex

ELSE temp.VALUE1 END

END AS CPT_FROM

,CASE

WHEN TypeID IN (145,146) -- Include (Log type in determianant log) / --Exclude (Log type in determianant log)

THEN CASE WHEN IncludeDataFormat IN ( 'T','S') THEN temp.VALUE2ex

ELSE temp.VALUE2 END

END AS CPT_TO

,NULL AS CPT_MOD

,CASE TypeID WHEN 146 -- Exclude (Log type in determianant log)

THEN 1 ELSE 0 END AS EXCLUDE

,[USER_ID]

,AddDate AS DATE_TIMESTAMP

,@LineOfBusiness AS PAYER_ID

,@CmsContractID AS CMS_CONTRACT_NO

,GETDATE() AS INSERT_DATETIME

,ModifiedDate UPDATE_DATETIME

,SERVICE_ITEM_TYPE

,CLAIM_FLAG

FROM #tt

CROSS APPLY ( -- Looks like d.Items contains elements which look like firstpart:secondpart

SELECT

VALUE1ex = LTRIM(RTRIM(REPLACE(ISNULL(LEFT(ISNULL(d.items, ''), CASE

WHEN CHARINDEX(':', ISNULL(d.items, '')) <> 0

THEN CHARINDEX(':', ISNULL(d.items, '')) - 1

ELSE LEN(ISNULL(d.items, ''))

END), ''), '''', ''))),

VALUE1 = LTRIM(RTRIM(REPLACE(d.VALUE1, '''', ''))),

VALUE2ex = LTRIM(RTRIM(REPLACE(ISNULL(RIGHT(ISNULL(d.items, ''), CASE

WHEN CHARINDEX(':', ISNULL(d.items, '')) <> 0

THEN LEN(ISNULL(d.items, '')) - CHARINDEX(':', ISNULL(d.items, ''))

ELSE ''

END), ''), '''', ''))),

VALUE2 = LTRIM(RTRIM(REPLACE(d.VALUE2, '''', '')))

FROM dbo.[DelimitedSplit8K] (value1,',') d

WHERE d.items IS NOT NULL

AND d.items <> ''

) temp

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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