How to properly and efficiently query Lots of data from SQL Server putting it into specific cells?

Question

Recently I was asked to make a real-time project in Excel which evolves huge amount of data. I have this data stored in SQL Tables and managed to successfully bring it into Excel.

SQL Table on Excel:

Then, according to three first columns (primary keys of the table), I want to get the corresponding value and put it into a cell.

For so, I used the following formula:

=XLOOKUP(Resumo!H$1&Resumo!E$50&Resumo!$B66,  (Table_fixed_income_1[bond])&(Table_fixed_income_1[term])&(Table_fixed_income_1[date]),Table_fixed_income_1[value]/100)

It worked as intended, but a new problem arose: Excel is now very slow. There is about 4500 lines for each of the 4 main columns, resulting in 18k unique cells calculations (resulting about 5-10 minutes). The problem will grow even more considering the data in SQL is a small test table and the prod one is much bigger.

I would like to know if there is a way to make the specified project part faster.

Obs.: Some suggestions I already thought about evolved getting data from the SQL DB directly to the Cells and using different formula, but I do not have an extensive Excel knowledge to embrace it.

Answer 1

Here are some suggestions that might meet your needs:

Use Power Query to connect to your SQL data source, add columns that transform your data, and pivot the results to your output format. That would handle your two-step method (connecting to data source and then looking up data) in a single step i.e., you'd have only your final table).

Talk to your DBA about creating a stored procedure to run a query that performs the transformations. That harnesses the power and speed of the database server, leaving only the need to connect and pivot the data via Power Query. It's possible to include input parameters in Excel that are sent to the stored procedure for processing.

Answer 2

I recently had a similar problem on a project I was working on. Ran into the same problem with Excel calculations becoming bloated to the point of loosing functionality due to the size of the data set I was attempting to pull from SQL. I was also reliant on look up formulas.

I reworked formulas to limit volatility, leaned into pivot tables, trimmed the SQL data set, and was not able to reach a practical solution using Excel, even when converting from a real time SQL connection to a static data set.

I then began testing Power BI which ultimately ended up providing the solution I needed. I'm not vastly experienced with many business intelligence softwares so cannot recommend which might be the best fit for your needs/constraints, but this worked for my needs (Large real time SQL data set) and your problem seems similar.

Hope that's a little helpful.