The SQLCLR Impact

  • We use CLRs (have for some times) with custom C# code. Previously, it ran on all of our customer-facing SQL servers (non-SSRS report servers), now only one server uses it to support one customer.

    We do custom projects periodically that will use CLRs.

    Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

  • I've only used it once, and to the best of my knowledge, my company has only used it on two projects. Both were just before the advent of LINQ to SQL and EF. In the project I worked on, we used stored procedures to do all the data access and a bit of aggregation. As the project wore on, the requirements essentially transformed into a full rules engine. My client was not familiar with C# and wanted to be able to manipulate the rules from within SQL Server directly. I eventually switched to a SQLCLR SP to iterate the and apply the rules once my original dynamic SQL SP grew to several hundred, unreadable lines. SQLCLR really cleaned things up and sped the process up from 1.5 hours to 1 hour.

    I recently did a refresh of that project, which included a move from SQL 2005 to SQL 2008. I was able to get rid of the remaining dynamic SQL and switched to a streaming TVF rather than the SP. The rules now apply in fifteen minutes.

    I'm sure other approaches are better, and were we given the time to build a more robust front-end, we could probably have moved a lot of the data access to the application layer. Nevertheless, the SQLCLR really helped in terms of cutting down processing time.

  • At some point, I think we were using or evaluating a 3rd party column encryption solution that leveraged CLR functions. However, what ultimately got deployed to production was something I implemented using views and native symmetric key functions.

    I can see the argument for leveraging CLR functions in niche case usage scenarios where custom pattern matching or aggregation is required, but I've actually never had a real use for it.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • my old shop used it for two reasons; regular expressions for doing ETL on staging tables(no SSIS in that shop...everything is TSQL), and a custom implementation of AES encryption; the encryption was used in multiple applications as a class inside them, and we needed it on the server at times as well. That class did not produce the same results as standard AES encryption, so we pushed it into an CLR so that we did not have to switch and modify legacy apps.

    now test wise, i've done a zillion things with CLR: reports to disk in various text formats, including html, read web pages, results form web services, zip and unzip files , PGP encryption, and so many examples i'd have to look at my projects folder.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I used it to build aggregate functions that should be in SQL in opinion.

    Example: the ability to "Sum" a char/varchar/etc field.

  • drew.king1 (2/21/2014)


    I used it to build aggregate functions that should be in SQL in opinion.

    Example: the ability to "Sum" a char/varchar/etc field.

    Please, explain what it means to summarize a character column.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • I have 5 clients using the same piece of software from a 3rd party vendor. This vendor has rolled out CLR procedures to each of the clients. In this particular case, the CLR starts to leak memory and eventually impacts SQL Server. The vendor is working on a fix, but in this case the implementation could be better.

    In other cases, CLR works marvelously.

    In any case, the overall footprint of CLR (from my perspective) is a very small footprint.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • sum as in addition

    t1:

    animal Type

    ============

    dog Pet

    cat Pet

    cow Livestock

    SELECT Type, Concat(animal) as animals

    from t1

    group by type

    Type Animals

    ===============

    Livestock cow

    Pet dog, cat

  • Never found a good use for it. Always return sets and process externally if required.

  • We have a small IT department. There used to be 4 of us, but we're down to 2. One of those who left was using SQLCLR, but no one else has since he left. I'm not even sure where with it, or if he just was researching it and didn't get too far into it. And at least for me, I can't ask him. When he left he was very ill. He passed away a few months after that. It's very sad, as he was a close friend of mine.

    Well, at least if he did do something with SQLCLR, I don't see any impact. It would have involved some software he was working on which we're no longer using.

    Rod

  • I haven't touched the CLR.

  • The only use at my previous shop was for string aggregates, where it's orders of magnitude faster than TSQL. I second the thought that string aggregates should be native SQL.

  • We use CLR quite a bit for functions that are not well suited to TSQL. If it works well in TSQL, then do it there, but some things are obviously better in CLR.

    Examples of how we use it are:

    Regular Expressions

    Import flat file image (blob) to database

    Export image to flat file

    File manipulation (copy, delete, move etc...)

    Sending e-mail on Express version of SQL

    Web Service calls

    Ping across network

    Starting & stopping services locally or on remote computers

  • Eric M Russell (2/21/2014)


    drew.king1 (2/21/2014)


    I used it to build aggregate functions that should be in SQL in opinion.

    Example: the ability to "Sum" a char/varchar/etc field.

    Please, explain what it means to summarize a character column.

    declare @animal table (

    animal_name varchar(30) not null,

    animal_type varchar(30) not null,

    primary key (animal_name) );

    insert into @animal ( animal_name, animal_type )

    values ('dog','Pet'),

    ('cat','Pet'),

    ('cow','Livestock');

    select a.animal_type,

    (

    select animal_name + ', '

    from @animal as b

    where b.animal_type = a.animal_type

    order by animal_name

    for xml path('')

    ) animal_names

    from @animal as a

    group by a.animal_type;

    animal_type animal_names

    Livestock cow,

    Pet cat, dog,

    I'd call that concatenating or serializing and do that on occasion for stored procedurs that return an application facing resultset. I typically use a correlated sub-query with the "for xml" clause. Did you find that using a CLR function scales better across a large recordset?

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • We use CLR and it is, in fact, integral to a number of different pieces of functionality within our software. Probably the most interesting way we use CLR is as a backbone for an extensible search provider plugin architecture. We essentially allow our users to join search results to a custom table valued function or stored procedure that generates a table. This allow us to keep our plugin architecture really generic and has the added benefit of allowing our users to write a single query that merges search results from many different search providers.

Viewing 15 posts - 16 through 30 (of 51 total)

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