July 15, 2011 at 12:53 pm
I have a stored procedure that is getting a multi-valued parameter passed to it. I am then parsing out this parameter using an inline table function. Below is an example of the query with the inline function in the JOIN. When I use teh query below I get an Index seek in the execution plan, which is great.
SELECTpatient AS InjuredParty
FROMdbo.All_Data_Export ade JOIN dbo.DelimitedSplit(@piCompanyId,',')split ON
(ade.company_id = Split.item OR @piCompanyId = '-1')
LEFT JOIN incident inc ON inc.incident_id = ade.incident_id
WHEREinvoice_date >= @StartDate AND invoice_date < @EndDate
AND in_network = 1
and (credit_adjusted_price > 0)
But when I add a second split as you see below I go back to a Table Scan. How can I avoid the table scans? Thank you
SELECTpatient AS InjuredParty
FROMdbo.All_Data_Export ade JOIN dbo.DelimitedSplit(@piCompanyId,',')split ON
(ade.company_id = Split.item OR @piCompanyId = '-1')
JOIN dbo.DelimitedSplit8k(@piBranchId,',')splitB ON (ade.branch_id = Splitb.item OR @piBranchId = '-1')
LEFT JOIN incident inc ON inc.incident_id = ade.incident_id
WHEREinvoice_date >= @StartDate AND invoice_date < @EndDate
AND in_network = 1
and (credit_adjusted_price > 0
July 15, 2011 at 1:25 pm
Here is the function that is being used:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[DelimitedSplit]
--===== 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 VARCHAR(8000)
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 "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) 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 t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
FROM cteStart s
;
July 15, 2011 at 1:35 pm
Please post the table def for dbo.All_Data_Export including all index definitions as well as the actual execution plan (attached as sqlplan file). It seems like there is no covering index.
July 15, 2011 at 1:54 pm
Ok. I have uploaded the current execution plan and the index and table definition. I appeeciate any help. Like I said in the previous post if I only use the function once in the join it uses the Index IX17_ADE_MULTI.
July 15, 2011 at 2:09 pm
It seems like the statistics of All_Data_Export are out of date (estimated: 1.2mill rows vs. actual 180k).
Did you try to use the CROSS APPLY approach instead of the JOIN on the results of the DelimitedSplit function?
SELECT patient AS InjuredParty
FROM dbo.All_Data_Export ade
CROSS APPLY
(SELECT * FROM dbo.DelimitedSplit(@piCompanyId,',') WHERE ade.company_id = Split.item OR @piCompanyId = '-1' )split
CROSS APPLY
(SELECT * FROM dbo.DelimitedSplit8k(@piBranchId,',') WHERE ade.branch_id = Splitb.item OR @piBranchId = '-1')splitB
LEFT JOIN incident inc ON inc.incident_id = ade.incident_id
WHERE invoice_date >= @StartDate AND invoice_date < @EndDate
AND in_network = 1
and (credit_adjusted_price > 0
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply