Hi! I'm building my first tools via PowerQuery and hoping to pick the brain of some more seasoned users. I'm actually running PowerQuery from Excel platform, NOT Power BI, but hoping it doesn't matter all that much.
I have a SQL-based production database and a scheduled process that dumps data into intermediate Excel Workbook (.xlsx), containing 2 tables. One for item metadata (1 record per item - approx 5K rows and 4 columns) and the other for item historical data (multiple records per item - approx 50K rows and 8 columns), making up for about 3.5MB Excel file.
This data is fetched by users in Excel workbook over PowerQuery from intermediate Excel workbook where Excel cell value is used as a filter for what item data is fetched. There are very limited transformations set up in the query and Fast Load is switched on. Both table queries are set up individually and not merged or appended - but launched in sequence using VBA. Both the "user form" Excel workbook + intermediate Excel database are saved on network drives.
The query works, however it's one of the slowest things I have built as it takes about 20 seconds to complete. Not unusable, but I just can't help but to think if some other setup makes the system run clearly quicker (I can't connect user form directly to production SQL database - hence the intermediate data loading). So, instead of throwing darts into darkness and doing random experimentation, maybe you guys have some suggestions here?
Should I try to use CSV-format as the intermediate database, instead of XLSX (or some other format that I can export from Excel such as XML)? Should I try to build this intermediate database in Access or somehow load into separate SQL-database (no experience with any of the two)? Or should I just be satisfied with the 20-sec query times I'm currently having? Thanks a lot of any ideas!
