3
\$\begingroup\$

The class file below handles the display of information to a WPF window. In its current state it works except for the fast it takes 2 minutes for the window to open because of the SQL query's and every minute when the query's run again it locks up the entire application for the duration of running the query's. I'm very new to coding and this is one of the most complex thing's I've learned to do so far. Any Help in improving performance or learning a new and better way to accomplish this would be wonderful

public partial class CallInformationMainScreen : Window
    {
        public string SkillNumber;
        public string TotalDailyCalls;
        public string TotalDailyLast7Days;
        public string TotalDailyAbandon;

        public CallInformationMainScreen()
        {
            InitializeComponent();

            string value = "92";
            if (Tmp.InputBox("Skill Number", "Enter your skill ID below:", ref value) == System.Windows.Forms.DialogResult.OK)
            {
                SkillNumber = value;
            }

            TotalDailyLast7Days = SQLDataSevenPastCalls();
            TotalDailyCalls = SQLDataTotalCalls();
            TotalDailyAbandon = SQLDataAbandonCalls();            

            RunPeriodicQueryTotalCalls();
            RunPeriodicQueryAbandonCalls();
        }

        private void DisplayNumber_Loaded(object sender, RoutedEventArgs e)
        {
            FeedServiceAgent data = new FeedServiceAgent();

            data.MessageReceived += OnMessageReceived;
            data.Subscribe(SkillNumber, 3);
        }

        public void OnMessageReceived(object sender, MessageReceivedEventArgs e)
        {
            try
            {
                if (e == null)
                    return;

                if (e.CmsData != null)
                {
                    Dispatcher.BeginInvoke(DispatcherPriority.Normal, (Action)(() =>
                    {
                        TotalDailyCallsData.Text = TotalDailyCalls;
                        TotalCallsCompareText.Text = "Last " + DateTime.Today.DayOfWeek.ToString();
                        TotalCallsCompareData.Text = TotalDailyLast7Days;
                        TotalAbandonCallsData.Text = TotalDailyAbandon;
                        AverageCallTimeData.Text = e.CmsData.Skill.TimePerAcdCall.ToString();
                        LongestCallWaitingData.Text = e.CmsData.Skill.OldestCall.ToString();
                        EstimatedWaitTimeData.Text = e.CmsData.Skill.ExpectedWaitTimeMedium.ToString();
                    }));
                }
            }
            catch(Exception)
            {

            }
        }
        public string SQLDataTotalCalls()
        {
            DateTime dte = DateTime.Today;

            string fixedStartDate = String.Format("{0:yyyy-MM-dd " + "05:00:00.000" + "}", dte);
            string fixedEndDate = String.Format("{0:yyyy-MM-dd " + "05:00:00.000" + "}", dte.AddDays(1));
            string sql = "SELECT COUNT(SOURCEID) AS 'MYCOUNT' "
                         + "FROM [OADB].[oadb].[CmsCallHistory] "
                         + "WHERE disposition = 2 and DISPSPLIT in (" + SkillNumber + ") AND SEGSTOP BETWEEN '" +
                         fixedStartDate + "' and '" + fixedEndDate + "'";
            using (SqlConnection connection = new SqlConnection("Data Source=database;Initial Catalog=OADB;User Id=readonly;Password=password"))            

            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                try
                {
                    var dataSet = new DataSet();

                    var dataAdapter = new SqlDataAdapter { SelectCommand = command };

                    dataAdapter.Fill(dataSet);

                    return dataSet.Tables[0].Rows[0]["MYCOUNT"].ToString();

                }
                catch (Exception e)
                {
                    throw new Exception(e.Message, e);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                }
            }
        }
        public string SQLDataSevenPastCalls()
        {
            DateTime dte = DateTime.Today;

            string fixedStartDate = String.Format("{0:yyyy-MM-dd " + "05:00:00.000" + "}", dte.AddDays(-7));
            string fixedEndDate = String.Format("{0:yyyy-MM-dd " + "05:00:00.000" + "}", dte.AddDays(-6));
            string sql = "SELECT COUNT(SOURCEID) AS 'MYCOUNT' "
                         + "FROM [OADB].[oadb].[CmsCallHistory] "
                         + "WHERE disposition = 2 and DISPSPLIT in (" + SkillNumber + ") AND SEGSTOP BETWEEN '" +
                         fixedStartDate + "' and '" + fixedEndDate + "'";
            using (SqlConnection connection = new SqlConnection("Data Source=database;Initial Catalog=OADB;User Id=readonly;Password=password"))

            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                try
                {
                    var dataSet = new DataSet();

                    var dataAdapter = new SqlDataAdapter { SelectCommand = command };

                    dataAdapter.Fill(dataSet);

                    return dataSet.Tables[0].Rows[0]["MYCOUNT"].ToString();

                }
                catch (Exception e)
                {
                    throw new Exception(e.Message, e);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                }
            }
        }
        public string SQLDataAbandonCalls()
        {
            DateTime dte = DateTime.Today;

            string fixedStartDate = String.Format("{0:yyyy-MM-dd " + "05:00:00.000" + "}", dte);
            string fixedEndDate = String.Format("{0:yyyy-MM-dd " + "05:00:00.000" + "}", dte.AddDays(1));
            string sql = "SELECT COUNT(SOURCEID) AS 'MYCOUNT' "
                         + "FROM [OADB].[oadb].[CmsCallHistory] "
                         + "WHERE disposition = 3 and SPLIT1 in (" + SkillNumber + ") AND SEGSTOP BETWEEN '" +
                         fixedStartDate + "' and '" + fixedEndDate + "'";
            using (SqlConnection connection = new SqlConnection("Data Source=database;Initial Catalog=OADB;User Id=readonly;Password=password"))

            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                try
                {
                    var dataSet = new DataSet();

                    var dataAdapter = new SqlDataAdapter { SelectCommand = command };

                    dataAdapter.Fill(dataSet);

                    return dataSet.Tables[0].Rows[0]["MYCOUNT"].ToString();


                }
                catch (Exception e)
                {
                    throw new Exception(e.Message, e);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                }
            }
        }
        public async Task RunPeriodicQueryTotalCalls()
        {
            TimeSpan interval = TimeSpan.FromMinutes(1);

            while (true)
            {
                await Task.Delay(interval);

                string result = await Task.Run((Func<string>)SQLDataTotalCalls);

                TotalDailyCalls = result;
            }
        }
        public async Task RunPeriodicQueryAbandonCalls()
        {
            TimeSpan interval = TimeSpan.FromMinutes(1);
            TimeSpan inter = TimeSpan.FromDays(1);

            while (true)
            {
                await Task.Delay(interval);

                string result = await Task.Run((Func<string>)SQLDataAbandonCalls);

                TotalDailyAbandon = result;
            }

        }
   }
}
\$\endgroup\$
1
  • \$\begingroup\$ Is there any reason why you are not using Entity Framework or anything alike? It would be so much easier to make it async. \$\endgroup\$
    – t3chb0t
    Commented May 7, 2017 at 9:00

