Find solution
Why my SQLite3 query takes time?

Calculations in Excel are really slow for 1million plus rows. How can I speed things up so users don't have to wait for 10 minutes every time?

  • I connected Excel to ODBC SQL Server Database and Excel pivot table is getting its data from an SQL server VIEW. There are a total of about 1 million rows from the QUERY. From the data, I created new columns in excel to perform calculations. Every time I choose a criteria from the drop down in Pivot Table, the formulas would need to be recalculated and it takes about 10 minutes for the calculation to complete. Is there a more efficient way to do this, because I cant make a user sit and wait for 10 mins to get the data they need.

  • Answer:

    Can you move the calculations to the server? Most PivotTable operations represent a "Group By" in SQL; to the extent you can return only the unique combination of groups with your summarized metrics, you can then do your PivotTable calculations. Note that this implies you can't do calculations like conversion rates directly, you need to be sure that all operations are commutative. So you can return the sum of a metric by group (the numerator and denominator), then define the division of those columns in the PivotTable. By doing so, this ensures your conversion rate is correct, irrespective of how your users group the data. After that, using a better reporting tool is the next step. Returning a million records to the end user generally means some intermediate data preparation steps are being left out.

Randy Zwitch at Quora Visit the source

Was this solution helpful to you?

Other answers

Have you tried PowerPivot? Its a free Microsoft Excel add-in that works really well with large datasets in Excel.  I have worked with upwards of 30 million rows of data in PowerPivot without any issues.  The link is below. http://www.microsoft.com/en-us/bi/powerpivot.aspx You should be able to gather the data directly from SQL Server into PowerPivot, create your new columns and Pivot tables from this dataset.  You can then share this file with your users.  Additionally, it will compress your data, so you end up with smaller file size and its geared towards enabling faster analysis.  I definitely recommend it. Hope that helps

Suhel Mangera

also you can paste special you formulae to values to reduce pressure on processor...

Harshal Gajare

For slow Pivot data Retrieval (due to large data set) try http://www.microsoft.com/en-us/bi/powerpivot.aspx as highlighted by Its a free Microsoft Excel add-in that works really well with large data sets in Excel and can work the data set like yourin PowerPivot without any issues If you are still facing issues you can try the following tips( to increase overall spreadsheet speed) 1) Change  the formula calculation option to Manual to disable automatic calculations, press F9 to calculate manually only when required 2) Turn screen updating Off  before changing criteria Application.ScreenUpdating = False and set it back to Application.ScreenUpdating = True after its done 3) Dump the formatting whenever you can, excessive use of conditional formatting can slow down your sheet 4) Reduce the number volatile functions in your sheet. Some of the volatile functions are NOW(), TODAY(), OFFSET(), CELL(),INDIRECT(), ROWS(), COLUMNS() 5)Whenever possible sort your data appropriately and use TRUE for VLOOKUP and HLOOKUP. (i.e avoid using exact match because in that way excel has to look in all the range) 6)If all else fails increase RAM and opt for higher speed processor

Kamal Subhani

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.