x

query cancellation

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
more ▼

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

Tauseef_jan gravatar image

Tauseef_jan
51 2 2 2

(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

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

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

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

+1 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x986

asked: May 02, 2012 at 02:20 PM

Seen: 550 times

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