OpenRowSet, OpenQuery, OpenDataSource - which is better in terms of performance

Question

This can be a debatable answer, but I'm looking for the case where a local Excel file needs to be exported to a local SQL Server 2008' table.

Has anyone ever had the chance to check execution time to compare OpenRowSet/OpenQuery/OpenDataSource for a very large file import in SQL Server 2008?

I'm able to use any of the 3 options, and the query can be executed from anywhere. However, the data source (Excel) is in the same server as the SQL Server.

Any pointers would be helpful.

Hohenstaufen

Answer 1

It's been nearly 12 years since this question was asked and it's viewed 2k times, which means people are interested in knowing the answer... even today, I wanted to get to this answer...

So I created an excel spreadsheet with 100k rows and registered it as a linked server then compared the average result of the duration of four different type of open queries to this data. Here are the results:

There's a bit of setup to do that requires administrator privileges on the SQL server, registering an OLEDB provider, and acquiring permissions on the file.

This test was run on a 2016 version of SQL Server Enterprise (64-bit).

Each equation was run through 12 cycles and averaged and rounded.

1. Test for OpenRowset:

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'Excel 12.0 Xml;Database=C:\temp\sample100k.xlsx;',
                Sample100k$);

CPU Time: 4705 ms
Elapsed Time: 7894 ms

2. Test for OpenDatasource

SELECT *
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
                    'Data Source=C:\temp\sample100k.xlsx;Extended Properties=EXCEL 12.0')
                    ...[Sample100k$];

CPU Time: 4794 ms
Elapsed Time: 7918 ms

3. Test for a direct query on a Linked Server

/* Configuration. Only run once for setting up the linked server */
/* Note that this step needs to take place for the third and fourth tests */

EXEC sys.sp_addlinkedserver @server = N'SAMPLE100K',
                            @srvproduct = N'Excel',
                            @provider = N'Microsoft.ACE.OLEDB.12.0',
                            @datasrc = N'C:\temp\sample100k.xlsx',
                            @provstr = N'Excel 12.0'

SELECT * FROM [SAMPLE100K]...[sample100k$];

CPU Time: 4919 ms
Elapsed Time: 7934 ms

4. Test for OpenQuery on a Linked Server

/* Assume linked server has been registered, as mentioned in the third test */

SELECT * FROM OPENQUERY(SAMPLE100K, 'SELECT * FROM [sample100k$]');

CPU Time: 3569 ms
Elapsed Time: 5643 ms

I did not expect these results; it appears that test 4 (SELECT * FROM OPENQUERY...) performed 20% faster than the average and over 25% faster than the linked server query in test 3 (SELECT * FROM SAMPLE100K...)

I'll let the OP and other readers determine whether or not they should really use any of these methods compared to doing a table import, a BCP, an SSIS ETL package or some other method.

I'm simply providing an answer to the question for stack overflow visitors who visit this page every other day.

Camus