CODEWITHSUNDEEP
sqlexcelsql-server-2012export-to-excel
sirish59
sirish59

Reputation: 53

Querying large SQL Server data sets from Excel

I have a very large SQL Server data set (120 million+ rows) that I want to analyze using Excel. I have heard you can query external data sources using Excel, which will not be subject to the 1.048 million row maximum if downloaded into a Pivot Table.

I tried to use the "External Data Source" option in Excel and wrote a SQL query to narrow down the data set to 2 million records to be safe (I hear you can go up to 4 million records using this method). All seems to go as planned until the data begins to download, but then it freezes my Excel up and doesn't work.

Does anyone know if I'm doing something wrong or if there is another way to analyze this data?

Thanks!

Upvotes: 1

Views: 2213

Answers (1)

Twelfth
Twelfth

Reputation: 7180

It's not frozen...just slow. Leave your computer running and go home for the night, it'll be unfrozen by the morning. I've seen excel freeze over 15 minutes, and it was simply trying to grab 32'000 records (28 columns wide).

Solution is to aggregate further from SQL...any more datapoints than 50k in excel is bulky and takes its sweet time. Unless you are literally plotting 2 million points on a chart, you should be able to reduce the number of rows coming into excel (Do not aggregate anything in excel if you have to)

Add:

Do you have access to MSSQL activity monitor on that database? You should be able to see the excel query running through there...it's the best way to confirm that it's not frozen.

Second add:

You might also be hitting table locking issues...if a table is locked by another process, excel waits until it's available (which looks like excel freezes). Check activity monitor for locking problems. Might also note that during the 'freeze' excel could be locking tables and not allowing other users to hit them.

Upvotes: 1

Related Questions