OPENROWSET or OPENDATASOURCE - Situation preferences / Performance differences?

• sqldriver

  SSChampion

  Points: 10440

  More actions

  July 17, 2013 at 10:17 am

  #277610

  Hi,

  I'm not asking what the difference between them is, because I think I get it after some time spent reading. I'm looking more for what situation people would use one over the other, and if/when there are any real performance differences to note. Is one better than the other for Excel files? I would appreciate any thoughts that aren't links to Google or BOL.

  Thanks

• sqldriver

  SSChampion

  Points: 10440

  More actions

  July 18, 2013 at 2:29 pm

  #1633795

  Due to popular demand, I decided to test each of these queries. I used a .xlsx file with 215,337 rows, and 15 columns.

  SELECT * INTO dbo.ImportTest

  FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

  'Database=\\plaus42\Sample\SampleRepository\j3688810_Cycle 13.xlsx;

  Excel 12.0 XML;HDR=YES;IMEX=1', [sample$])

  Ran for 2:24

  SELECT * INTO dbo.ImportTest

  FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',

  'Data Source=\\plaus42\Sample\SampleRepository\j3688810_Cycle 13.xlsx;

  Extended Properties="Excel 12.0 XML;HDR=YES;IMEX=1"')...[sample$]

  Ran for 2:20

  The results mostly make me think my server kind of stinks :angry: