Very slow performance reading data

[Madcat1981]

Afternoon all,

I have a chart which is populated via a database with a OleDbConnection which only adds the series where values are over £0. Whilst my code does work it is extremely slow and having to wait 30 seconds for only one chart to update is far too much!

Below is my code for a sample project using the same methods (sorry if it's a bit long).

using System;
using System.Drawing;
using System.Windows.Forms;
using Telerik.Charting;
using System.Data.OleDb;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        string connectionString = Convert.ToString(WindowsFormsApplication1.Properties.Settings.Default.MOA_DBConnectionString);
        ChartSeries chartSeries = new ChartSeries("Material Types", ChartSeriesType.Bar);
        string agentName = "Admin";

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            styleCharts();
            buildDAMTChart();
        }

        void styleCharts()
        {
            //Styles for Chart 1
            radChart1.SkinsOverrideStyles = false;
            radChart1.Appearance.Border.Color = Color.Transparent;
            radChart1.Appearance.FillStyle.MainColor = Color.Transparent;

            radChart1.PlotArea.XAxis.Appearance.TextAppearance.TextProperties.Color = Color.FromArgb(255, 21, 66, 139);
            radChart1.PlotArea.YAxis.Appearance.TextAppearance.TextProperties.Color = Color.FromArgb(255, 21, 66, 139);
            radChart1.PlotArea.YAxis.AxisLabel.TextBlock.Appearance.TextProperties.Color = Color.FromArgb(255, 21, 66, 139);
        }

        private double getDAMTVal(string matType)
        {
            string queryString = "SELECT Agent, MatType, SUM(RevenueValue), [Date], Delete FROM Orders WHERE Agent=? and MatType=? and [Date]=? and Delete<>? GROUP BY Agent, MatType, [Date], Delete";
            double sum = 0;

            OleDbConnection mConnection = new OleDbConnection(connectionString);
            OleDbCommand mCommand = new OleDbCommand(queryString, mConnection);

            mCommand.Parameters.AddWithValue("Agent", agentName);
            mCommand.Parameters.AddWithValue("MatType", matType);
            mCommand.Parameters.AddWithValue("[Date]", DateTime.Today);
            mCommand.Parameters.AddWithValue("Delete", true);

            try
            {
                mConnection.Open();
                OleDbDataReader reader = mCommand.ExecuteReader();

                while (reader.Read())
                {
                    sum = Convert.ToDouble(reader.GetValue(2).ToString());
                }
                reader.Close();

                mCommand.Dispose();
                mConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Connection failed\n" + ex.Message);
            }
            return sum;
        }

        void buildDAMTChart()
        {
            chartSeries.Clear();
            int itemCount = 0;

            //Add series only when value higher than £0
            if (getDAMTVal("Ink") > 0)
            {
                chartSeries.AddItem(getDAMTVal("Ink"));
                itemCount = itemCount + 1;
            }
            if (getDAMTVal("Labels") > 0)
            {
                chartSeries.AddItem(getDAMTVal("Labels"));
                itemCount = itemCount + 1;
            }
            if (getDAMTVal("Cleaning Kit") > 0)
            {
                chartSeries.AddItem(getDAMTVal("Cleaning Kit"));
                itemCount = itemCount + 1;
            }
            if (getDAMTVal("EZ Seal") > 0)
            {
                chartSeries.AddItem(getDAMTVal("EZ Seal"));
                itemCount = itemCount + 1;
            }
            if (getDAMTVal("Toners") > 0)
            {
                chartSeries.AddItem(getDAMTVal("Toners"));
                itemCount = itemCount + 1;
            }
            if (getDAMTVal("Miscellaneous") > 0)
            {
                chartSeries.AddItem(getDAMTVal("Miscellaneous"));
                itemCount = itemCount + 1;
            }
            if (getDAMTVal("Bindomatic") > 0)
            {
                chartSeries.AddItem(getDAMTVal("Bindomatic"));
                itemCount = itemCount + 1;
            }
            if (getDAMTVal("Paper") > 0)
            {
                chartSeries.AddItem(getDAMTVal("Paper"));
                itemCount = itemCount + 1;
            }
            if (getDAMTVal("Envelopes") > 0)
            {
                chartSeries.AddItem(getDAMTVal("Envelopes"));
                itemCount = itemCount + 1;
            }
            if (getDAMTVal("Freight") > 0)
            {
                chartSeries.AddItem(getDAMTVal("Freight"));
                itemCount = itemCount + 1;
            }

            if (itemCount > 0)
            {
                radChart1.PlotArea.XAxis.AutoScale = false;
                radChart1.PlotArea.XAxis.AddRange(1, itemCount, 1);

                int i = 0;
                //Add XAxis Labels
                if (getDAMTVal("Ink") > 0)
                {
                    radChart1.PlotArea.XAxis[i].TextBlock.Text = "Ink";
                    i = i + 1;
                }
                if (getDAMTVal("Labels") > 0)
                {
                    radChart1.PlotArea.XAxis[i].TextBlock.Text = "Labels";
                    i = i + 1;
                }
                if (getDAMTVal("Cleaning Kit") > 0)
                {
                    radChart1.PlotArea.XAxis[i].TextBlock.Text = "Cleaning Kits";
                    i = i + 1;
                }
                if (getDAMTVal("EZ Seal") > 0)
                {
                    radChart1.PlotArea.XAxis[i].TextBlock.Text = "EZ Seal";
                    i = i + 1;
                }
                if (getDAMTVal("Toners") > 0)
                {
                    radChart1.PlotArea.XAxis[i].TextBlock.Text = "Toners";
                    i = i + 1;
                }
                if (getDAMTVal("Miscellaneous") > 0)
                {
                    radChart1.PlotArea.XAxis[i].TextBlock.Text = "Miscellaneous";
                    i = i + 1;
                }
                if (getDAMTVal("Bindomatic") > 0)
                {
                    radChart1.PlotArea.XAxis[i].TextBlock.Text = "Bindomatic";
                    i = i + 1;
                }
                if (getDAMTVal("Paper") > 0)
                {
                    radChart1.PlotArea.XAxis[i].TextBlock.Text = "Paper";
                    i = i + 1;
                }
                if (getDAMTVal("Envelopes") > 0)
                {
                    radChart1.PlotArea.XAxis[i].TextBlock.Text = "Envelopes";
                    i = i + 1;
                }
                if (getDAMTVal("Freight") > 0)
                {
                    radChart1.PlotArea.XAxis[i].TextBlock.Text = "Freight";
                    i = i + 1;
                }
            }
            radChart1.PlotArea.YAxis.AxisLabel.TextBlock.Text = "£ Revenue";
            radChart1.PlotArea.YAxis.AxisLabel.Visible = true;
            radChart1.Series.Add(chartSeries);
        }
    }
}

I suspect that the issue is coming from having to open and close connections a fair deal but unsure of what to do if it is.

Is what I have written the correct approach to handling my situation? As always, I appreciate any help or pointers anyone can give