Execute Stored Procedures in Parallel

After my post here, It seems that everything is working fine but I need performance out of it an my last resort is to Execute Stored Procedures in Parallel so I can run multiple instances in one run. To achieve that I need to create a CLR Stored Procedure so I can run Execute commands in a thread. So what do I need to achieve that?

CLR Stored procedures are routines that cannot be used in scalar expressions. Unlike scalar functions, they can return tabular results and messages to the client, invoke data definition language (DDL) and data manipulation language (DML) statements, and return output parameters.In SQL Server, you can develop a database objects inside an instance of SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR). Database objects that can leverage the rich programming model provided by the CLR include triggers, stored procedures, functions, aggregate functions, and types.Creating a CLR stored procedure in SQL Server involves the following steps:

Define the stored procedure as a static method of a class in a language supported by the .NET Framework. For more information about how to program CLR stored procedures, see CLR Stored Procedures. Then, compile the class to build an assembly in the .NET Framework by using the appropriate language compiler.

Register the assembly in SQL Server by using the CREATE ASSEMBLY statement. For more information about how to work with assemblies in SQL Server, see Assemblies.

Create the stored procedure that references the registered assembly by using the CREATE PROCEDURE statement.

Now having said that lets start creating our CLR Stored Procedure, fire up the Visual Studio and choose a Database/SQL Server Project

New Database Project

Once it is created it will ask you for the Database and choose the database you want run the CLR Stored Procedure from. Then the coding begins:

First you need to add a new item which is a stored procedure

Stored Procedure Item

As you might have noticed you can also add an Aggregate, User Defined Function, Triggers and User Defined Types. Once you added the Stored Procedure then the fun begins. Here is what I have done.

Now you might have noticed that I have a class called ExecuteSQL, that class is the actual class that performs the execute, the code above just takes care of the threads. Here is the code for ExecuteSQL:

21 Responses to Execute Stored Procedures in Parallel

A .NET Framework error occurred during execution of user-defined routine or aggregate “spExecuteParallel”:
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All
The demanded resources were: ExternalThreading

Error 1 CREATE ASSEMBLY for assembly ‘SqlServerProject1’ failed because assembly ‘SqlServerProject1’ is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. SqlServerProject1

now i cant see the stored running but im getting this errors…
many thanks for posting back…

4:02:01 PM : Start : InternalSiteDB_Dev
4:02:01 PM : Start : InternalSiteDB_Dev
4:02:01 PM : Start : InternalSiteDB_Dev
4:02:01 PM : Start : InternalSiteDB_Dev
4:02:01 PM : Start : InternalSiteDB_Dev
4:02:01 PM : Start : InternalSiteDB_Dev
4:02:01 PM : Start : InternalSiteDB_Dev
4:02:01 PM : Start : InternalSiteDB_Dev
4:02:21 PM : Stop : InternalSiteDB_Dev
All Thread have Stopped with 8 Error/s
InternalSiteDB_Dev : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)
InternalSiteDB_Dev : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)
InternalSiteDB_Dev : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)
InternalSiteDB_Dev : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)
InternalSiteDB_Dev : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)
InternalSiteDB_Dev : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)
InternalSiteDB_Dev : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)
InternalSiteDB_Dev : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)
Msg 6522, Level 16, State 1, Procedure spExecuteParallel, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate “spExecuteParallel”:
System.Exception: Error Occurred.
System.Exception:
at StoredProcedures.spExecuteParallel(String DB, Int32 MaxDOP, String TSQL, Int32 msDelay, Int32 Retries)
.

Parallelization for queries (SELECT) is covered quite well by the SQL engine itself, but when it comes to large volume data modifications (UPDATE, INSERT, DELETE), the standard engine does parallelize towards best use of all available resources (disk, multiple cpu-cores, etc.).

This approach can also be used to execute multiple SQL statements in parallel.

A purely SQL engine related parallelisation solution takes advantage of minimized complexity and has no ‘external’ components like SSIS involved, Furthermore it’s the best performing solution regarding task splitting and synchronization, as it hasn’t potential connection and communication overhead. The overall performance gain thru parallelisation with SQL Parallel Boost is up to 10 !

In case you don’t wan’t to rebuild your own solution, SQL Parallel Boost provides a self-contained pure T-SQL based solution, which can be easily embedded in existing applications and ETL process tasks.

__
I’m a bit confuse about the user define Function checkThreads() and “If errors.Count > 0 Then …”. I am just using one database, how can i change the flow of the code that only uses one database and return a result set ?

I’m a bit confuse about the user define Function checkThreads() and “If errors.Count > 0 Then …”. I am just using one database, how can i change the flow of the code that only uses one database and return a result set ?

the code originally uses multiple database, i’m concerned about “Public Shared Function checkThreads” because i think it is design to handle multiple database and also the “If errors.Count” statement . It slows down the execution of query even though only small data is being queried. About the Execute SQL class are you referring to Class ExecSql? I’ve already included in the above code .