SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple CLR request


Simple CLR request

Author
Message
Alan Burstein
Alan Burstein
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54162 Visits: 8986
I'm trying to create what I believe should be a very simple Table Valued Function CLR. Here's a T-SQL version of what I'm trying to create:


CREATE FUNCTION dbo.NGLoop(@string varchar(8000), @N int)
RETURNS @NG TABLE (Position int, Token varchar(8000)) AS
BEGIN
DECLARE @token varchar(8000), @position int = 0;

WHILE @position < LEN(@string) - (@N-1) AND (@N > 0 AND @N <= LEN(@string))
BEGIN
SELECT @token = SUBSTRING(@string,@position+1,@N),
@position = @position+1;
INSERT @NG VALUES(@position, @token)
END
RETURN;
END
GO



Here's some examples of what the results should look like (note the comments):


DECLARE @string varchar(8000) = 'abc123';

SELECT * FROM dbo.NGLoop(@string,1); -- should return 6 rows: (1,a), (2,b), (3,c), etc...
SELECT * FROM dbo.NGLoop(@string,2); -- should return 5 rows: (1,ab), (2,bc), (3,c1), etc...
SELECT * FROM dbo.NGLoop(@string,3); -- should return 4 rows: (1,abc), (2,bc1), (3,c12), etc...

SELECT * FROM dbo.NGLoop(NULL,1); -- should return nothing
SELECT * FROM dbo.NGLoop('xxx',-1); -- should return nothing
SELECT * FROM dbo.NGLoop('xxx',NULL); -- should return nothing
SELECT * FROM dbo.NGLoop('xxx',10); -- should return nothing



Hopefully this is simple and self-explanatory enough to understand what I'm trying to do. a C# or VB.Net version should be fine. I've been racking my brain here, any help would be greatly appreciated. Thanks!

-- Alan Burstein


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001

Con Alexis
Con Alexis
SSC Veteran
SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)

Group: General Forum Members
Points: 293 Visits: 256
Shouldn't be too hard using Linq...


using System.Linq;

[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "clrNGLoop_FillRow", TableDefinition = "Position int, Token nvarchar(4000)")]
public static IEnumerable clrNGLoop(SqlString value, SqlInt32 n)
{
int idx = 1;

Lookup<int, char> l = (Lookup < int, char> )value.Value.ToLookup(x => idx++, y => y);

return l.Select(x => new Item() { Position = x.Key, Token = new string(l.Where(y => y.Key >= x.Key && y.Key < (x.Key + n.Value)).Select(z => z.First()).ToArray()) }).Where(v=>v.Token.Length == n.Value);
}

public static void clrNGLoop_FillRow(Object obj, out SqlInt32 position, out SqlString token)
{
Item item = (Item)obj;
position = new SqlInt32(item.Position);
token = new SqlString(item.Token);
}

public class Item
{
public int Position;
public string Token;
}


Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)

Group: General Forum Members
Points: 162257 Visits: 22751
Just curious on why you want to use CLR for this. I hope that you made that function just to make it easy to translate to procedural programming.
If anyone else tries to do something like this in T-SQL, this should be a lot better.


CREATE FUNCTION dbo.NGLoop(@string varchar(8000), @N int)
RETURNS TABLE AS
RETURN
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP(LEN(@string)) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT n, SUBSTRING( @string, n, @N) Token
FROM cteTally
WHERE n <= LEN(@string) - @N + 1
AND @N > 0;
GO




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)

Group: General Forum Members
Points: 870330 Visits: 47424
Luis Cazares (6/17/2016)
Just curious on why you want to use CLR for this.


+1000

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Alan Burstein
Alan Burstein
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54162 Visits: 8986
Con Alexis thank you sir. That's what I was looking for. Sorry for the late reply, yesterday was my birthday.

Luis. It's for an article I'm working on. I have a tally table based version and wanted to compare the performance to a CLR. I figured that a more people who could write CLRs would understand the procedural code.

-- Alan Burstein


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001

Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)

Group: General Forum Members
Points: 162257 Visits: 22751
Alan.B (6/18/2016)
Con Alexis thank you sir. That's what I was looking for. Sorry for the late reply, yesterday was my birthday.

Luis. It's for an article I'm working on. I have a tally table based version and wanted to compare the performance to a CLR. I figured that a more people who could write CLRs would understand the procedural code.

I hope you had a nice birthday!
Seems like I wasn't too far away from the truth :-). I just got puzzled because the code didn't seem like your usual code. And I just thought on leaving the alternative for someone else.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Solomon Rutzky
Solomon Rutzky
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12716 Visits: 3354
Hey Alan. Interesting request. I do have a suggestion for the code, but first I would like to mention that it is probably best to state at the beginning that the desire for the code is to do performance comparisons as it will help inform / guide responders. For example, it would help when deciding between expediency of writing the code and efficiency of processing the code. Case in point: LINQ is very powerful, flexible, and expressive. However, that does come at a cost at runtime.

