|
hi, 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?? Tauseef
(comments are locked)
|
|
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? Sorry my mistake. The E: is a local drive. There are just 3 columns and and only 4 rows. i have added the excel sheet as a linked server.
May 02 '12 at 03:22 PM
Tauseef_jan
(comments are locked)
|
|
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. +1 for SSIS, although it does seem overkill for a 3x4 spreadsheet...
May 03 '12 at 08:46 AM
ThomasRushton ♦
(comments are locked)
|
