CLR and MARS

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

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

  • 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?

  • 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?

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

  • 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?

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

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

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

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

  • 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.

  • ...MARS being a good example

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

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

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