Slow App

[patrickstrijdonck]

Hi all,

When Form2 is loading, a background worker is doing the following:

// TODO: This line of code loads data into the 'iT4DataSet.tblGebruikers' table. You can move, or remove it, as needed.
            this.tblGebruikersTableAdapter.Fill(this.iT4DataSet.tblGebruikers);
            // TODO: This line of code loads data into the 'iT4DataSet.tblStores' table. You can move, or remove it, as needed.
            this.tblStoresTableAdapter.Fill(this.iT4DataSet.tblStores);
            // TODO: This line of code loads data into the 'iT4STOCKDataSet.qryOpenOrders' table. You can move, or remove it, as needed.
            this.qryOpenOrdersTableAdapter.Fill(this.iT4STOCKDataSet.qryOpenOrders);
            // TODO: This line of code loads data into the 'iT4DataSet.qryHomeTasks' table. You can move, or remove it, as needed.
            this.qryHomeTasksTableAdapter.Fill(this.iT4DataSet.qryHomeTasks);
            // TODO: This line of code loads data into the 'iT4DataSet.tblTasks' table. You can move, or remove it, as needed.
            this.tblTasksTableAdapter.Fill(this.iT4DataSet.tblTasks);
            // TODO: This line of code loads data into the 'iT4DataSet1.OutstandingCalls' table. You can move, or remove it, as needed.
            this.outstandingCallsTableAdapter1.Fill(this.iT4DataSet1.OutstandingCalls);
            // TODO: This line of code loads data into the 'iT4STOCKDataSet.tblLeveranciers' table. You can move, or remove it, as needed.
            this.tblLeveranciersTableAdapter.Fill(this.iT4STOCKDataSet.tblLeveranciers);


            // Getting Row Count for IT Helpdesk Alert!

            string selectCount;
            string cmdCount = Properties.Settings.Default.IT4ConnectionString1;
            selectCount = "SELECT Count(*) FROM OutstandingCalls";
            OleDbConnection connCount = new OleDbConnection(cmdCount);
            OleDbCommand comCount = new OleDbCommand(selectCount, connCount);
            connCount.Open();
            Int32 count = Convert.ToInt32(comCount.ExecuteScalar());
            this.Invoke((MethodInvoker)delegate
            {
                textBox7.Text = Convert.ToString(count);
            });
            //textBox7.Text = Convert.ToString(count);
            if (count >= 0)
            {
                this.Invoke((MethodInvoker)delegate
            {
                textBox10.Visible = true;
                textBox11.Visible = false;
                textBox12.Visible = false;
                textBox13.Visible = false;
                textBox14.Visible = false;
                });
            }
            if (count >= 10)
            {
                this.Invoke((MethodInvoker)delegate
            {
                textBox10.Visible = false;
                textBox11.Visible = true;
                textBox12.Visible = false;
                textBox13.Visible = false;
                textBox14.Visible = false;
                });
            }
            if (count >= 15)
            {
                this.Invoke((MethodInvoker)delegate
            {
                textBox10.Visible = false;
                textBox11.Visible = false;
                textBox12.Visible = true;
                textBox13.Visible = false;
                textBox14.Visible = false;
                });
            }
            if (count >= 25)
            {
                this.Invoke((MethodInvoker)delegate
            {
                textBox10.Visible = false;
                textBox11.Visible = false;
                textBox12.Visible = false;
                textBox13.Visible = true;
                textBox14.Visible = false;
                });
            }
            if (count >= 40)
            {
                this.Invoke((MethodInvoker)delegate
                {
                textBox10.Visible = false;
                textBox11.Visible = false;
                textBox12.Visible = false;
                textBox13.Visible = false;
                textBox14.Visible = true;
                });
            }

            connCount.Close();

            // Get Row Count of open orders
            string selectCount2;
            string cmdCount2 = Properties.Settings.Default.IT4STOCKConnectionString;
            selectCount2 = "SELECT Count(*) FROM qryOpenOrders";
            OleDbConnection connCount2 = new OleDbConnection(cmdCount2);
            OleDbCommand comCount2 = new OleDbCommand(selectCount2, connCount2);
            connCount2.Open();
            Int32 count2 = Convert.ToInt32(comCount2.ExecuteScalar());
               this.Invoke((MethodInvoker)delegate
                {
            textBox9.Text = Convert.ToString(count2);
                });
            connCount2.Close();

            // Get Row Count of open tasks
            string selectCount3;
            string cmdCount3 = Properties.Settings.Default.IT4ConnectionString1;
            selectCount3 = "SELECT Count(*) FROM qryOpenTasks";
            OleDbConnection connCount3 = new OleDbConnection(cmdCount3);
            OleDbCommand comCount3 = new OleDbCommand(selectCount3, connCount3);
            connCount3.Open();
            Int32 count3 = Convert.ToInt32(comCount3.ExecuteScalar());
               this.Invoke((MethodInvoker)delegate
                {
            textBox8.Text = Convert.ToString(count3);
                });
            connCount3.Close();

Before, it wasnt even in a background worker and that made my app loading / hanging for about 2 minutes.

now that this is in a background service, the GUI is more responding but the backgroundworker is busy for about 1 / 2 minutes to get this data.

The database is located in the same network, and there are no speed issues in our network.

Can someone take a look at the code and tell me if there is something which can be improved?

Thanks alot for the help.

 

[patrickstrijdonck]

1000 records is a lot of data to present to the user at once. Paging 20-30 records at a time may be a better option. this would reduce the amount of data traversing the wire and the amount of rows bound to the UI grid. Even if you enable paging at the UI that data is still traversing the wire.with batching I mean instead of issuing 3 separate commands which will issue 3 remote calls, combine them into a single remote call. I use a 3rd party data access framework (NHibernate) which makes batching commands transparent to my code. under the hood I believe it works something like this.I need to issue 3 queries. lets say: get users, get tickets, get statuses. typically you would create a connection and then issue 3 unique commands with that connection: CODEcommand1.CommandText = "select [fields] from user where [whatever]";command2.CommandText = "select [fields] from tickets where [whatever]";command3.CommandText = "select [fields] from statuses where [whatever]";To batch them together I would issue a single command with 3 sql statements.CODEcommand1.CommandText = @"select [fields] from user where [whatever]; select [fields] from tickets where [whatever]; select [fields] from statuses where [whatever];";I would then need to iterate of each recordset that was returned from the command. if i was using datatables it would look like thisCODEvar users = new DataTable();var tickets = new DataTable();var statuses = new DataTable();using(var reader = command1.ExecuteReader()){ users.Load(reader); reader.NextResult(); tickets.Load(reader); reader.NextResult(); statuses.Load(reader);}we have now reduced the number of remote calls from 3 to 1.

Jason Meckley has posted this in one of my previous treads which is actual something i can try to do,

Combine the 3 Get Row Count SQL queries into 1 SQL command.
I'll post the results later.

 

[patrickstrijdonck]

Ok, Ive combined 2 SQL commands into 1, the other command is getting the data from another datasource.

            string command1;
            string cmdCount3 = Properties.Settings.Default.IT4ConnectionString1;
            command1 = @"SELECT Count(*) FROM OutstandingCalls
	                SELECT Count(*) FROM qryOpenTasks";
            OleDbConnection connCount3 = new OleDbConnection(cmdCount3);
            OleDbCommand comCount3 = new OleDbCommand(command1, connCount3);

var OutstandingCalls = new DataTable();
var qryOpenTasks = new DataTable();
connCount3.Open();

using (var reader = comCount3.ExecuteReader())
{
	OutstandingCalls.Load(reader);
                this.Invoke((MethodInvoker)delegate
                {
                textBox7.Text = reader.GetString(0);
                });
	reader.NextResult();
	qryOpenTasks.Load(reader);
                this.Invoke((MethodInvoker)delegate
                {
                textBox8.Text = reader.GetString(0);
                });
}
connCount3.Close();

Somehow, the program seems hanging at the point when it should do the Invoke and change the text in textBox7 which it isnt doing.

when trying to click on the program no matter where you click, windows says, Not Responding.

Anything wrong here?

 

[PGO01]

There may also be ways to improve the time taken to execute the SQL:

SELECT COUNT(*) FROM OutstandingCalls

For example you could add a clause similar to:

SELECT * FROM OutstandingCalls WHERE DealtWith = 0

Although I expect OutstandingCalls is a view that filters the results already. If that is true you can ignore the above.

You could always save the "SELECT COUNT(*) FROM OutstandingCalls" to disk and run it through the Database Tuning Advisor (from Tools menu in SSMS I think).

This will tell you whether stats/indexes could be created to improve things and also gives you the commands to do the necessary work.

 

[patrickstrijdonck]

Its a query that already exists in the msaccess database.
and yes its already filtering the results. it just needs to give the count of that query.

im not using SQL Server form Microsoft but MS Access.
when trying to run that in MS Access and let Access advise the performance of this query, it has no advise for this one.

See my last post above, ive managed to combine 2 SQL commands, but the program is hanging..... dont know why

 

[jmeckley]

Before, it wasnt even in a background worker and that made my app loading / hanging for about 2 minutes.

now that this is in a background service, the GUI is more responding but the backgroundworker is busy for about 1 / 2 minutes to get this data.

does this surprise you? All you did was move where the work is done, you haven't changed what work is done.

I see some issues with the code some deal with preformance, others do not.
1. You are using MS Access. Access is a limited database that doesn't have the same performance options as MS SQL Server, MS Sql Express, Oracle, etc.
2. poor variable names. TextBox1 says nothing about what you are displaying. Having a meaningful name like CommissionRate or UsersFullName is much more descriptive
3. You can better utilize database connections/commands to reduce remote calls.
4. Have you run these queries in MS Access directly to see how long then take to execute? The query "SELECT Count(*) FROM qryOpenOrders" looks like you would be quering an Access query. It may be this query is the bottleneck.
5. you are currently using ISynchronizeInvoke to update controls on the form. I have heard this can cause preformance issues when updating large portions of the UI. I don't have any concrete evidence supporting this though. You may want to consider using SynchronizationContext instead.
6. Simply for readability I would refactor the massive if block

HideAllControls();
if(condition 1)
{
  TextBox1.Visible = true;
}
if(condition 2)
{
  TextBox2.Visible = true;
}
if(condition 3)
{
  TextBox3.Visible = true;
}
...
private void HideAllControls()
{
   TextBox1.Visible = false;
   TextBox2.Visible = false;
   TextBox3.Visible = false;
   TextBox4.Visible = false;
   TextBox5.Visible = false;
}

I have written 2 FAQ's which may help in your scenario. Both are in my signature below. the database connection management FAQ is built in a Web environment, but that can be changed for a desktop environment easily enough. The second is my research of ISynchronizeInvoke and SynchronizationContext which deals with pushing work off to background threads.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259