Multithreading

When a step (that executes a procedure) is evaluated, Bizview check if the procedure takes CLIENT as a parameter. In this case, the step will be looped with all clients the job was ordered with.

To check If the step takes client as a parameter, you need to name the client parameter in one of the following ways:

• @client
• @client_id
• @p_client_id

The test is NOT case sensitive.

At the opposite, if the procedure doesn't have any of the parameters above, the step will NOT loop all clients and will only be executed once.

This topic contains the following sections:

• New feature: PASS_ALL_CLIENTS
• Split the @client_id-parameter to a Temporary Table
• Split the @client_id-parameter to a @variable
• How to use the Clients in Your Procedure
• Bizview Standard Procedures
• Technical Information

New feature: PASS_ALL_CLIENTS

Let's say you have a step that executes a procedure with @client as parameter but you wish that the job only executes the step once anyway, you can now do that.

The trick is to add a new parameter to your procedure with the name @pass_all_clients tinyint. If this parameter is set to 1 the job skips the loop of clients and only executes the step once.

You can, as with all other parameters, add PASS_ALL_CLIENTS with value 1 to the job parameters to make sure all procedures that have the parameter runs once or you can set it for a specific step.

As a consequence of this, you must take care of the @client in your procedure since you now will have all ordered clients in this parameter ('#'-delimited). So, you'll need to do this in your procedure:

1. Make sure you @client-parameter is big enough to accommodate the submitted clients.
2.  Split the @client so you can use it in the code.

When you split the @client you may want to use two ways, depending how your procedure is used. If you have plain SQL (not dynamic SQL), you may want to put all clients in a temporary table and use it in the SQL. If you use dynamic SQL, you may want to parse the clients to a valid SQL in a @variable.

Split the @client_id-parameter to a Temporary Table

select value as client_id into #clients from [utils].[f_split](@client_id,'#')

 The table #clients will contain all ordered clients.

Split the @client_id-parameter to a @variable

declare @clients nvarchar(max)

select @clients = utils.f_sqlsmart('client_id', @client_id)

The @clients will contain a valid SQL for the ordered clients (e.g. client_id in ('10','20','30').

How to Use the Clients in Your Procedure

Example: Plain SQL

The original SQL:

deletefrom stg.fin_ledgertrans WHERE client_id = @client_id

This SQL should be changed to:

deletefrom stg.fin_ledgertrans WHERE client_id in(select client_id from #clients)

Example: Dynamic SQL

The original SQL:

set @sql ='insert into #client_attributes select client_id, attrib_id, attrib_txt

           from '+ @app_dbname +'.app.client_attributes

           where client_id = '+ app.f_quote(@client_id)

This SQL should be changed to:

set @sql ='insert into #client_attributes select client_id, attrib_id, attrib_txt

           from '+ @app_dbname +'.app.client_attributes

 where '+ @clients    

Bizview Standard Procedures

Following procedures support @pass_all_clients parameter:

fact.p_update_ledgersum

fact.p_update_ledgertrans

Technical Information

The following changes have been made to accomplish a faster generation of jobs.

Processing Service

The processing service has been changed so plugins execute in parallel. Every ordered client is executed in its own thread. The key to execute the plugin in parallel is to set the column loop_clients = 1 in dis.job_steps. For procedures, loop_clients is set to 1 if @pass_all_clients exists as a parameter and it's 1.

Plugins do not have such parameters to apply (even if you can actually add a parameter for a plugin-step, but the job ignore parameters for a plugin).

So, at the moment we set loop_clients = 0 if the plugin is "24SO". To add more plugins to run in parallel, we must ensure the plugin is thread safe.

The loop_clients = 0 is done in dis.p_bvproc_job_step_prepare, both for plugin and for procedures.

24SO Plugin

This plugin is changed so it's thread safe. The only problem with the plugin was the delete that caused deadlock on tables with clustered index. The delete is removed from the plugin and must be done in a separate step. The plugin deleted following tables:

• custstg.[24SO_Accounts]
• custstg.[24SO_Companies
• custstg.[24SO_Departments]
• custstg.[24SO_Persons
• custstg.[24SO_Products]
• custstg.[24SO_Projects]
• custstg.[24SO_Categories]
• custstg.[24SO_CustomerCategories]
• custstg.[24SO_ProductCategories]
• custstg.[24SO_Transactions] (DatastoreLocation)
• custstg.[24SO_Dimensions] (DatastoreLocation)
• custstg.[24SO_Time]         (DatastoreLocation)
• custstg.[24SO_Invoices]   (DatastoreLocation)
• custstg.[24SO_InvoiceRows]  (DatastoreLocation)

Example Procedure that Deletes Data:

alterprocedure cust.p_24SODeleteData

  @client_id nvarchar(max),

  @pass_all_clients tinyint

asbegin

 selectvalueas client_id into #clients from [utils].[f_split](@client_id,'#')

 deletefrom custstg.[24SO_Accounts] where ClientId in(select client_id from #clients)

 deletefrom custstg.[24SO_Companies] where ClientId in(select client_id from #clients)

 deletefrom custstg.[24SO_Departments] where ClientId in(select client_id from #clients)

 deletefrom custstg.[24SO_Persons] where ClientId in(select client_id from #clients)

 deletefrom custstg.[24SO_Products] where ClientId in(select client_id from #clients)

 deletefrom custstg.[24SO_Projects] where ClientId in(select client_id from #clients)

 deletefrom custstg.[24SO_Categories] where ClientId in(select client_id from #clients)

 deletefrom custstg.[24SO_CustomerCategories] where ClientId in(select client_id from #clients)

 deletefrom custstg.[24SO_ProductCategories] where ClientId in(select client_id from #clients)

 deletefrom bizview_vac104_test_ds.custstg.[24SO_Transactions] where ClientId in(select client_id from #clients)

 deletefrom bizview_vac104_test_ds.custstg.[24SO_Dimensions] where ClientId in(select client_id from #clients)

 deletefrom bizview_vac104_test_ds.custstg.[24SO_Time] where ClientId in(select client_id from #clients)

 deletefrom bizview_vac104_test_ds.custstg.[24SO_Invoices] where ClientId in(select client_id from #clients)

 deletefrom bizview_vac104_test_ds.custstg.[24SO_InvoiceRows] where ClientId in(select client_id from #clients)

end

Procedure dis.p_bvproc_exec_step (executes a plugin or report/form)

Since the procedure takes client as parameter the @client_id declaration is changed to nvarchar(max) so a list of clients can be passed if loop_clients = 0.

A variable (@max_threads) is declared in the procedure that can be set (perhaps we will get it from a setting). This is the maximum number of threads the plugin will execute in (e.g. max number of clients simultaneously). If the number is below 1, unlimited amount of threads will be created.

Procedure dis.p_bvproc_job_step_prepare

This procedure adds up all parameter for a procedure. If @pass_all_clients is found with value 1 the step is set to loop_clients = 0.

 Also, the hard-coded test if the plugin is SO24 the loop_clients is set to 0 here.

Procedure dis.p_bvproc_job_steps_execute

The parameter @client for a procedure is only updated with current client if the procedure loops clients. If it not (with the new @pass_all_clients) this procedure sets a list of all clients and pass it to the procedure.

In the same way if it's plugin the procedure pass a list of clients to dis.p_bvproc_exec_step if the plugin not loop clients.

Procedure fact.p_upate_ledgertrans and fact.p_update_ledgersum

These procedures now takes @pass_all_clients and have been modified to support update with a client list.