2

enter image description here

I am using TABLE OUTPUT component in pentaho tool.After i checked "use batch update for insert" it should run as one single insert statement for over all records.

where as insert is happening like one by one insert script which i found using profiler as below,

insert into #temp values('a')
insert into #temp values('b')
insert into #temp values('c')

But "use batch update for insert" should not behave like each insert query. Can anyone give me suggestion regarding this issue

Expectation :

insert into #temp values('a'),('b'),('c')
yuvi
  • 564
  • 5
  • 12
  • Correct me if I'm wrong, but I believe the single `INSERT` statement with multiple `VALUES` tuples is syntactic-sugar for multiple `INSERT` statements - i.e. there's no difference between the two. I believe you need to use the `OPENROWSET` hint to actually get a performance benefit. – Dai Oct 13 '16 at 09:11
  • @dai i am specifically asking for pentaho tool with sql server.i know about usage of OPENROWSET in sql server.how this can be achieved specific to tool – yuvi Oct 13 '16 at 09:34
  • 1
    @Dai, the Wiki (http://wiki.pentaho.com/display/EAI/Table+Output) suggests the `use batch insert` option simply encapsulates multiple inserts in a single batch to avoid round trips. – Dan Guzman Oct 13 '16 at 10:19
  • @DanGuzman ya exactly.can u pl say how this option will behave if we check and unchecked it as well on the above question which insert behavior it will follow if we check and unchecked. – yuvi Oct 13 '16 at 10:27
  • @Dai, my expectation is that each insert will be sent in a individual batch when the option is unchecked. When checked, and the requirements listed in the documentation are met, a single batch will be sent containing many insert statements. Importantly, the commit size must be greater than zero for batch inserts. This should also improve performance. – Dan Guzman Oct 13 '16 at 10:36
  • Thanks @DanGuzman.i got ur point – yuvi Oct 13 '16 at 10:50
  • @Dai this is not true. There is an implicit transaction associated with each insert and the table-valued constructor can be used to avoid this penalty. See http://stackoverflow.com/a/37501560/1186165 for more info and test for yourself. – Kittoes0124 Oct 28 '16 at 17:38

0 Answers0