2 Answers 2

1
\$\begingroup\$

The locking UI problem comes from a misunderstanding between async/await/task and multithread.

First thing first: WPF has one single thread to control the interface, and that's one of the reasons for the existence of dispatcher: when you have to do something computationally intensive you do on a new, different thread, and then from inside this thread you use the dispatcher to change the UI content.

Now your problem here is that async per se doesn't manage threading: async is a layer to help manage states and asynchronous operations, if you use it on a blocking call like a query...you still block everything.

So, what can you do?

  1. Simple, just call each of your query functions (SQLDataTotalCalls, SQLDataSevenPastCalls, SQLDataAbandonCalls) with the ThreadPool.QueueUserWorkItem method.
  2. You can do this by using a timer to trigger the calls: create a Timer and in its Elapsed event call the ThreadPool.QueueUserWorkItem queueing the three functions.
  3. When the functions get the query results, remember to use a lock object to update the common variables. Here it's not a big deal, as each function just write a single variable and the UI only read them, but it's a good habit to always use one.
  4. UI: are you sure the Dispatcher.BeginInvoke is necessary in the OnMessageReceived method? Is it really running in a different thread? Anyway, WPF already checks itself if it really needs to invoke or not, so you can keep it, still probably you don't want to BeginInvoke as in this case it's an unnecessary overhead. Use Invoke when you are going to do something simple, something that will finish quickly, like updating some labels text to some variables. Use BeginInvoke when your operation can be delayed, letting the UI scheduler decide when will it be a better moment; if the operations you want to be done are really simple, there is no need to ask the system to do all the additional work to schedule them.

A small side note, consider it a useful exercise about something that people get wrong more often than not: think carefully about the timer timing and long threads...I've seen lot of people crashing servers and loosing tons of data, with that ;-)

\$\endgroup\$
1
  • \$\begingroup\$ SQLcommand and SQLreader support async and it is pretty clean \$\endgroup\$
    – paparazzo
    Commented May 7, 2017 at 16:52
0
\$\begingroup\$

qA DataSet to retrieve one number is wasteful. Use ExecuteScalar. Those methods just differ by date from what I can tell. I would just have one method and pass a parameter for what you want. You could also stack 3 or 2 and get the results using Reader and NextResult. This is more efficient. SQLcommand and SQLreader support async - this will fix the application locking up.

It looks like you are running the queries every minute but only update with UI when a message is received. And the entire application is locked up. That is not a good design in my opinion. Run one query and only when you need it.

Use data binding with properties (get set) and iNotifyPropertyChanged.

TotalCallsCompareData.Text = TotalDailyLast7Days; does not change but you are resetting it on every message.

Optimize the query with indexes.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.