Viewing 13 posts - 1 through 13 (of 13 total)
I had a little more fun... again using 17,000+ tokens.
I put the CTE version inside the original function by creating 200 character tokens using the original method (sort of), assuming...
March 7, 2011 at 10:17 pm
My theory on the XML problem was that it was actually a CLR call-out. Tally seems to work well, it shows an up front execution cost that is not...
March 7, 2011 at 7:40 pm
I ran the three types again, with "SET EXECUTION TIME ON" and...
...8788 tokens:
XML:CPU time = 203722 ms
Tally:CPU time = 47 ms
Original:CPU time = 686 ms
March 5, 2011 at 2:47 pm
arturv (3/4/2011)
why dont we keep it simple?
CREATE TABLE #test (id INT NULL, NAME NVARCHAR(20) NULL)
INSERT INTO #test ( id, name )
VALUES ( 101, 'Apple' ),
...
March 4, 2011 at 9:07 pm
That function looks pretty similar to the one from the original article, which I called "fn_Split". It does fine, too, and it will work on older versions of SQL...
March 4, 2011 at 8:52 pm
The tally table method seems to work fine, but the estimated and actual execution plans show a pretty severe penalty. My guess is that the initial file I/O and...
March 2, 2011 at 9:26 pm
Norbert, try to fix it the way you want it, and post your code if you get stuck. I intentionally attempted to keep the last token if the delimiter...
February 28, 2011 at 10:38 pm
CTE version, made more like the SUBSTRING version:
CREATE FUNCTION [dbo].[fnStringSplit_CTE]
(
@SourceString VARCHAR(MAX),
@Delim CHAR(1)
)
RETURNS @Values TABLE
(
VALUE VARCHAR(MAX)
)
AS
BEGIN
with split(i, token, remainder) as
(select 1
, left(@SourceString,charindex(@delim,@SourceString)-1)
, LTRIM(right(@SourceString,len(@SourceString)-CHARINDEX(@delim,@SourceString)))
union all
select i + 1
,case when charindex(@delim,remainder)...
February 25, 2011 at 7:33 pm
I ran the XML, CTE, and the SUBSTRING functions through and checked the estimated to actual execution plans. The estimated shows the XML_Reader is, by far, the...
February 25, 2011 at 7:32 pm
I am fairly certain that CTE will be faster...
/*sql server 2008 CTE split and join fun*/
/*split begins*/
if object_id(N'tempdb..#split') is not null drop table #split;
if object_id(N'tempdb..#joined') is not null drop table...
February 25, 2011 at 12:36 am
You can use charindex with substring() but it is slower than parsename. I don't know why. I would love to see the code behind them to understand why.
January 9, 2010 at 11:50 am
I actually tested 4 tiny int conversions and 1 int conversions and binary always beat 'em in conversion costs (cheaper on the CPU and the same size). If you...
December 7, 2009 at 9:10 pm
I think a 4 byte binary field is the best. You can use substring function to pick out the octets and it takes the least amount of work to...
December 1, 2009 at 10:36 pm
Viewing 13 posts - 1 through 13 (of 13 total)