9. Pushing multiple tables multi threading

Pushing multiple tables

Using BETL

exec betl.dbo.push '[AdventureWorks2017].[Production].%'

Using TSQL

declare 
@t as varchar(255) 
, @sql as varchar(4000) 

declare c cursor for
  select quotename(s.name ) + '.'+ quotename(t.name) 
  from sys.tables t
  inner join sys.schemas s on t.schema_id =s.schema_id
  where s.name = 'Production'

open c 
fetch next from c into @t
while @@FETCH_STATUS=0
begin 
  set @sql = 'exec betl.dbo.push ''' + @t + ''' '
  print @sql 
  exec (@sql) 

  fetch next from c into @t
end 
 close c
 deallocate c

Multi threading

Betl uses SQL agent jobs to implement multi threading. This way you can execute several big transfers in paralel. These jobs are prefixed with 'betl_proc' and categorized under betl job category. When a job / process / betl push is finished it will delete itself (step 2 of the job). There is also a cleaning mechanism to cleanup aborted jobs or jobs with errors.

This is implemented in the procedure exec_sql. When the parameter async is set to 1 it will run multi-threaded else sequential. Currently only the procedure push_all uses multi-threading.

Configuration of multi-threading using properties.

• proc_max_cnt. How many concurrent processes / jobs. default 4
• proc_max_wait_time_min. How long should we wait for a proc to finish when proc_max_cnt is reached. default 10 minutes. in other words. When proc_max_cnt processes are running simultaneously. How long will it take for a slot to come free.
• wait polling interval. default 2 sec. How long till we check again. range: 1-59. do not set this too low because every polling time a query is executed on msdb.sys.jobs.
• proc_dead_time_sec. delete jobs that are created more than @proc_dead_time_sec ago and are not running. warning: do not set this too low or else new jobs that are being setup are deleted as well. default: 60 sec.