June 23, 2016 at 4:49 am
Hi,
I have been reading links relating to the fact that instead of using table variables, then I should be able to switch to using memory optimized tables.
To do this, I am meant to define a type, which seems straightforward enough, but I am trying to convert a user defined function, and I can't seem to figure out the exact syntax that would allow me to convert it into using a memory optimized table, rather than a table variable.
Can someone suggest how I would convert the below, so that the @temptable table variable can be converted to using a memory optimized table? The code itself just reads formulas with +'s and -'s in them, and converts them to a table that has the value, and whether it is positive or negative, if that matters.
SQL Server is actually SQL Server 2016, but I don't see a forum appropriate for that...
/****** Object: UserDefinedFunction [dbo].[rfn_Split_VirtCons] Script Date: 6/20/2016 2:11:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[rfn_Split_VirtCons_mod]
(
@String NVARCHAR(MAX)
)
RETURNS @temptable TABLE (items NVARCHAR(MAX), signs NVARCHAR(1), itemcount SMALLINT)
AS
BEGIN
DECLARE @delimiterneg nvarchar(1)
DECLARE @delimiterpos nvarchar(1)
SET @string = REPLACE(REPLACE(REPLACE(REPLACE(@string,'(',''),')',''),'[',''),']','')
SET @delimiterneg = '-'
SET @delimiterpos = '+'
DECLARE @idx INT
DECLARE @idxpos INT
DECLARE @idxneg INT
DECLARE @slice NVARCHAR(MAX)
DECLARE @sign NVARCHAR(1)
DECLARE @iterations int
DECLARE @itemcount SMALLINT
SET @idx = 1
SET @iterations = 0
IF DATALENGTH(@String)/2<1 OR @String IS NULL
RETURN
WHILE @idx!= 0
BEGIN
SET @iterations = @iterations+1
SET @idxpos = CHARINDEX(@delimiterpos, @string)
SET @idxneg = CHARINDEX(@delimiterneg, @string)
-- first iteration
IF(@iterations = 1)
SET @sign = '+'
-- negative only if sign is negative
IF(LEFT(@string,1)='-' AND @iterations = 1 )
SET @sign = '-'
SELECT @idx = MIN(idx)
FROM
(
SELECT CASE WHEN @idxpos = 0 THEN NULL ELSE @idxpos END AS [idx]
UNION
SELECT CASE WHEN @idxneg = 0 THEN NULL ELSE @idxneg END AS [idx]
) AS tmp
IF @idx!=0
SET @slice = LEFT(@String, @idx -1)
ELSE SET @slice = @string
IF (DATALENGTH(@slice)/2>0)
INSERT INTO @temptable(items, signs)
VALUES (@slice, @sign)
SET @sign = LEFT (RIGHT(@String, DATALENGTH(@String)/2 - @idx +1 ),1)
SET @String = RIGHT(@String, DATALENGTH(@String)/2 - @idx)
IF DATALENGTH(@String)/2 = 0
BREAK
IF @idxneg=0 AND @idxpos = 0
BREAK
END
SELECT @itemcount = COUNT(*)
FROM @temptable
UPDATE @temptable
SET itemcount = @itemcount
RETURN
END
June 23, 2016 at 5:02 am
What might be typical inputs and outputs of this code?
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
June 23, 2016 at 5:07 am
Something like "[ab]+[cd]-[ef]" would create a mini-table that would have a row for "ab", "cd", "ef", and tell you that "ab" and "cd" were positive, and "ef" is negative.
I've been reading about it a bit more, and I think my problem is that I am trying to pass a memory optimized table as the return value for the function, I think the only thing I can return is a basic table, not a user defined type.
June 23, 2016 at 5:15 am
kyagi.jo (6/23/2016)
Something like "[ab]+[cd]-[ef]" would create a mini-table that would have a row for "ab", "cd", "ef", and tell you that "ab" and "cd" were positive, and "ef" is negative.I've been reading about it a bit more, and I think my problem is that I am trying to pass a memory optimized table as the return value for the function, I think the only thing I can return is a basic table, not a user defined type.
I would suggest, rather than worrying about memory optimized tables, you convert this into an inline table-valued function - it will run very much faster than the current version. Are you up for that? If so, we'll need a little more info about the inputs and outputs: for instance, are the elements [ab], [cd] always pairs? Or can they be [abc], [abcd]?
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
June 23, 2016 at 5:27 am
It's more of a test of how memory optimized tables work more than anything else; it's a simple enough function the existing way of doing it hasn't had any noticeable speed issues given the data I am providing it, but I am planning in future to be using memory optimized tables when I would be using table variables, so just wanted to figure out how to do it... turns out I chose a bad example, as the return type for a function seems to be a case that is not covered by memory optimized tables.
I would be interested in turning it into an in-line table valued function, however, but I don't see how I could do that. The input is basically any string you want, of how many values you want, with +/- operators separating them.
e.g
"abc+def"
"abc-d+e-fgh"
Are all valid inputs, and they will all produce grids that list the parsed out values, and the sign associated with them.
June 23, 2016 at 5:31 am
Oh I see - I got completely the wrong end of the stick, I assumed you were looking for a means of speeding up the function. To that end, here's a code snippet for testing, adapted from the "house" function DelimitedSplit8K_LEAD, documented here[/url]:
DECLARE @pString VARCHAR(8000) = '[ab]+[cd]-[ef]';
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) IN ('+','-') 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),
Items = REPLACE(REPLACE(
SUBSTRING(@pString,s.N1,ISNULL(NULLIF((LEAD(s.N1,1,1) OVER (ORDER BY s.N1) - 1),0)-s.N1,8000))
,'[',''),']',''),
Signs = CASE WHEN s.N1-1 = 0 THEN '+' ELSE SUBSTRING(@pString,s.N1-1,1) END
FROM cteStart s
;
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
June 23, 2016 at 6:11 am
Nice work, Chris. I like the way you handled the first element.
June 23, 2016 at 6:24 am
Thanks! This doesn't seem to work quite right when the first value is negative as I get a positive empty entry, but it gets me nearly there, so I'll see if I can tweak it.
June 23, 2016 at 6:27 am
Ed Wagner (6/23/2016)
Nice work, Chris. I like the way you handled the first element.
Haha! The OP doesn't 😀
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
June 23, 2016 at 6:29 am
kyagi.jo (6/23/2016)
Thanks! This doesn't seem to work quite right when the first value is negative as I get a positive empty entry, but it gets me nearly there, so I'll see if I can tweak it.
Yes, do, it will help you to understand the code.
So your input string can look like this: '-[ab]+[cd]-[ef]'?
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
June 23, 2016 at 6:36 am
Yep, that's correct.
June 23, 2016 at 6:41 am
Can it be any of these?
'-[ab]+[cd]-[ef]'
'+[ab]+[cd]-[ef]'
'[ab]+[cd]-[ef]' (first element defaults to '+')
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
June 23, 2016 at 6:43 am
Yes, all of those would work.
June 23, 2016 at 7:00 am
Still need to do some more reading to fully understand the nuances of the code, but by looking at the numbers the indexes were producing, adding the following to the where clause addressed the issue:
CASE LEFT(@pString,1)
WHEN '-'
THEN s.n1-1
WHEN '+'
THEN s.n1-1
ELSE 1 END <> 0
June 23, 2016 at 7:06 am
Or this:
DECLARE @pString VARCHAR(8000) = '-[ab]+[cd]-[ef]';
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, pString) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1, pString
FROM cteTally t
CROSS APPLY (SELECT pString = CASE WHEN LEFT(@pString,1) IN ('+','-') THEN STUFF(@pString,1,1,'') ELSE @pString END) x
WHERE SUBSTRING(x.pString,t.N,1) IN ('+','-') 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),
Items = REPLACE(REPLACE(
SUBSTRING(pString,s.N1,ISNULL(NULLIF((LEAD(s.N1,1,1) OVER (ORDER BY s.N1) - 1),0)-s.N1,8000))
,'[',''),']',''),
Signs = CASE
WHEN s.N1-1 = 0 THEN CASE WHEN LEFT(@pString,1) ='-' THEN '-' ELSE '+' END
ELSE SUBSTRING(pString,s.N1-1,1) END
FROM cteStart s;
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply