OpenRowSet - Bulk Load Performance Considerations

• etl2016

  Default port

  Points: 1459

  More actions

  September 19, 2018 at 1:21 pm

  #372885

  Hi,

  I have a scenario where I need to load a table using OpenRowSet - Bulk Load option.   The table is Clustered Indexed. There are no ROWS_PER_BATCH sort of hints currently being used.  The load file contains anywhere between 10 to 15 million records.

  Could you please share your thoughts on: 

  1) How Clustered Index is rebuilt during the span of loading these , say 10 million records?  Is the entire lot of 10 million considered as a single batch and thus Index Rebuilding is done ONLY ONCE?
  The option I am considering is to DROP-LOAD-REBUILD index sequence.  If, in the absence of ROWS_PER_BATCH hint the entire lot of 10 million records is considered a single batch and Index Rebuild is done only once, then, I doubt if my option of DROP-LOAD-REBUILD will make any difference, performance wise.

  2) On the other hand, say if I mention ROWS_PER_BATCH as Ten thousand, will the commit size be Ten thousand and this lot of 10 million is loaded in packets of Ten thousand (and committing them).  In this scenario, will Index on the table be Rebuilt after each packet of Ten thousand committed records are inserted into the table? Will SQL Engine be more efficient if we limit the COMMIT size, because packets of SMALLER sized data are flushed to DB table clearing the Cache, thus reducing the Disk I/O?

  3) How does all this scenario change if I replace Clustered (row based) index with a Column Store Index.  Which parameter combinations of OpenRowSet will be performant?

  thank you