query cancellation


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??


more ▼

asked May 02, 2012 at 02:20 PM in Default

avatar image

81 3 4 6

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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?

more ▼

answered May 02, 2012 at 03:13 PM

avatar image

ThomasRushton ♦♦
42.3k 20 57 53

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, 2012 at 03:22 PM Tauseef_jan
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 02, 2012 at 04:34 PM

avatar image

15.6k 22 55 38

  • for SSIS, although it does seem overkill for a 3x4 spreadsheet...

May 03, 2012 at 08:46 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 02, 2012 at 02:20 PM

Seen: 652 times

Last Updated: May 03, 2012 at 08:46 AM

Copyright 2018 Redgate Software. Privacy Policy