Deterministic UDF slower than Non-Deterministic?

Question

Post reply

Deterministic UDF slower than Non-Deterministic?

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442447

August 3, 2010 at 4:38 pm

#1201841

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.

dant12 SSCrazy Points: 2712 More actions · Answer 2

dant12

SSCrazy

Points: 2712

August 3, 2010 at 10:49 pm

#1201882

actually that is the expected output, you can try setting it all to upper and adding extra spaces, dots

--
Thiago Dantas
@DantHimself

ludovitDrenka Grasshopper Points: 16 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

{edit} This post was related to a spam post which has since been deleted, so I've "deleted" mine. Thanks folks.

--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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

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 codes

That 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

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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 6

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

dant12 SSCrazy Points: 2712 More actions · Answer 7

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 codes

That 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

Paul White SSC Guru Points: 150457 More actions · Answer 8

Paul White

SSC Guru

Points: 150457

September 16, 2010 at 8:02 am

#1222210

Thanks, I look forward to it.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

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 codes

That 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

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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions