June 14, 2010 at 4:54 am
I am trying to write an extended stroed procedure with C# that create several threads
In each thread I want to run a stored procedure in a database wrriten in TSQL .
I am getting this error message :
The protected resources (only available with full trust) were : All The demand Resources were : externalthreeding
I have read that sql server controls the thread pool but I could not understand if it is bosibble to create suce an extended soreporcdures
My goal is to parlelize the execution of batch data processing by devide it into chanks of processing
which runs speratly but concurrently to minimize the batch processing time
(I still do not Know if this is the right foroum ao general is the right one0
June 14, 2010 at 5:02 am
Extended stored procedure or CLR stored procedure?
Extended stored procs have to be written in C++ (not C#) and doing multi-threading in a C++ extended stored proc is very dangerous.
Assuming that you mean CLR, why do you want to do this in a stored proc in SQL?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2010 at 5:13 am
SSIS may be a better fit for this, though you can probably set the permission level to 'external' , assuming this is CLR.
June 14, 2010 at 5:18 am
Dave Ballantyne (6/14/2010)
though you can probably set the permission level to 'external' , assuming this is CLR.
Threading requires unsafe.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2010 at 5:46 am
Yes you are right I ment CLR
The reason is that Ihave a batch processing which varies
between several handred records to oprocess to several milions of records
I wand the CLR procedure to deicde who many time to run the Batch dinamicly
the batch is onther stored procedure which is controlled with paramters send to it
the paramters describe the chunk of recrds that the stored procedure has to process
June 14, 2010 at 6:52 am
Are you calulating fields in your CLR stored procedure? If all you are doing is calling stored procedures from a stored procedure then TSQL should be enough if you configure the MAX DOP settings in the SQL engine. E.g
How to check what MAX DOP should be
select case
when cpu_count / hyperthread_ratio > 8 then 8
else cpu_count / hyperthread_ratio
end as optimal_maxdop_setting
from sys.dm_os_sys_info;
As a last restort you can use query hints with MAXDOP settings.
eg
SELECT field1 FROM table1
OPTION (MAXDOP 2)
This will keep the order but run each query over the CPUs
June 14, 2010 at 7:14 am
MAX DEGREE OF PRALELISEM Will not work here
Because the stored procedure that is beeing called works on create a pool of recrods and then process them one at a time
So I need to Call this stored procedure several times to create several pools wich are have diffrent records
I want to that in parlel
June 14, 2010 at 8:29 am
Gil_Adi (6/14/2010)
Because the stored procedure that is beeing called works on create a pool of recrods and then process them one at a time
Why are you processing records one by one? SQL works best on sets of rows (where it can make the choice about paralleling if necessary), not row by row.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2010 at 10:54 am
I know hat set based thinking is beter for SQL Server
Each records holds the key for a data collecting operation which involves very complicated comutation
these computations can not be preformed for all records at one time.
eache record drwas a diffrent and indevidual path for each element that is computed or rerived from the tables
and I am talking on verry large tables
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply