1
I’m doing a c# program that basically queries (large) a Mysql database. When I run the program via Visual Studio, everything goes perfectly. But when I do it by the executable, when the button that performs the queries and treats them is pressed, the application stops responding. Even locking, after a while the program presents the result. However, I would like it to monitor progress via progress bar.
As I am only a "programmer", I program everything in a simple way, using little of the most complex features of the language. Therefore, I would like to know, in general, what are the main causes of this difference in performance/interaction between the Debug mode and the release mode. Below the code used for the query.
MySqlCommand command = new MySqlCommand(sql, con);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
for (int n = 1; n <= numVar; n++)
{
if (!Convert.IsDBNull(reader.GetValue(n-1)))
{
resultados[n - 1].Add(Convert.ToDouble(reader.GetValue(n - 1)));
}
else
{
resultados[n - 1].Add(0);
}
}
}
progressBar1.PerformStep();
And the queries used are usually of that kind:
SELECT
ROUND(SUM(HT_MA4_SBX_SEG.VL_M4000251), 2),
ROUND(SUM(HT_MA4_SBX_SEG.VL_M4000258), 2),
ROUND(SUM(HT_MA4_SBX_SEG.VL_M4000265), 2),
ROUND(SUM(HT_MA4_SBX_SEG.VL_M4000272), 2),
ROUND(SUM(HT_MA4_SBX_SEG.VL_M4000279), 2),
ROUND(SUM(HT_MA4_SBX_SEG.VL_M4000286), 2),
ROUND(SUM(HT_MA4_SBX_SEG.VL_M4000293), 2),
ROUND(SUM(HT_MA4_SBX_SEG.VL_M4000300), 2),
..
ROUND(SUM(HT_MA4_SBX_SEG.VL_M4000398), 2)
FROM HT_MA4_SBX_SEG WHERE HT_MA4_SBX_SEG.TS_SAMPLETM BETWEEN STR_TO_DATE('13/07/2015', '%d/%m/%Y') AND STR_TO_DATE('14/07/2015', '%d/%m/%Y')
The tables used have approximately 5,300,000 data lines.
Try to learn about async await, the problem is that you run all the code on the interface thread, then whenever you have something time consuming, it will freeze.
– Alexandre Borela
It makes total sense. I’ll take a look. Even after running the time-consuming part, the progress bar, for example, doesn’t update. Is that normal? Thanks!
– Rafael
This is probably related to the size of the data. It’s not supposed to slow down just because it’s release.
– Maniero
Got it. Running one of the queries directly from Mysql Workbench it takes about 30s to return the result. At the moment, the application I’m developing has to perform similar codes about 60 times.
– Rafael
Yes, Workbench suffers from a different problem, it does not virtualize the interface or if it virtualizes it is very bad, then whenever it has a lot of data it chokes. About the progress bar not updating sometimes is normal, because you have locked the entire rendering of the screen and the winforms updates using the Pump message, it may be that you are missing the update message in the chokes.
– Alexandre Borela
It’s been a few years since I’ve dealt with winforms, so I don’t remember if you have virtualization on the original datagrid, so I warn you, trying to show 1 million items without virtualization will lock the interface even if the processing is in another thread.
– Alexandre Borela
Quiet. The queries, in fact, are the kind that return only one value line. Would it be possible, for example, to use the async feature to query Mysql to make the interface less plastered? Thank you very much.
– Rafael
If you return a line and are screwing you are in serious trouble.
– Maniero
I added the main parts of the code for colleagues to evaluate if there is any bad method of data acquisition via app.
– Rafael
Yes, async will allow the interface to continue working, the progress bar animations and everything else will not crash. If possible create caches of sums obtained, reminds me an accounting system that I programmed, queries of this size are very heavy even.
– Alexandre Borela