how to overcome from the tempdb spill over ?
-
Hi Folks,
Here with i have attached the execution plan, In that i have tempdb spill over in sort operation. i have created index to sort even it needs temp db spill over.
please suggest
http://www.sqlservercentral.com/Forums/Attachment17663.aspx
thanks
-
Can you post the entire query please, and the definition of the tvf "splitstring".
Cheers.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
ChrisM@Work (8/19/2015)
Can you post the entire query please, and the definition of the tvf "splitstring".Cheers.
SP :
DECLARE @lobid INT = 3,@LineOfBusiness VARCHAR(50)='ECN'
DECLARE @CmsContractID VARCHAR(MAX)='H9285'
SELECT DISTINCT D.DeterminantID AS BEN_CAT_ID
,DENSE_RANK() OVER (
PARTITION BY DA.DeterminantID ORDER BY DA.DeterminantGroupID
) - 1 AS BEN_CAT_EXT
,CASE
WHEN DAV.TypeID = 145 -- Include (Log type in determianant log)
THEN CASE
WHEN DA.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(DAV.VALUE1, '''', '')))
END
WHEN DAV.TypeID = 146 --Exclude (Log type in determianant log)
THEN CASE
WHEN DA.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(DAV.VALUE1, '''', '')))
END
END AS CPT_FROM
,CASE
WHEN DAV.TypeID = 145 -- Include (Log type in determianant log)
THEN CASE
WHEN DA.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(DAV.VALUE2, '''', '')))
END
WHEN DAV.TypeID = 146 --Exclude (Log type in determianant log)
THEN CASE
WHEN DA.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(DAV.VALUE2, '''', '')))
END
END AS CPT_TO
,NULL AS CPT_MOD
,CASE DAV.TypeID
WHEN 146 -- Exclude (Log type in determianant log)
THEN 1
ELSE 0
END AS EXCLUDE
,Users.UserName USER_ID
,DA.AddDate AS DATE_TIMESTAMP
,@LineOfBusiness AS PAYER_ID
,@CmsContractID AS CMS_CONTRACT_NO
,GETDATE() AS INSERT_DATETIME
,D.ModifiedDate UPDATE_DATETIME
,(
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
FROM HealthPlan25.dbo.Determinant D WITH (NOLOCK)
INNER JOIN HealthPlan25.dbo.BenefitPlanDeterminant BPD WITH (NOLOCK) ON D.DeterminantID_PK = BPD.DeterminantID
AND EXISTS (Select 'A' RS UNION ALL Select 'D' WHERE D.RowState = RS)
AND D.DeterminantTypeID = 2 -- Benefits
AND BPD.RowState <> 'R'
INNER JOIN HealthPlan25.dbo.BenefitPlan BPs WITH (NOLOCK) ON BPD.BenefitPlanID = BPs.BenefitPlanID
AND BPs.LineOfBusinessID = @lobid
AND BPs.RowState <> 'R'
INNER JOIN HealthPlan25.dbo.DeterminantAttribute DA WITH (NOLOCK) ON BPD.DeterminantID = DA.DeterminantID
AND DA.RowState <> 'R'
AND DA.DeterminantFieldID IN (
12--CPT Codes
,21--HCPCS Codes
,50--Revenue Codes
,76--Non Standard Codes
)
INNER JOIN HealthPlan25.dbo.DeterminantAttributeValue DAV WITH (NOLOCK) ON DA.DeterminantAttributeID = DAV.DeterminantAttributeID
INNER JOIN HealthPlan25.dbo.[User] Users WITH (NOLOCK) ON Users.UserID = ISNULL(DA.ModifiedBy, DA.AddBy)
LEFT JOIN HealthPlan25.dbo.DeterminantField DF WITH (NOLOCK) ON DA.DeterminantFieldID = DF.DeterminantFieldID
--LEFT JOIN HealthPlan25.dbo.DeterminantGroup DG WITH (NOLOCK) ON DA.DeterminantGroupID = DG.DeterminantGroupID
LEFT JOIN HealthPlan25.dbo.BenefitIndex BI WITH (NOLOCK) ON BI.BenefitPlanID = BPD.BenefitPlanID
AND BI.RowState <> 'R'
LEFT JOIN HealthPlan25.dbo.BenefitIndexItem BII WITH (NOLOCK) ON BII.BenefitIndexID = BI.BenefitIndexID
AND BII.RowState <> 'R'
LEFT JOIN HealthPlan25.dbo.BenefitIndexAction BIA WITH (NOLOCK) ON BIA.BenefitIndexItemID = BII.BenefitIndexItemID
AND BIA.Rowstate <> 'R'
OUTER APPLY (
SELECT items
FROM dbo.SplitString(ISNULL(DAV.Value1, ''), ',')
) TEMP
WHERE 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 TEMP.items IS NOT NULL
[/code]
Split string :
CREATE FUNCTION [dbo].[SplitString] (
@String VARCHAR(8000)
,@Delimiter CHAR(1)
)
RETURNS @temptable TABLE (items VARCHAR(8000))
AS
BEGIN
DECLARE @idx INT
DECLARE @slice VARCHAR(8000)
SELECT @idx = 1
IF len(@String) < 1
OR @String IS NULL
RETURN
WHILE @idx != 0
BEGIN
SET @idx = charindex(@Delimiter, @String)
IF @idx != 0
SET @slice = left(@String, @idx - 1)
ELSE
SET @slice = @String
IF (len(@slice) > 0)
INSERT INTO @temptable (Items)
VALUES (@slice)
SET @String = right(@String, len(@String) - @idx)
IF len(@String) = 0
BREAK
END
RETURN
END
-
Marked where I face spill over
-
Fantastic, thanks.
1. Spilling sorts occur when the number of rows received by the sort operator is more than the estimate. If you examine the property sheet of the sort operator, it's expecting 18,000 rows and getting 900,000.
2. The query is too complex for the optimiser to consider enough plans to guarantee that the one it spits out is good enough - instead it times out.
3. The query has numerous "code smells" including a predicate in the WHERE clause which converts a bunch of outer joins into inner joins.
First things first - does the query generate the correct results, every time? How long does it take to run?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
ChrisM@Work (8/19/2015)
Fantastic, thanks.1. Spilling sorts occur when the number of rows received by the sort operator is more than the estimate. If you examine the property sheet of the sort operator, it's expecting 18,000 rows and getting 900,000.
2. The query is too complex for the optimiser to consider enough plans to guarantee that the one it spits out is good enough - instead it times out.
3. The query has numerous "code smells" including a predicate in the WHERE clause which converts a bunch of outer joins into inner joins.
First things first - does the query generate the correct results, every time? How long does it take to run?
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.
-
squvi.87 (8/19/2015)
ChrisM@Work (8/19/2015)
Fantastic, thanks.1. Spilling sorts occur when the number of rows received by the sort operator is more than the estimate. If you examine the property sheet of the sort operator, it's expecting 18,000 rows and getting 900,000.
2. The query is too complex for the optimiser to consider enough plans to guarantee that the one it spits out is good enough - instead it times out.
3. The query has numerous "code smells" including a predicate in the WHERE clause which converts a bunch of outer joins into inner joins.
First things first - does the query generate the correct results, every time? How long does it take to run?
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.
Then we'll work on that part first.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
Here's the first part. Run it, report back with your findings. Are there loads of dupes in the output?
IF OBJECT_ID('tempdb..#DeterminantAttributeValue') IS NOT NULL DROP TABLE #DeterminantAttributeValue
SELECT
DAV.DeterminantAttributeID, DAV.TypeID,
Value1Part = 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), ''), '''', ''))),
Value1 = LTRIM(RTRIM(REPLACE(DAV.VALUE1, '''', ''))),
Value2Part = 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), ''), '''', ''))),
Value2 = LTRIM(RTRIM(REPLACE(DAV.VALUE2, '''', '')))
INTO #DeterminantAttributeValue
FROM HealthPlan25.dbo.DeterminantAttributeValue DAV
CROSS APPLY (
SELECT items FROM dbo.SplitString(ISNULL(DAV.Value1, ''), ',')
) TEMP
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
Have you considered changing your splitter function to a faster one?
Here's probably the fastest available: http://www.sqlservercentral.com/articles/Tally+Table/72993/
-
Luis Cazares (8/19/2015)
Have you considered changing your splitter function to a faster one?Here's probably the fastest available: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Thanks for your help Luis - there's plenty to go round in this query 😉
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 (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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
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.
Thanks Eric, that was exactly what I was thinking too 😉
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
In case this spills past UK time, this is what I have so far:
DECLARE @lobid INT = 3,@LineOfBusiness VARCHAR(50)='ECN'DECLARE @CmsContractID VARCHAR(MAX)='H9285'
SELECT DISTINCT D.DeterminantID AS BEN_CAT_ID
,DENSE_RANK() OVER (PARTITION BY DA.DeterminantID ORDER BY DA.DeterminantGroupID) - 1 AS BEN_CAT_EXT
,CASE
WHEN DAV.TypeID IN (145,146) -- Include (Log type in determianant log) / Exclude (Log type in determianant log)
THEN CASE WHEN DA.IncludeDataFormat IN ('T','S') THEN dav.Value1Part ELSE DAV.VALUE1 END
END AS CPT_FROM
,CASE
WHEN DAV.TypeID IN (145,146) -- Include (Log type in determianant log) / Exclude (Log type in determianant log)
THEN CASE WHEN DA.IncludeDataFormat IN ('T','S') THEN dav.Value2Part ELSE DAV.VALUE2 END
END AS CPT_TO
,NULL AS CPT_MOD
,CASE DAV.TypeID
WHEN 146 -- Exclude (Log type in determianant log)
THEN 1
ELSE 0
END AS EXCLUDE
,Users.UserName USER_ID
,DA.AddDate AS DATE_TIMESTAMP
,@LineOfBusiness AS PAYER_ID
,@CmsContractID AS CMS_CONTRACT_NO
,GETDATE() AS INSERT_DATETIME
,D.ModifiedDate UPDATE_DATETIME
,(
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
FROM HealthPlan25.dbo.Determinant D WITH (NOLOCK)
INNER JOIN HealthPlan25.dbo.BenefitPlanDeterminant BPD WITH (NOLOCK) ON BPD.DeterminantID = D.DeterminantID_PK
--AND EXISTS (Select 'A' RS UNION ALL Select 'D' WHERE D.RowState = RS) -- see WHERE clause
AND BPD.RowState <> 'R'
INNER JOIN HealthPlan25.dbo.DeterminantAttribute DA WITH (NOLOCK) ON DA.DeterminantID = BPD.DeterminantID
AND DA.RowState <> 'R'
AND DA.DeterminantFieldID IN (
12--CPT Codes
,21--HCPCS Codes
,50--Revenue Codes
,76--Non Standard Codes
)
INNER JOIN #DeterminantAttributeValue DAV WITH (NOLOCK) ON DAV.DeterminantAttributeID = DA.DeterminantAttributeID
INNER JOIN HealthPlan25.dbo.[User] Users WITH (NOLOCK) ON Users.UserID = ISNULL(DA.ModifiedBy, DA.AddBy)
LEFT JOIN HealthPlan25.dbo.DeterminantField DF WITH (NOLOCK) ON DF.DeterminantFieldID = DA.DeterminantFieldID
--LEFT JOIN HealthPlan25.dbo.DeterminantGroup DG WITH (NOLOCK) ON DA.DeterminantGroupID = DG.DeterminantGroupID
WHERE 1 = 1
AND D.RowState IN ('A', 'D')
AND D.DeterminantTypeID = 2 -- Benefits
AND EXISTS (
SELECT 1
FROM HealthPlan25.dbo.BenefitPlan BPs WITH (NOLOCK)
INNER JOIN HealthPlan25.dbo.BenefitIndex BI WITH (NOLOCK)
ON BI.BenefitPlanID = BP.BenefitPlanID
INNER JOIN HealthPlan25.dbo.BenefitIndexItem BII WITH (NOLOCK)
ON BII.BenefitIndexID = BI.BenefitIndexID -- #################### EXPLODE!!!
AND BII.RowState <> 'R'
INNER JOIN HealthPlan25.dbo.BenefitIndexAction BIA WITH (NOLOCK)
ON BIA.BenefitIndexItemID = BII.BenefitIndexItemID
AND BIA.Rowstate <> 'R'
WHERE BI.RowState <> 'R'
AND BP.BenefitPlanID = BPD.BenefitPlanID
AND BPs.LineOfBusinessID = @lobid
AND BPs.RowState <> 'R'
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)
)
)
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
I know I'm a bit late to the party here. I started typing a post about 7 hours ago and then work got in the way.
There are a few things here. The things I noticed were:
1. The inefficient string splitter. Replace it with Jeff's high-performance one like Luis suggested.
2. The predicate in the WHERE clause that effectively changes an OUTER JOIN on dbo.BenefitIndexAction to an INNER JOIN.
3. If this query is running in the HealthPlan25 database, get rid of the 3-part naming conventions and go with 2-part instead.
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.
5. All those NOLOCK hints are making me dizzy. Only use this if you really understand what it does and can mean for your data.
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.sqlservercentral.com/Forums/Topic1690995-3387-1.aspx
-
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.
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.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply