I tried to execute the below query
SELECT FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=E:\QUERY_EXCEL.xlsx;HDR=YES', 'SELECT FROM [Sheet1$]')
it takes ages to complete and ages to cancel.
what am i doing wrong??
asked May 02, 2012 at 02:20 PM in Default
Is E:\\ a local drive, or a network drive?
How big is the spreadsheet?
Do you need to select *, or can you limit it to specific columns?
The first thing I would do is to rewrite the query to only retrieve the columns that I actually need. Even if I need all the columns, I would be inclined to list them explicitly.
While the transaction is running, can you see any locks?
answered May 02, 2012 at 03:13 PM
Generally, I have not had performance problems using openrowset with excel files as long as the excel file was on a local drive. However, if needed, you could use SSIS (or one of several other options) to bring the excel data into a table and then drop the table when you were finished. Bringing it in as a table has the advantage of permitting you to index it which can be worthwhile if you will be using complicated conditions.
To second Thomas Rushton, limiting the columns will also help, possibly very substantially.
answered May 02, 2012 at 04:34 PM