CLR Yes or no?

  • mishka-723908

    SSCommitted

    Points: 1672

    Hello,

    We have a Warehouse DB server. The main DB is the Warehouse and there are a few auxiliary databases on the server as well. I have a developer who wants to add the first CLR to the server and I have concerns. In the past I've heard issues for SQL performance., Security, deployment etc... Im also concerned because the server has our Datawarehouse.

    If anyone has any good input, I would appreciate it.

    Thank you,

    Michael

  • Eric M Russell

    SSC Guru

    Points: 125089

    It depends on what they're planning to use CLR functions for.

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

  • Jeff Moden

    SSC Guru

    Points: 996622

    mishka-723908 (12/22/2015)


    Hello,

    We have a Warehouse DB server. The main DB is the Warehouse and there are a few auxiliary databases on the server as well. I have a developer who wants to add the first CLR to the server and I have concerns. In the past I've heard issues for SQL performance., Security, deployment etc... Im also concerned because the server has our Datawarehouse.

    If anyone has any good input, I would appreciate it.

    Thank you,

    Michael

    What does the SQLCLR do? And what has it been configured as? Safe, External, Unsafe???

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Solomon Rutzky

    SSCoach

    Points: 16251

    SQLCLR is not inherently slow. Like most technologies it can be used to do things faster than T-SQL can, or can be used inappropriately and be much slower. Please see my series, Stairway to SQLCLR[/url], here on SQL Server Central, for lots of information and examples.

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • mishka-723908

    SSCommitted

    Points: 1672

    It is not yet in place. Currently we have a service that does calculations to populate a table. The developer is asking if we could have a clr to call the service and directly use it from sql. This is a warehouse server.

  • Jeff Moden

    SSC Guru

    Points: 996622

    That might be a decent use but it really depends on how the CLR is written. My suggestion would be to test it for performance in a Dev environment AND check memory on the server. If someone makes the mistake of loading all the data into a local array instead of passing it through to SQL Server, there could be a big problem.

    In this case, I'd say "Give it a shot in Dev with the understanding that if it doesn't perform well or overwhelms the server in any way, we can't use it and we'll have to find another way".

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Solomon Rutzky

    SSCoach

    Points: 16251

    mishka-723908 (12/22/2015)


    It is not yet in place. Currently we have a service that does calculations to populate a table. The developer is asking if we could have a clr to call the service and directly use it from sql. This is a warehouse server.

    Calling a web service can be done, and depending on several factors, if done properly it has the potential to be quite helpful. I posted several thoughts on this topic on the following StackOverflow answers:

    I hope that info helps.

    Take care, Solomon...

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Eric M Russell

    SSC Guru

    Points: 125089

    The only practical use for CLR in a data warehouse that comes to mind is perhaps a custom aggregate function.

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

  • Solomon Rutzky

    SSCoach

    Points: 16251

    Eric M Russell (12/23/2015)


    The only practical use for CLR in a data warehouse that comes to mind is perhaps a custom aggregate function.

    Just out of curiosity, what exactly is this opinion based on?

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Phil Parkin

    SSC Guru

    Points: 244578

    Solomon Rutzky (12/26/2015)


    Eric M Russell (12/23/2015)


    The only practical use for CLR in a data warehouse that comes to mind is perhaps a custom aggregate function.

    Just out of curiosity, what exactly is this opinion based on?

    I suspect it's because Eric is thinking that any 'standard' CLR processing should occur on data before it gets into the DW.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Eric M Russell

    SSC Guru

    Points: 125089

    Phil Parkin (12/26/2015)


    Solomon Rutzky (12/26/2015)


    Eric M Russell (12/23/2015)


    The only practical use for CLR in a data warehouse that comes to mind is perhaps a custom aggregate function.

    Just out of curiosity, what exactly is this opinion based on?

    I suspect it's because Eric is thinking that any 'standard' CLR processing should occur on data before it gets into the DW.

    It's considered best practice, at least according to the widely adopted Kimbal methodology, that data transformation or complex joins occur either upfront during ETL stage, or downstream in the presentation layer. I can perhaps understand occasional use of custom SQL aggregates for specialzed reporting, but if you're doing stuff like parsing multivalued text or xml columns, then that suggests the DW hasnt been dimensionally modeled in proper star schema (or 3NF) form.

    But that's speaking generally and why I asked earlier for details on what the intended usage of CLR is for. I mean, if I were assembling a team to develop a DW, I wouldn't look for folks experienced in CLR. It's essentially a niche technology in the context of data warehousing.

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

  • Solomon Rutzky

    SSCoach

    Points: 16251

    Eric M Russell (12/26/2015)


    Solomon Rutzky (12/26/2015)


    Eric M Russell (12/23/2015)


    The only practical use for CLR in a data warehouse that comes to mind is perhaps a custom aggregate function.

    Just out of curiosity, what exactly is this opinion based on?

    It's considered best practice, at least according to the widely adopted Kimbal methodology, that data transformation or complex joins occur either upfront during ETL stage, or downstream in the presentation layer. I can perhaps understand occasional use of custom SQL aggregates for specialzed reporting, but if you're doing stuff like parsing multivalued text or xml columns, then that suggests the DW hasnt been dimensionally modeled in proper star schema (or 3NF) form.

    But that's speaking generally and why I asked earlier for details on what the intended usage of CLR is for. I mean, if I were assembling a team to develop a DW, I wouldn't look for folks experienced in CLR. It's essentially a niche technology in the context of data warehousing.

    Eric, thank you for providing that context. Your comment makes a lot more sense now :-). I do understand what you are saying regarding the complexity being ideally dealt with "pre" or "post" DW, though I would still reserve final judgement until learning the specific needs (and constraints!) of the system.

    Also, I would think that something like RegEx functions to assist filtering would be acceptable since that is not an issue of either ETL or presentation. And I'm sure that there are similar functions that can benefit the DW queries.

    Take care,

    Solomon...

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Eric M Russell

    SSC Guru

    Points: 125089

    Well, im still not sure about RegEx in a data warehouse. Parsing text columns suggests things like product descriptions were not identified by category in a way thats useful to the business during the dimensional modeling phase.

    Although parsing data is common when staging data from the source system, I just wouldnt want to see end users writing regex or clr queries against the data warehouse, which should be conformed. It would suggest poor upfront modeling and requirements gathering.

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

  • Solomon Rutzky

    SSCoach

    Points: 16251

    Eric M Russell (12/26/2015)


    Well, im still not sure about RegEx in a data warehouse. Parsing text columns suggests things like product descriptions were not identified by category in a way thats useful to the business during the dimensional modeling phase.

    Although parsing data is common when staging data from the source system, I just wouldnt want to see end users writing regex or clr queries against the data warehouse, which should be conformed. It would suggest poor upfront modeling and requirements gathering.

    Does this mean that you don't allow for "contains" / LIKE '%' + @SearchTerm + '%' type filtering of dimension tables? Or does such a desire fit into your second paragraph as merely indicating the need for another categorization?

    I am really just asking. I do not claim to be a BI / DW expert, and much of my experience in that area was on a system that, for various reasons, probably broke more rules than it followed ;-).

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Jeff Moden

    SSC Guru

    Points: 996622

    Eric M Russell (12/26/2015)


    Well, im still not sure about RegEx in a data warehouse. Parsing text columns suggests things like product descriptions were not identified by category in a way thats useful to the business during the dimensional modeling phase.

    Although parsing data is common when staging data from the source system, I just wouldnt want to see end users writing regex or clr queries against the data warehouse, which should be conformed. It would suggest poor upfront modeling and requirements gathering.

    We've actually been through this before where a RegEx SQLCLR was put up against the likes of LIKE. It was an extensive post. I'll see if I can find it. IIRC, if something could be done with LIKE in SQL Server, it pretty much blew the doors of the RegEx SQLCLR. Disclaimer... I haven't personally tried it because I didn't want to load the SQLCLR. Someone else did the testing. And, IIFRC, Solomon was seriously involved in that thread.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

Viewing 15 posts - 1 through 15 (of 22 total)

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