August 3, 2010 at 4:38 pm
Based on the sample data provided, what are the expected results? I know I need to work on my code some as there is no difference between input and output in what I ran. Would help to know if my output is correct by having something to check it against.
Thank you.
August 3, 2010 at 10:49 pm
actually that is the expected output, you can try setting it all to upper and adding extra spaces, dots
--
Thiago Dantas
@DantHimself
August 11, 2010 at 6:40 pm
I am not .net guru, but you can tray this.
In VB.NET code
Dim strCharAnterior As String = "."
Dim strChar As String
Dim x As Integer = 0
Dim intPosicaoPrimeiroEspaco As Integer = 0
Dim strRetorno As New StringBuilder
Dim strPrimeiraPalavra As String = ""
should be
Dim strCharAnterior As Char = "."
Dim strChar As Char
Dim x As Integer = 0
Dim intPosicaoPrimeiroEspaco As Integer = 0
Dim strRetorno As New StringBuilder
Dim strPrimeiraPalavra As StringBuilder = ""
In CLR code type String is immutable.
As result
strCharAnterior = strChar
yields to new memory allocations.
Memory is freed by Garbage Colector (GC).
Take look at performance counters in
.NET CLR memory group of sqlserver process.
September 15, 2010 at 10:02 pm
{edit} This post was related to a spam post which has since been deleted, so I've "deleted" mine. Thanks folks.
--Jeff Moden
September 15, 2010 at 10:05 pm
Jeff Moden (8/3/2010)
dant12 (8/2/2010)
yeah sure ill send you a .csv if your willing to play with it, but i wouldnt bother so much. you already gave me lots of ideas with both ur sample codesThat would be very cool. The reason I'm asking is because I want to try to find what went wrong. On 8K rows, your function took about 16 seconds to run and mine took 1/4 of a second. Yup... I've got an older slower machine but that a huge difference from what you posted as time and I wanted to find out why.
Thanks.
I'm sorry... I lost track of this post. Would you tell me, please, what the collation for the columns involved are? If the collation is the default, please tell me what the default collation on your server is. Thanks.
--Jeff Moden
September 15, 2010 at 11:34 pm
dant12 (7/29/2010)
I'm no .NET programmer but i did a function that manipulates string inputs and if i set IsDeterministic=true on the CLR function it consumes around 40% cpu and also takes 40% longer to complete compared to not setting the IsDeterministic flag. As im no CLR Integration expert, anyone cares to explain this?
A deterministic function gives the SQL Server query optimizer more plan choices, which is normally a good thing. The optimizer might, for example, choose to cache results from the function call (in a table spool). This is a good optimization if many duplicate input values are presented to the function. That optimization is obviously not safe if the function is non-deterministic: the cached result from a previous invocation could well be 'wrong'.
Whether to cache function results or not is a cost-based decision: the optimizer tries to balance the cost of storing the results against that of recomputing the function for every row. Sadly, the optimizer doesn't have much in the way of useful information about scalar functions (T-SQL or CLR) so it guesses at the cost. In some cases, this cost will be wrong, and you'll get a suboptimal plan as a result.
It would help a great deal if you could post actual execution plans for the deterministic and non-deterministic cases, as that would allow me to say for sure what the cause is, and what you might do to help the optimizer along. If you cannot post the *.sqlplan files for some reason, even an image of them would be better than nothing.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 16, 2010 at 7:36 am
Jeff Moden (9/15/2010)
Jeff Moden (8/3/2010)
dant12 (8/2/2010)
yeah sure ill send you a .csv if your willing to play with it, but i wouldnt bother so much. you already gave me lots of ideas with both ur sample codesThat would be very cool. The reason I'm asking is because I want to try to find what went wrong. On 8K rows, your function took about 16 seconds to run and mine took 1/4 of a second. Yup... I've got an older slower machine but that a huge difference from what you posted as time and I wanted to find out why.
Thanks.
I'm sorry... I lost track of this post. Would you tell me, please, what the collation for the columns involved are? If the collation is the default, please tell me what the default collation on your server is. Thanks.
No problem mate, I moved along on this issue, I'm feeding this topic on pure curiosity
The collation is Latin1_General_CI_AS
Paul,
I'll provide the .sqlplans asap
--
Thiago Dantas
@DantHimself
September 16, 2010 at 8:02 am
Thanks, I look forward to it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 17, 2010 at 6:14 pm
dant12 (9/16/2010)
Jeff Moden (9/15/2010)
Jeff Moden (8/3/2010)
dant12 (8/2/2010)
yeah sure ill send you a .csv if your willing to play with it, but i wouldnt bother so much. you already gave me lots of ideas with both ur sample codesThat would be very cool. The reason I'm asking is because I want to try to find what went wrong. On 8K rows, your function took about 16 seconds to run and mine took 1/4 of a second. Yup... I've got an older slower machine but that a huge difference from what you posted as time and I wanted to find out why.
Thanks.
I'm sorry... I lost track of this post. Would you tell me, please, what the collation for the columns involved are? If the collation is the default, please tell me what the default collation on your server is. Thanks.
No problem mate, I moved along on this issue, I'm feeding this topic on pure curiosity
The collation is Latin1_General_CI_AS
Paul,
I'll provide the .sqlplans asap
Yep... agreed... that's kind of where I'm at right now. Thanks for your time. 🙂
--Jeff Moden
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply