January 29, 2012 at 5:45 pm
Hi guys,
I've been trying to tune a query for a while now but I cannot understand why SQL Server is choosing a bad plan for my query.
I'm faced with the unenviable job where I have to parse a column that has pipe delimited strings.
I've stripped down my query to the bare minimum to show the problem:
SELECT
CASE WHEN Link.ProcessType = 'D' THEN (SELECT ExecutionTime FROM MARSX.[ICIS].[ufnExtractDeletionTime](AI.LoadExceptionsFileName, '|')) END AS ExecutionTime
,SubString(AI.LoadExceptionsFileName, CHARINDEX('|', AI.LoadExceptionsFileName , 15) +1 ,80) + '|' PipeDelimitedText
FROM dbo.FactProcessPEQLink Link
LEFT OUTER JOIN MARSX.dbo.AuditInfo AI
ON Link.KeyAuditInfo = AI.KeyAuditInfo
LEFT OUTER JOIN MARSX.dbo.AuditInfo_DeletedRows AID
ON Link.KeyAuditInfo = AID.KeyAuditInfo
which produces the bad plan which I've attached in this post.
Notice that it performs an index scan on table AuditInfo_DeletedRows and table AuditInfo and brings back the entire table (about 1 million rows each) which is greatly slowing down my query.
If I leave out the table valued function in the above code:
SELECT
SubString(AI.LoadExceptionsFileName, CHARINDEX('|', AI.LoadExceptionsFileName , 15) +1 ,80) + '|' PipeDelimitedText
FROM dbo.FactProcessPEQLink Link
LEFT OUTER JOIN MARSX.dbo.AuditInfo AI
ON Link.KeyAuditInfo = AI.KeyAuditInfo
LEFT OUTER JOIN MARSX.dbo.AuditInfo_DeletedRows AID
ON Link.KeyAuditInfo = AID.KeyAuditInfo
This produces a much better plan (attached as well) and I get the results very quickly.
Here is the code that parses the pipe delimited string:
CREATE FUNCTION [ICIS].[ufnExtractDeletionTime]
(
@List VARCHAR(8000),
@Delimiter CHAR(1)
)
RETURNS @ret TABLE
(
ExecutionTime INT
)
AS BEGIN
DECLARE @LEN INT
SET @LEN=LEN(@List)+1
;With a AS
(
SELECT 1 AS nStart,
isNull(NULLIF(CHARINDEX(@Delimiter,@List,1),0),@LEN) AS nEnd,
RTRIM(LTRIM(SUBSTRING(@List,1,isNull(NULLIF(CHARINDEX(@Delimiter,@List,1),0),@LEN)-1))) AS VALUE
UNION All
SELECT nEnd+1,
isNull(NULLIF(CHARINDEX(@Delimiter,@List,nEnd+1),0),@LEN),
RTRIM(LTRIM(SUBSTRING(@List,nEnd+1,isNull(NULLIF(CHARINDEX(@Delimiter,@List,nEnd+1),0),@LEN)-nEnd-1)))
FROM a
WHERE nEnd<@LEN
)
INSERT INTO @ret
SELECT DATEDIFF(s, CAST(PivotTable.[1] AS DATETIME), CAST(PivotTable.[2] AS DATETIME))
FROM
(SELECT Row_Number() OVER (ORDER BY nStart) Row,
NULLIF(VALUE,'') Value
FROM a
WHERE LEN(VALUE)>0 AND ISDATE(VALUE) = 1
) InnerTable
PIVOT
(
Max(Value) FOR Row IN([1],[2])
) AS PivotTable
RETURN
END
GO
This is based off the CTE method for parsing delimited strings from:
http://forum.lessthandot.com/viewtopic.php?f=17&t=7566
The delimited string contains a Start Date and End Date and I am basically just trying to get the diff between them in Seconds.
I'm struggling to figure out why the query is scanning the entire tables in when I use this function. Is there a way to achieve what I am trying to do that avoids the costly index scans?
Ideally I would like to get rid of the delimited column entirely, but at this stage, that is not a possible solution for me.
Thanks very much.
January 29, 2012 at 6:11 pm
Just a suggestion - look at this article by Jeff Moden on an 8K splitter, its use just might help you.
January 30, 2012 at 2:22 am
Thanks for the suggestion, but I think that my problem doesn't actually have anything to do with how I've defined my function.
For example, I tried redefinig the ufn as:
ALTER FUNCTION [ICIS].[ufnExtractDeletionTime]
(
@List VARCHAR(8000),
@Delimiter CHAR(1)
)
RETURNS @ret TABLE
(
ExecutionTime INT
)
AS BEGIN
INSERT INTO @ret
SELECT 1
RETURN
END
and that produced the same inefficient query plan (attached).
However, if, instead of passing in AI.LoadExceptionsFileName I passed in a constant eg: '1' into the function then it would produce a much better plan (attached).
Can someone explain what might be happening here and why sql engine thinks it has to do an entire index scan of 1 million+ rows instead of only retrieving the rows based on the joins?
January 30, 2012 at 6:53 am
Split your string into a temporary table (NOT table variable) and then join to that for your output query. Problem solved.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 30, 2012 at 8:19 am
elogen (1/30/2012)
Thanks for the suggestion, but I think that my problem doesn't actually have anything to do with how I've defined my function.
It really does, but the reasons are interesting. The good plans you posted all use parallelism, and in particular, use bitmap operators to filter rows during the full index scans (yes the fast plans fully scan the index, just like the slow one). You can find all the details on that here:
http://sqlblog.com/blogs/paul_white/archive/2011/07/07/bitmap-magic.aspx
The bad plans are slow because any plan that writes to a table variable (reading is allowed) disables parallelism for the whole query. No parallelism, no bitmap filters (as explained in the link above). Your multi-statement function writes to a table variable, obviously. When you pass a constant in, the optimizer is smart enough to recognize that. It splits the single constant value once and stores the result in the table variable. This part has to run without parallelism, and does so, on the first input to the sequence operator in the plan. The second input to the sequence simply reads from the table variable, so it can use parallelism, we get bitmap filters, and all is good.
In your original query, the input to the function is not constant - it is a correlated value from some other table in the query. Since the result may change on every change of correlated parameter, SQL Server cannot perform the same trick, so the whole plan has to run on a single worker thread.
One solution is to retire the multi-statement function (they are rarely good news for performance anyway) and use Jeff's *in-line* table-valued function instead. The in-line function is incorporated in the body of the calling code at compilation time (just like a view definition is). This stands a good chance of giving you the parallel plan with bitmap filters you need to get good performance from this query.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 30, 2012 at 5:53 pm
Well what do you know, the inline string split has resulted in a parallel execution plan!
Here is my query:
SELECT
CASE WHEN Link.ProcessType = 'D' THEN (SELECT ExecutionTime FROM MARSX.dbo.[DelimitedSplit8K](AI.LoadExceptionsFileName, '|')) END AS ExecutionTime
,SubString(AI.LoadExceptionsFileName, CHARINDEX('|', AI.LoadExceptionsFileName , 15) +1 ,80) + '|' PipeDelimitedText
FROM dbo.FactProcessPEQLink Link
LEFT OUTER JOIN MARSX.dbo.AuditInfo AI
ON Link.KeyAuditInfo = AI.KeyAuditInfo
LEFT OUTER JOIN MARSX.dbo.AuditInfo_DeletedRows AID
ON Link.KeyAuditInfo = AID.KeyAuditInfo
and here is my definition of Jeffs inline function (slightly modified with an additional pivot to return me the value I need)
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover NVARCHAR(4000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT CASE WHEN ISDATE(PivotTable.[1]) = 1 AND ISDATE(PivotTable.[2]) = 1 THEN DATEDIFF(s, CAST(PivotTable.[1] AS DATETIME), CAST(PivotTable.[2] AS DATETIME)) END ExecutionTime
FROM
(
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
) InnerTable
PIVOT
(
Max(Item) FOR ItemNumber IN([1],[2])
) AS PivotTable
;
GO
Thank you Ron and Paul for helping me with this and especially Paul your explanation made it very clear what was happening.
Just a question to confirm my understanding:
If you look at the query plan attached, even though it is very quick, I still notice it does a full index scan on AuditInfo_DeletedRows tables (1 mill rows). Is this unaviodable? Does SQL Server simply decide that it's the best way to run the query?
As you pointed out Paul, even with the previous GoodPlans, it still does an index scan (although the scan produces significantly less rows - thousands instead of millions, partial index scan?). So I guess the full index scan is not something I have control over.
January 30, 2012 at 7:59 pm
elogen (1/30/2012)
Well what do you know, the inline string split has resulted in a parallel execution plan!
Cool.
If you look at the query plan attached, even though it is very quick, I still notice it does a full index scan on AuditInfo_DeletedRows tables (1 mill rows). Is this unaviodable? Does SQL Server simply decide that it's the best way to run the query?
As you pointed out Paul, even with the previous GoodPlans, it still does an index scan (although the scan produces significantly less rows - thousands instead of millions, partial index scan?). So I guess the full index scan is not something I have control over.
There has always been a full index scan, yes, though in the previous examples a bit map filter was applied by the storage engine as it scanned the index - resulting in many fewer rows being presented to the query processor. I'd need access to the real data (or perhaps a statistics-only copy of the database) to tune this query properly, but one thing you might like to try:
SELECT
CASE
WHEN Link.ProcessType = 'D'
THEN
(
SELECT ExecutionTime
FROM MARSX.dbo.[DelimitedSplit8K](AI.LoadExceptionsFileName, '|')
) END AS ExecutionTime,
SUBSTRING(AI.LoadExceptionsFileName, CHARINDEX('|', AI.LoadExceptionsFileName , 15) +1 ,80) + '|' AS PipeDelimitedText
FROM dbo.FactProcessPEQLink AS Link
LEFT OUTER HASH JOIN MARSX.dbo.AuditInfo AS AI ON
AI.KeyAuditInfo = Link.KeyAuditInfo
LEFT OUTER HASH JOIN MARSX.dbo.AuditInfo_DeletedRows AS AID ON
AID.KeyAuditInfo = Link.KeyAuditInfo;
The main change here is the addition of the HASH key word to the LEFT JOINs. This forces a hash join, but also *forces the order* the joins are performed in. This might get you something closer to the double-bitmap plan seen previously. Of course, it would be better still if the Audit Info table already contained the result of the ExecutionTime calculation...but there we are.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 31, 2012 at 5:58 pm
Thanks Paul, adding HASH to the left outer joins did indeed result in the double bitmap plan and also forces the order of the joins.
Cheers.
February 1, 2012 at 11:18 am
Altogether now: PAUL WHITE ROCKS!!!! :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 1, 2012 at 12:31 pm
PAUL WHITE ROCKS!!!!
seeeeeeeriously
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply