Excel Query speeds/running queries in parallel instead of sequentially

codeliftsleep

Board Regular
Joined
Apr 14, 2017
Messages
103
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?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top