Performance issue with tally solution

Question

Performance issue with tally solution

Viewing 15 posts - 106 through 120 (of 522 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

Flo,

I was thinking originally of the second approach - a T-SQL chunking loop. However, the data access from CLR idea is intriguing - it would be nice to have a fully encapsulated CLR implementation - the T-SQL loop is a bit of a hack really.

I normally avoid data access from CLR because it doesn't perform well and excludes parallelism from any plan in which it appears. That's not a concern here since we already kissed parallelism goodbye by passing a LOB.

I've not had as much time as I would have liked this week - hence my lack of posted code recently, but I might have a crack at some of this for my own entertainment tonight.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

Paul White (4/18/2009)
I was thinking originally of the second approach - a T-SQL chunking loop. However, the data access from CLR idea is intriguing - it would be nice to have a fully encapsulated CLR implementation - the T-SQL loop is a bit of a hack really.
I normally avoid data access from CLR because it doesn't perform well and excludes parallelism from any plan in which it appears. That's not a concern here since we already kissed parallelism goodbye by passing a LOB.

Yes the data access is a nice feature but usually quiet slow and no parallelism... Maybe in this case it could work because of the chunk loads but I usually also don't use data access from CLR side.

I've not had as much time as I would have liked this week - hence my lack of posted code recently, but I might have a crack at some of this for my own entertainment tonight.

Did we have a contract for lines-of-code per week? Sorry I didn't know; I think I have to consider for your to-do list for next week :-).

I'm really curious about your night-shift solution!

Wish you best

Flo

Paul White SSC Guru Points: 150467 More actions · Answer 3

Jeff Moden (4/17/2009)
I won't be able to test the CLR methods on my machine to compare apples with apples.

Why not Jeff? You don't need to compile any C# or anything to test the CLR methods...or do you mean something else?

For example, my TVF is attached, expressed entirely in T-SQL.

Paul

edit: Attachment removed for safety reasons! See Jeff's next post...

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Paul White (4/18/2009)
Jeff Moden (4/17/2009)
I won't be able to test the CLR methods on my machine to compare apples with apples.
Why not Jeff? You don't need to compile any C# or anything to test the CLR methods...or do you mean something else?
For example, my TVF is attached, expressed entirely in T-SQL.
Paul

Thanks for the offer and the super easy code, Paul, but I won't run it. It's a lesson learned many years ago. It's a matter of "learned distrust". Please take no offense... it's not that I specifically distrust you, it's that I distrust everyone with compiled code.

The short story is that a very good and trusted friend wrote some code to solve a relatively simple file manipulation problem. With that in mind, I implemented the compiled code. It turned out that an exception he hadn't planned for occurred and instead of doing what it was supposed to, it simply deleted all of the files in the directory it was working on (thoughtful pre-run backups saved that part). It also required a change to the registry for the type of delimiter that was going to be handled by the "Jet" engine... damned good thing I'd made a recovery diskette (it was a long time ago).

It'll sound strange to most, but I've pretty much quit using any of the compiled languages and I don't know how to program in "C". In fact, it would take quite some time for me to even setup VB.NET because I don't have it installed.

So the short answer is, sorry but no... I won't do the CLR testing even when you've made it so very easy for me to.

--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)

Paul White SSC Guru Points: 150467 More actions · Answer 5

Hey Jeff,

Well that makes sense!

I would just add though that compiled code marked SAFE (as mine was) cannot access external resources:

BOL
Only internal computation and local data access are allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry.

It could, however, wreak havoc on your databases as it can use the 'context connection' to run arbitrary SQL in the same server context (same SPID) as the code is called on. This could be circumvented in this case by running it while logged in as a user with extremely limited permissions.

Having said all that, I can now completely understand your position.

Perhaps you might download the free MS .NET Framework SDK (which includes their C# compiler) at some stage, so you could compile posted C# code for yourself? Just a thought. Other free C# compilers are available 😉

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 6

Paul White (4/18/2009)
Hey Jeff,
Well that makes sense!
I would just add though that compiled code marked SAFE (as mine was) cannot access external resources:
BOL
Only internal computation and local data access are allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry.
It could, however, wreak havoc on your databases as it can use the 'context connection' to run arbitrary SQL in the same server context (same SPID) as the code is called on. This could be circumvented in this case by running it while logged in as a user with extremely limited permissions.
Having said all that, I can now completely understand your position.
Perhaps you might download the free MS .NET Framework SDK (which includes their C# compiler) at some stage, so you could compile posted C# code for yourself? Just a thought. Other free C# compilers are available 😉
Paul

That's good info... thanks, Paul.

--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)

Phil Factor SSC-Insane Points: 20244 More actions · Answer 7

Paul, Jeff,

I hate to disagree, but distributing a CLR this way is safe as long as you can read any current .NET language to check what is going on. All you need to do is to create the assembly, and then inspect the source code with NET Reflector to ensure that it is safe- doesn't do anything naughty. You can use NET Reflector with Denis Bauer's Add-in for examining your SQL Server Assemblies. This will give you the source code in whatever language you understand most easily. VB, C#, IronPython Whatever. You can then examine it to make sure it does what it says on the box before letting it anywhere near your servers. You can save the source code and modify your assembly with the languages that are distributed with the NET framework without going anywhere near visual studio.

Best wishes,
Phil Factor

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 8

I agree with Jeff. Sure, it is simple to have a look into the assembly with .Net Reflector. But since you are no C#/VB.Net/... developer it might look strange and unknown for you and you are not able to estimate if it really does no other things.

Jeff does really many test in many threads and it's okay if he says "no" to compiled code he doesn't know.

Just my opinion.

Greets

Flo

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

Phil Factor (4/18/2009)
Paul, Jeff,
I hate to disagree, but distributing a CLR this way is safe as long as you can read any current .NET language to check what is going on. All you need to do is to create the assembly, and then inspect the source code with NET Reflector to ensure that it is safe- doesn't do anything naughty. You can use NET Reflector with Denis Bauer's Add-in for examining your SQL Server Assemblies. This will give you the source code in whatever language you understand most easily. VB, C#, IronPython Whatever. You can then examine it to make sure it does what it says on the box before letting it anywhere near your servers. You can save the source code and modify your assembly with the languages that are distributed with the NET framework without going anywhere near visual studio.

Interesting. Do you have a convenient link for the NET Reflector? Heh... not that I actually have the time right now, but it sounds like a very good thing to know.

--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)

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 10

Florian Reischl (4/18/2009)
I agree with Jeff. Sure, it is simple to have a look into the assembly with .Net Reflector. But since you are no C#/VB.Net/... developer it might look strange and unknown for you and you are not able to estimate if it really does no other things.
Jeff does really many test in many threads and it's okay if he says "no" to compiled code he doesn't know.
Just my opinion.
Greets
Flo

Thanks, Flo. Since I used to be rather a Ninja at Visual Basic (more than 7 years ago), I might be able to handle VB.Net if the Net Reflector works as advertised.

And thanks for the thoughtful concern on compiled code because it is a real concern on my part.

--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)

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 11

Jeff Moden (4/18/2009)
Florian Reischl (4/18/2009)
I agree with Jeff. Sure, it is simple to have a look into the assembly with .Net Reflector. But since you are no C#/VB.Net/... developer it might look strange and unknown for you and you are not able to estimate if it really does no other things.
Jeff does really many test in many threads and it's okay if he says "no" to compiled code he doesn't know.
Just my opinion.
Greets
Flo
Thanks, Flo. Since I used to be rather a Ninja at Visual Basic (more than 7 years ago), I might be able to handle VB.Net if the Net Reflector works as advertised.
And thanks for the thoughtful concern on compiled code because it is a real concern on my part.

Hi Jeff

The reflector is a really great tool to look into any .Net assembly since it is not encrypted. You can easily browse through all classes, methods and properties and see the complete source code. You can also look into all (okay most...) Microsoft .Net Framework DLLs which is sometimes very useful to understand why something does not work or doesn't perform.

Here the link[/url]. I'm not sure if Phil knows another but this is the one I use.

(more than 7 years ago)

So you are already an old hand 😛

Greets

Flo

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 12

Florian Reischl (4/18/2009)
(more than 7 years ago)
So you are already an old hand

Oddly enough, so is the rest of me. 😉

--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)

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 13

Florian Reischl (4/18/2009)
The reflector is a really great tool to look into any .Net assembly since it is not encrypted. You can easily browse through all classes, methods and properties and see the complete source code. You can also look into all (okay most...) Microsoft .Net Framework DLLs which is sometimes very useful to understand why something does not work or doesn't perform.

I just watched the demo video. I'm impressed! Decompilers have come a long way since the last time I looked.

--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)

Paul White SSC Guru Points: 150467 More actions · Answer 14

Phil Factor (4/18/2009)
I hate to disagree, ...

:unsure: Uh oh... :laugh:

Phil Factor (4/18/2009)
All you need to do is to create the assembly, and then inspect the source code with NET Reflector to ensure that it is safe- doesn't do anything naughty...

Hey Phil,

That's a really cool thing - I didn't know that, so many thanks for pointing it out.

It would be neat if the tool came with the standard SQL Server distribution and was referenced in Books Online. Many people who are suspicious of CLR code would probably find it very useful.

Thanks again for a great tip.

BTW - Every time I see a post from you, I wonder whether Phil Factor is 0 or 100, and whether it makes any difference anyway 😉 😛 😀

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Phil Factor SSC-Insane Points: 20244 More actions · Answer 15

[p]useful links[/p]

Net Reflector download page[/url]

Net Reflector soup-to-nuts[/url]

Net Reflector add-ins[/url]

SQL 2005/8 Browser add-in for NET Reflector

[p].... and NET Reflector is the dream tool for SMO programming as it allows you to look through SMO seeing what the classes and methods are. It is way way better than using BOL[/p]

[p] It is also great to use .reflector just so you can translate between C# and VB just so you can see that you can do anything in VB that you can in C# and the performance is exactly the sames since it produces the same IL.[/p]

Best wishes,
Phil Factor