Also, when comparing performance between T-SQL and SQLCLR code, it is important to test both a VARCHAR(4000) and NVARCHAR(4000) versions of the T-SQL code since SQLCLR can only do NVARCHAR. So testing both using NVARCHAR(4000) will get the truest sense of performance difference, and testing with VARCHAR(4000) will provide a pragmatic difference since if one knows that their data will only ever be VARCHAR, then that reality won't be constrained with trying to be fair in testing ;-). Please note that I am using 4000 instead of 8000 for the VARCHAR datatypes since it doesn't help to test 8000 characters against SQLCLR using NVARCHAR(4000) as the extra 4000 characters will be silently truncated, resulting in a different input string given to the SQLCLR object.

Here is the iTVF with Tally Table I used (I created the VARCHAR(4000) version by copying and pasting it, changing the name, and changing "@String" to be VARCHAR(4000)):


CREATE FUNCTION dbo.NGramITVF(@String NVARCHAR(4000), @N INT)
RETURNS TABLE
--WITH SCHEMABINDING
AS RETURN

WITH nums AS
(
SELECT TOP (LEN(@string) - (@N - 1))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [Num]
FROM [sys].[all_columns]
)
SELECT nums.[Num] AS [Position],
SUBSTRING(@string, nums.[Num], @N) AS [Token]
FROM nums;



And here is the .NET / C# code that I used for the SQLCLR streaming TVF:


[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "NGramSolomon_FillRow",
TableDefinition = "Position INT, Token NVARCHAR(4000)")]
public static IEnumerable NGramSolomon(
[SqlFacet(MaxSize = 4000)] SqlChars InputString, SqlInt32 TokenSize)
{
if (InputString.IsNull || TokenSize.IsNull)
{
yield break;
}

int _Take = TokenSize.Value;
int _Limit = ((int)InputString.Length - _Take);

if (_Take < 1 || _Limit < 0)
{
yield break;
}

ResultRow _Result = new ResultRow();

char[] _Chars = InputString.Value;
char[] _Temp = new char[_Take];

for (int _Index = 0; _Index <= _Limit; _Index++)
{
Array.Copy(_Chars, _Index, _Temp, 0, _Take);

_Result.Position = (_Index + 1);
_Result.Token = new SqlChars(_Temp);

yield return _Result;
}
}

public class ResultRow
{
public int Position;
public SqlChars Token;
}

public static void NGramSolomon_FillRow(Object obj,
out SqlInt32 position, out SqlChars token)
{
ResultRow _Item = (ResultRow)obj;
position = new SqlInt32(_Item.Position);
token = _Item.Token;
}



Test results:

NVARCHAR(4000) Tests (4000 characters):
NGLoop : 60 - 80 ms
SQLCLR_ConAlexis : 375 - 400 ms
iTVF_TallyTable : 2 - 6 ms
iTVF_TallyTableVC : 2 - 6 ms
SQLCLR_Solomon : 2 - 9 ms

NVARCHAR(MAX) Tests (40,000 characters):
iTVF_TallyTable : 34 - 50 ms
SQLCLR_ConAlexis : 39,000 ms
SQLCLR_Solomon : 34 - 56 ms




As you can see, the T-SQL version is only a few milliseconds faster than the SQLCLR. Of course, perhaps Alan's version using his "numbersAB" iTVF will be slightly faster, or maybe had I done an inline Tally Table then the T-SQL version might have been slightly faster. But that still wouldn't be much of a difference.

Still, it seems that there is no compelling reason to use SQLCLR for this particular task. HOWEVER, in so far as the NGram stuff might be used for string searches, it should be noted that depending on what those strings are, there could be a case for using SQLCLR. That case would be if the input strings use combining characters. Combining characters are accents and other marks that can be added to many letters and will display in the same position as the base character, appearing as a single character. And multiple combining characters can be used. When present, the base character and any number of combining characters that follow it need to be treated as a single unit. T-SQL has no facility for handling combining sequences, but .NET does. And no, combining characters are not the same as Supplementary Characters, which can be handled correctly in T-SQL when using a Collation ending in _SC, which were introduced in SQL Server 2012.

Take care,
Solomon..

SQL# - https://SQLsharp.com/ ( SQLCLR library of over 340 Functions and Procedures)
Sql Quantum Lift - https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap - https://SqlQuantumLeap.com/ ( blog )
Alan Burstein
Alan Burstein
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54162 Visits: 8986
Solomon Rutzky (6/26/2016)
Hey Alan. Interesting request. I do have a suggestion for the code, but first I would like to mention that it is probably best to state at the beginning that the desire for the code is to do performance comparisons as it will help inform / guide responders. For example, it would help when deciding between expediency of writing the code and efficiency of processing the code. Case in point: LINQ is very powerful, flexible, and expressive. However, that does come at a cost at runtime.

Also, when comparing performance between T-SQL and SQLCLR code, it is important to test both a VARCHAR(4000) and NVARCHAR(4000) versions of the T-SQL code since SQLCLR can only do NVARCHAR. So testing both using NVARCHAR(4000) will get the truest sense of performance difference, and testing with VARCHAR(4000) will provide a pragmatic difference since if one knows that their data will only ever be VARCHAR, then that reality won't be constrained with trying to be fair in testing ;-). Please note that I am using 4000 instead of 8000 for the VARCHAR datatypes since it doesn't help to test 8000 characters against SQLCLR using NVARCHAR(4000) as the extra 4000 characters will be silently truncated, resulting in a different input string given to the SQLCLR object.

Here is the iTVF with Tally Table I used (I created the VARCHAR(4000) version by copying and pasting it, changing the name, and changing "@String" to be VARCHAR(4000)):


CREATE FUNCTION dbo.NGramITVF(@String NVARCHAR(4000), @N INT)
RETURNS TABLE
--WITH SCHEMABINDING
AS RETURN

WITH nums AS
(
SELECT TOP (LEN(@string) - (@N - 1))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [Num]
FROM [sys].[all_columns]
)
SELECT nums.[Num] AS [Position],
SUBSTRING(@string, nums.[Num], @N) AS [Token]
FROM nums;



And here is the .NET / C# code that I used for the SQLCLR streaming TVF:


[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "NGramSolomon_FillRow",
TableDefinition = "Position INT, Token NVARCHAR(4000)")]
public static IEnumerable NGramSolomon(
[SqlFacet(MaxSize = 4000)] SqlChars InputString, SqlInt32 TokenSize)
{
if (InputString.IsNull || TokenSize.IsNull)
{
yield break;
}

int _Take = TokenSize.Value;
int _Limit = ((int)InputString.Length - _Take);

if (_Take < 1 || _Limit < 0)
{
yield break;
}

ResultRow _Result = new ResultRow();

char[] _Chars = InputString.Value;
char[] _Temp = new char[_Take];

for (int _Index = 0; _Index <= _Limit; _Index++)
{
Array.Copy(_Chars, _Index, _Temp, 0, _Take);

_Result.Position = (_Index + 1);
_Result.Token = new SqlChars(_Temp);

yield return _Result;
}
}

public class ResultRow
{
public int Position;
public SqlChars Token;
}

public static void NGramSolomon_FillRow(Object obj,
out SqlInt32 position, out SqlChars token)
{
ResultRow _Item = (ResultRow)obj;
position = new SqlInt32(_Item.Position);
token = _Item.Token;
}



Test results:

NVARCHAR(4000) Tests (4000 characters):
NGLoop : 60 - 80 ms
SQLCLR_ConAlexis : 375 - 400 ms
iTVF_TallyTable : 2 - 6 ms
iTVF_TallyTableVC : 2 - 6 ms
SQLCLR_Solomon : 2 - 9 ms

NVARCHAR(MAX) Tests (40,000 characters):
iTVF_TallyTable : 34 - 50 ms
SQLCLR_ConAlexis : 39,000 ms
SQLCLR_Solomon : 34 - 56 ms




As you can see, the T-SQL version is only a few milliseconds faster than the SQLCLR. Of course, perhaps Alan's version using his "numbersAB" iTVF will be slightly faster, or maybe had I done an inline Tally Table then the T-SQL version might have been slightly faster. But that still wouldn't be much of a difference.

Still, it seems that there is no compelling reason to use SQLCLR for this particular task. HOWEVER, in so far as the NGram stuff might be used for string searches, it should be noted that depending on what those strings are, there could be a case for using SQLCLR. That case would be if the input strings use combining characters. Combining characters are accents and other marks that can be added to many letters and will display in the same position as the base character, appearing as a single character. And multiple combining characters can be used. When present, the base character and any number of combining characters that follow it need to be treated as a single unit. T-SQL has no facility for handling combining sequences, but .NET does. And no, combining characters are not the same as Supplementary Characters, which can be handled correctly in T-SQL when using a Collation ending in _SC, which were introduced in SQL Server 2012.

Take care,
Solomon..


Great stuff Solomon! Its been a busy couple of weeks. I'm going to compile and play around with this code later today and post back. Thanks again sir.

-- Alan Burstein


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001

Alan Burstein
Alan Burstein
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54162 Visits: 8986
Ok Solomon, I tried to figure this out and failed. Forgive my CLR noobness (I create about 1 CLR/year) and I'm sure this is simple but I'm having a hard time compiling your code. I've been playing around with this on/off for the past week.

I've been trying with the csc.exe in .NET 3.5 and 2.0 I've added (then removed when it did not help) this to the top using <namespace> statements...

using System;
using System.Data;
using Microsoft.SqlServer.Server;



I saved your code as NGramsNV4000.cs and have been attempting to compile the code using this command:

C:\Windows\Microsoft.NET\Framework\v2.0.50727>csc.exe /target:library NGramsNV4000.cs


This is what I get in v2.0.50727:

C:\Windows\Microsoft.NET\Framework\v2.0.50727>csc.exe /target:library NGramsNV4000.cs
Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.5483
for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727
Copyright (C) Microsoft Corporation 2001-2005. All rights reserved.

NGramsNV4000.cs(5,15): error CS1518: Expected class, delegate, enum, interface,
or struct
NGramsNV4000.cs(6,29): error CS1518: Expected class, delegate, enum, interface,
or struct
NGramsNV4000.cs(21,26): error CS1518: Expected class, delegate, enum, interface,
or struct
NGramsNV4000.cs(23,7): error CS1001: Identifier expected
NGramsNV4000.cs(23,9): error CS1518: Expected class, delegate, enum, interface,
or struct
NGramsNV4000.cs(24,7): error CS1001: Identifier expected
NGramsNV4000.cs(24,9): error CS1518: Expected class, delegate, enum, interface,
or struct
NGramsNV4000.cs(24,21): error CS1518: Expected class, delegate, enum, interface,
or struct
NGramsNV4000.cs(24,32): error CS1518: Expected class, delegate, enum, interface,
or struct
NGramsNV4000.cs(31,23): error CS1518: Expected class, delegate, enum, interface,
or struct
NGramsNV4000.cs(34,2): error CS1022: Type or namespace definition, or
end-of-file expected
NGramsNV4000.cs(6,27): error CS0101: The namespace '<global namespace>' already
contains a definition for '?'
NGramsNV4000.cs(4,57): (Location of symbol related to previous error)
NGramsNV4000.cs(23,7): error CS0101: The namespace '<global namespace>' already
contains a definition for '?'
NGramsNV4000.cs(4,57): (Location of symbol related to previous error)
NGramsNV4000.cs(24,7): error CS0101: The namespace '<global namespace>' already
contains a definition for '?'
NGramsNV4000.cs(4,57): (Location of symbol related to previous error)
NGramsNV4000.cs(24,31): error CS0101: The namespace '<global namespace>' already
contains a definition for '?'
NGramsNV4000.cs(4,57): (Location of symbol related to previous error)


This is what I get in v3.5:

 C:\Windows\Microsoft.NET\Framework\v3.5>csc.exe /target:library NGramsNV4000.cs
Microsoft (R) Visual C# 2008 Compiler version 3.5.30729.5420
for Microsoft (R) .NET Framework version 3.5
Copyright (C) Microsoft Corporation. All rights reserved.

NGramsNV4000.cs(8,15): error CS1518: Expected class, delegate, enum, interface,
or struct
NGramsNV4000.cs(9,29): error CS1518: Expected class, delegate, enum, interface,
or struct
NGramsNV4000.cs(24,26): error CS1518: Expected class, delegate, enum, interface,
or struct
NGramsNV4000.cs(26,7): error CS1001: Identifier expected
NGramsNV4000.cs(26,9): error CS1518: Expected class, delegate, enum, interface,
or struct
NGramsNV4000.cs(27,7): error CS1001: Identifier expected
NGramsNV4000.cs(27,9): error CS1518: Expected class, delegate, enum, interface,
or struct
NGramsNV4000.cs(27,21): error CS1518: Expected class, delegate, enum, interface,
or struct
NGramsNV4000.cs(27,32): error CS1518: Expected class, delegate, enum, interface,
or struct
NGramsNV4000.cs(34,23): error CS1518: Expected class, delegate, enum, interface,
or struct
NGramsNV4000.cs(37,2): error CS1022: Type or namespace definition, or
end-of-file expected


-- Alan Burstein


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)

Group: General Forum Members
Points: 870330 Visits: 47424
Alan.B (7/7/2016)
Forgive my CLR noobness (I create about 1 CLR/year)...


You're ahead of me, there. My record has been an average of about -1 per year. That's not a mistake. I've been replacing SQLCLR with T-SQL. :-D The best replacement, so far, has been for SQLCLR generic audit triggers at work, which were crushing performance. It was taking 4 minutes to update just 4 columns of just 10,000 rows of some 100+ column tables (not my design, for sure). The rewrite got that down to less than 800ms (yeah, I know... I'm slipping :-D) on the widest of the tables.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search