CLR and MARS

Question

Post reply

CLR and MARS

tod.mckenna

SSC Journeyman

Points: 88
More actions
November 1, 2007 at 7:41 am

#183545

A colleague and I have been stumped with this one.
We found (more like: hit a brick wall) that server-side CLR code does not support MARS (Multiple Active Result Sets). Our testing with oleDB gave much better results, but to my understanding the oleDB implementation is not MARS. It actually creates a new connection under the covers, skirting around the one-connection-per-session issue that apparently exists in the CLR (and in SQL Server 2000).
A couple of questions:
1.) Is there any viable workaround? We would like multiple results on a single connection. I read that using cursors might help, but we've been unable to get this to work with our scenarios.
2.) Does the next version of SQL Server promise any improvements in this area?
3.) Or, are we just complete dumbos? Does MARS work with CLR? Are we just missing something?
Thanks in advance!
Tod (http://blog.todmeansfox.com)
Blog | Home | Stew

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 1

I guess my first question would be... what does the CLR do?

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

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 2

MARS works with regular c# outside of SQL Server so I would assume that it should work inside as well... can you give us an example of your code?

tod.mckenna SSC Journeyman Points: 88 More actions · Answer 3

Hi Jeff, Joe

It isn't easy to provide a code sample, but the code works as expected -- with MARS -- when run outside of the CLR. But not inside. One connection, one result set. It seems that the workaround is to use here is what MS says about it: http://msdn2.microsoft.com/en-us/library/ms160831.aspx

The relevant part: "Currently, MARS is not implemented for the in-process provider used for server-side programming. To work around this limitation, you can use server-side cursors."

So, it seems that our options are a bit limited. The code is rather complex, has some recursion, and requires several query results available. We'll have to bring more into memory, but if we engineer correctly, we might be able to minimize the performance hit.

I wonder if this is addressed in '08?

Blog | Home | Stew

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

Hi Tod,

Still would like to know what the CLR does (general explanation would do) because there's a lot of times when folks will write a CLR instead of doing it in T-SQL simply because they're not sure it can be done in T-SQL... if we can do what the CLR does, it may simplify your life a bit. 🙂

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

I don't claim to know what they plan on doing in '08. I do think you've found the current backdoor for what you're wanting to do however (with OLEDB).

I think the deathknell on the integration was in the "CLR Best Practices" document, which roughly stated (I am paraphrasing) that "when dealing with data access, TransactSQL should be used". In other words, they didn't have plans to provide full framework optimization to data access from within CLR when T-SQL will always be more efficient.

In short - if your process is heavily data access driven - go T-SQL instead. Keep the procedural-heavy parts in CLR.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

tod.mckenna SSC Journeyman Points: 88 More actions · Answer 6

Thanks Matt, this is good advice. We'll look at the code again and see if T-SQL can do the job alone (or with limited CLR).

Blog | Home | Stew

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 7

please look into this area, explains more on MARS compatability with sql

http://blogs.msdn.com/angelsb/archive/2004/09/07/226597.aspx

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 8

Ok... 3rd time I've asked... If you let us know what the CLR is supposed to do, we can tell you if it can be done in T-SQL and maybe even help you crank out some code. 😉

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

tod.mckenna SSC Journeyman Points: 88 More actions · Answer 9

Jeff Moden (11/2/2007)
Ok... 3rd time I've asked... If you let us know what the CLR is supposed to do, we can tell you if it can be done in T-SQL and maybe even help you crank out some code. 😉

Sorry Jeff!

Our problem is simply that we are trying to have 3 or more result sets open at a time in the CLR. Basically, one result is processed in a loop, creating an additional set that is further processed using some complex business logic (which also requires a result set).

In light of Matt's post (and probably what you would be getting to), we're re-thinking what we hope to accomplish with CLR. We're no longer thinking of it as an all-encompassing replacement for TSQL. Bottom line: continue to use TSQL for what TSQL does best (data access, set logic, etc), and use the CLR for other tasks (and utilize extensive class libraries, compiled code performance, our better C# knowledge, etc.).

Honestly, I'm not sure where I got the idea that CLR could essentially replace T-SQL. Everywhere I read states otherwise, that they are meant to compliment each other.

Blog | Home | Stew

tod.mckenna SSC Journeyman Points: 88 More actions · Answer 10

tod.mckenna

SSC Journeyman

Points: 88

November 2, 2007 at 9:53 am

#747837

...MARS being a good example

Blog | Home | Stew

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 11

Hi Tod,

I think you're headed down the right path... you haven't said anything (yet) that I don't think can be done in T-SQL. Your re-evaluation of the situation sounds like you've getting things under control.

If you need any help with what you're doing, don't hesitate to post back... there's a ton of good people on this forum that would love to help in this.

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