Excel Query speeds/running queries in parallel instead of sequentially

[codeliftsleep]

So I have created multiple connections that query an SQL Server and have 2 running when I open the file in the background and then I call the other 4 in VBA since need to identify the user and pass it to the stored procedure it is calling.

The issue is that it is slow...really slow. From opening the file to finishing the queries and having all the pivot tables refresh it is taking about 5 minutes. The total query times only takes about 1.5 minutes or less so I am not sure where the time bottleneck is as far as having the file ready to use and why its taking so long for the other processes to complete. I am using Excel 2016.

So my question is, is there a way to have these queries run in parallel at the same time instead of sequentially i VBA to cut down on times?

Are there any tips that can lessen the amount of time this is taking to open and be ready for users?

 

[bobsan42]

VBA does not natively support multi-threading afaik. But i'm no expert programmer.
There are solutions on the web though.

However i would suggest first with investigating - compare the times it takes for each query or operation to complete. Use vba.timer and debug.print the time differences (=end - start) at selected lines in your code. See if anything takes too long.

 

[codeliftsleep]

VBA does not natively support multi-threading afaik. But i'm no expert programmer.
There are solutions on the web though.

However i would suggest first with investigating - compare the times it takes for each query or operation to complete. Use vba.timer and debug.print the time differences (=end - start) at selected lines in your code. See if anything takes too long.

Yeah I know..believe me, this was not my first choice...I was basically forced into this as a solution because my company is scared to let me expand the application I built in C#/WPF because there is nobody else who can maintain it and it is considered a huge "risk" as such.

Excel is such a crap solution when it comes to this type of stuff because of the myriad of errors that are difficult to track down and that don't make a lot of sense as to why one user would experience them but not another.
The only reason this is even occurring Excel doesn't read data from SQL CAST as a date as an actual Date. So I have to use a formula to change a date/time into a date in excel, and that is where the issue is currently. I would have been done with this entire project at least 4 or 5 days ago if I designed it as an application but in Excel you can spend all day trying to figure out why something doesn't work properly. SMH.