December 22, 2015 at 1:51 pm
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
December 22, 2015 at 2:34 pm
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
December 22, 2015 at 2:45 pm
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
Change is inevitable... Change for the better is not.
December 22, 2015 at 2:48 pm
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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
December 22, 2015 at 5:21 pm
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.
December 22, 2015 at 6:01 pm
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
Change is inevitable... Change for the better is not.
December 23, 2015 at 4:46 am
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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
December 23, 2015 at 6:59 am
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
December 26, 2015 at 2:47 am
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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
December 26, 2015 at 3:33 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 26, 2015 at 10:41 am
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
December 26, 2015 at 8:00 pm
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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
December 26, 2015 at 8:19 pm
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
December 29, 2015 at 8:11 pm
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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
December 29, 2015 at 8:50 pm
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply