•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Excel Query vs data connection, Query much slower
Our take
In the world of data management, understanding the difference between Excel's connection and query features is crucial for optimizing performance. While fixed connections can import large datasets—like 66,000 rows from Snowflake in about 10 seconds—queries may introduce significant delays, potentially extending refresh times to several minutes. This discrepancy often stems from how queries handle data retrieval and processing. If you're experiencing slower refresh rates with queries, exploring the underlying configurations and optimizing your approach could unlock a more efficient, user-friendly data experience.
I've been working on overhauling some reporting and really struggling to get a good/fast/easy to maintain setup. Currently I have a workbook with a fixed "Connection" (under queries & connections) to snowflake databases that can import about 66K rows of data in roughly 10 seconds.
Cannot use the connection in power query or data model as I need so I tried recreating it as a query, copy/pasted the connection string and command text from the connection into a Query so that I can more freely use it, but it goes from 10 seconds to refresh to 3-4 minutes to load the exact same 66K rows of data.
Is there something I'm missing?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How Can I Use Connection-Only Quieries?We are imitatng an ERP system using Power Query, and the accreditation of queries on top of queries is starting to impact performance. I think I can speed it up by not loading ever query into the workbook. Is there a way to reference data via formula (I.e XLOOKUP) in a worksheet without loading the quiered data into the workbook? submitted by /u/SteelishBread [link] [comments]
- Power Query Refresh TimeI created an Excel file with a Power Query product database. It takes item-level data (sizes, colors, logos, etc.) from an Item Master, expands it into all SKU combinations, and builds out a final product database with pricing and item info. It also pulls in a predetermined product codes from a separate tab with 300,000 rows. When I refresh the file (both at home and at work), it takes about 20 seconds with 50 rows. I had a couple coworkers try the exact same file, and it takes about 2 minutes for them to refresh. It is the same file, same data, same network, and similar computers. I can’t figure out why it’s consistently faster for me than everyone else. Any ideas of what could be causing this? submitted by /u/Presto1985 [link] [comments]
- How Do I Speed Up My PowerQueryMy powerquery is so slow even though I tried to make it faster. The data In working with is just the raw data and I’m tasked with converting everything into something else through custom columns. These custom columns use legends by merges and are nested if statements. The excel equivalent would be vlookups with nested ifs. I made a library inside Powerquery that houses that logic. I also branched the query into 2, one where the columns aren’t used in the custom column creation and another where it is. I know this decrease the amount of data since the columns went from 20s to 7-8. The issue is the data doesn’t load. And I didn’t even remerge the constant with the custom columns throigh an index column, then append everything. I tried Buffering the alleged which didn’t do a thing submitted by /u/Lucky-Tea-2370 [link] [comments]
- Excel - Get Data - Access stuck on "Loading Data..."I have a workbook with multiple queries that pull data from Access queries and load to pivot tables. Most of them update/refresh fine, but several of them just spin and get stuck on "Loading data..." The query results are only like 10-20 rows for each of these that get stuck. The power query does no filtering/sorting or anything, I have no VBA doing anything, no macros, or conditional formatting. I'm clueless as to why some are fine and these others just get stuck. Help!!! TIA. submitted by /u/Good_Struggle_7471 [link] [comments]
Tagged with
#Excel alternatives for data analysis#big data management in spreadsheets#generative AI for data analysis#conversational data analysis#rows.com#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#Excel compatibility#Excel alternatives#financial modeling with spreadsheets#Query#Data Connection#Excel#Power Query#Snowflake