August 27, 2020 at 8:12 am
I'm working out a solution for distributed processing of quite complex calculation. I was also thinking to build the logic such a way that it can be even triggered from SQL Server. However, I have few questions (as follows) on which didn't got any clear answer from my google searches. May be I may not have used rights keywords.
Would really appreciate any suggestion/hint!
August 28, 2020 at 9:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 3, 2020 at 6:36 am
{ obsolete (but can't delete) }
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
September 9, 2020 at 6:47 am
Hi there. I will do my best to answer your questions individually:
I was also thinking to build the logic such a way that it can be even triggered from SQL Server.
How would being trigger from SQL Server help? Understanding this will help determine the best course of action. And do you mean "triggered" in the sense that the process is merely kicked-off by some code running within SQL Server? Or, do you specifically need direct interaction with the data, having your process be inline with one or more queries? If it can be an external process kicked-off by something happening within SQL Server, does it need to run synchronously with that process (i.e. your process will wait for the external process to complete), or can it run asynchronously (i.e. the process in the DB will proceed while the external process is running)?
Does CLR procedure shares the memory & cores allocated to SQL Server instance?
As far as I understand, yes. Things were different (and a bit clunkier) prior to SQL Server 2012, but starting with 2012 they changed how they handle memory management and things have been much better since.
Is it possible to implement the Multi-threading in CLR procedure? If yes then is it a right approach?
Yes, you can do multi-threading in SQLCLR (in an UNSAFE assembly). However, this is rarely the correct approach (it can be in some rare cases, but usually it's not). You would need to be very careful. This is certainly not the place to start if you are new to SQLCLR.
Is there any tradeoff in calling Web API from CLR procedures?
This greatly depends on exactly what you are doing, how often it is being called, how busy the instance gets at peak times, etc. Many people have been successful at calling a web API using SQLCLR, but many others have not been.
In the end, you might be better off creating a console app and then executing that from either xp_cmdshell or a SQL Server Agent job (in an OS / CmdExec job step) using the msdb.dbo.sp_start_job system stored procedure.
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
September 11, 2020 at 12:00 pm
How would being trigger from SQL Server help? Understanding this will help determine the best course of action. And do you mean "triggered" in the sense that the process is merely kicked-off by some code running within SQL Server? Or, do you specifically need direct interaction with the data, having your process be inline with one or more queries? If it can be an external process kicked-off by something happening within SQL Server, does it need to run synchronously with that process (i.e. your process will wait for the external process to complete), or can it run asynchronously (i.e. the process in the DB will proceed while the external process is running)?
The solution I'm thinking is to utilize the computing of the existing available servers than upgrading the licenses to support additional cores.
Additionally, the so called trigger would be a simple call to CLR procedure/function from Stored Procedure/T-SQL and it would be synchronous.
Solution would involve one server hosting SQL Server and other servers will be web servers hosted on intranet.
Trigger of CLR from SQL will act as parent thread that will internally open multiple threads (across multiple servers) and uniformaly distribute the task amongst them. The control shall be returned from CLR procedure upon successful completion of all the jobs of the respective worker threads and after parent thread combines the output from all the worker threads.
I'm not sure if I'm making sense but this was a random thought which I feel can help utilize available resources.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply