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

Guy

[Madcat1981]

Evening,

Just wanted to give an update and ask some additional questions (hopefully this doesn't break any kind of bump policy; I didn't see the need to create a new thread).

Ok, so I went away and looked at the process I had created and thought about where I could make changes. I realised that I was calling the database far more than I needed and this was obviously creating a delay.

Here is my new code.

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";
        double inkValue = 0;
        double labelsValue = 0;
        double ckitsValue = 0;
        double ezsealValue = 0;
        double tonersValue = 0;
        double miscValue = 0;
        double bindoValue = 0;
        double paperValue = 0;
        double envValue = 0;
        double freightValue = 0;

        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 getDBValues(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 i = 0;
            radChart1.PlotArea.XAxis.AutoScale = false;

            inkValue = getDBValues("Ink");
            labelsValue = getDBValues("Labels");
            ckitsValue = getDBValues("Cleaning Kit");
            ezsealValue = getDBValues("EZ Seal");
            tonersValue = getDBValues("Toners");
            miscValue = getDBValues("Miscellaneous");
            bindoValue = getDBValues("Bindomatic");
            paperValue = getDBValues("Paper");
            envValue = getDBValues("Envelopes");
            freightValue = getDBValues("Freight");

            int itemCount = 0;

            if (inkValue > 0){itemCount=itemCount+1;}
            if (labelsValue > 0) { itemCount = itemCount + 1; }
            if (ckitsValue > 0) { itemCount = itemCount + 1; }
            if (ezsealValue > 0) { itemCount = itemCount + 1; }
            if (tonersValue > 0) { itemCount = itemCount + 1; }
            if (miscValue > 0) { itemCount = itemCount + 1; }
            if (bindoValue > 0) { itemCount = itemCount + 1; }
            if (paperValue > 0) { itemCount = itemCount + 1; }
            if (envValue > 0) { itemCount = itemCount + 1; }
            if (freightValue > 0) { itemCount = itemCount + 1; }

            radChart1.PlotArea.XAxis.AddRange(1, itemCount, 1);

            //Add series and xaxis label only when value higher than £0
            if (inkValue > 0)
            {
                chartSeries.AddItem(inkValue);
                radChart1.PlotArea.XAxis[i].TextBlock.Text = "Ink";
                i = i + 1;
            }
            if (labelsValue > 0)
            {
                chartSeries.AddItem(labelsValue);
                radChart1.PlotArea.XAxis[i].TextBlock.Text = "Labels";
                i = i + 1;
            }
            if (ckitsValue > 0)
            {
                chartSeries.AddItem(ckitsValue);
                radChart1.PlotArea.XAxis[i].TextBlock.Text = "Cleaning Kits";
                i = i + 1;
            }
            if (ezsealValue > 0)
            {
                chartSeries.AddItem(ezsealValue);
                radChart1.PlotArea.XAxis[i].TextBlock.Text = "EZ Seal";
                i = i + 1;
            }
            if (tonersValue > 0)
            {
                chartSeries.AddItem(tonersValue);
                radChart1.PlotArea.XAxis[i].TextBlock.Text = "Toners";
                i = i + 1;
            }
            if (miscValue > 0)
            {
                chartSeries.AddItem(miscValue);
                radChart1.PlotArea.XAxis[i].TextBlock.Text = "Miscellaneous";
                i = i + 1;
            }
            if (bindoValue > 0)
            {
                chartSeries.AddItem(bindoValue);
                radChart1.PlotArea.XAxis[i].TextBlock.Text = "Bindomatic";
                i = i + 1;
            }
            if (paperValue > 0)
            {
                chartSeries.AddItem(paperValue);
                radChart1.PlotArea.XAxis[i].TextBlock.Text = "Paper";
                i = i + 1;
            }
            if (envValue > 0)
            {
                chartSeries.AddItem(envValue);
                radChart1.PlotArea.XAxis[i].TextBlock.Text = "Envelopes";
                i = i + 1;
            }
            if (freightValue > 0)
            {
                chartSeries.AddItem(freightValue);
                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);
        }
    }
}

As you can see I do the one call and then store the results into variables to use later. This has massively improved the response time (I also think it was due to network issues also).

I still feel there is some room for improvement and the parts I have marked in bold I feel could be done better. Is this where an Array would come in handy? I've not used them much before so not sure and if not are there any other methods or approached I could use to improve the code?

Many thanks,

Guy

[techgnome]

oooh... wow... okay... yes... there IS a better way to get that data... I need to chew on it for a bit though... it's the end of the day and my brain is fried... but you should be getting your data all in one chunk... not in individual calls one by one... I'd take matType out of the where clause, but keep it in the group by.... then when you readf back the results, you'll have all of the amounts needed for your matTypes... then you can create a Disctionary<String, double> loop through the results, and add to the dictionary using matType as the Key and the Sum (please give the field a name) as the value ... then... from the calling side... replace the bold section where youre counting the resutls, and use the dictionary.length instead (that should return the count of items in the dictionary) .... that should speed it up tremendously...

-tg

[Madcat1981]

Awww, here I was thinking I had done a reasonable job lol.

I appreciate your help in advanced. After reading what I should do I share the brain friend feeling! haha.

I think I see where you're coming from; instead of doing individual WHERE's for the matType, you still Group By but pull all out at once then loop through and assign into a dictionary. The SUM field is called RevenueValue. Will the dictionary.length only be for those that have values of over 0?

Again, thanks for your help with this.

Guy

[techgnome]

I think I see where you're coming from; instead of doing individual WHERE's for the matType, you still Group By but pull all out at once then loop through and assign into a dictionary. The SUM field is called RevenueValue.

YEs! you got it!

Will the dictionary.length only be for those that have values of over 0?

No... but it can... if you add a "Having SUM(RevenueValue) > 0" to the end of your SQL... then it only returns rows where the sum is greater than 0.

-tg

[Madcat1981]

Afternoon techgnome,

Ok, whilst I think I have the basic principle down and like to work out things for myself, i've reached the end of my knowledge and not sure of what to look up to further my progression.

Below is my new code as a basic proof of concept so that I know exactly what I'm doing. Please let me know if I've got the wrong end of the stick regarding something and also how I can progress with the bits I've commented.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;


namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        Dictionary<string, double> d = new Dictionary<string, double>();
        int itemCount = 0;
        string connectionString = Convert.ToString(WindowsFormsApplication1.Properties.Settings.Default.MOA_DBConnectionString);
        string agentName = "Admin";

        public Form1()
        {
            InitializeComponent();
        }

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

        void getDBValues()
        {
            string queryString = "SELECT Agent, MatType, SUM(RevenueValue), [Date], Delete FROM Orders WHERE Agent=? and [Date]=? and Delete<>? GROUP BY Agent, MatType, [Date], Delete HAVING SUM(RevenueValue)>0";

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

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

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

                while (reader.Read())
                {
                    //Loop through matTypes and add to Dictionary?
                    //How is this accomplished?
                    //Is it something to do with KeyValuePair?
                }
                reader.Close();

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

            itemCount = d.Count;
        }

        void buildChart()
        {
            getDBValues();
            //Go through as before and create the series.
            //Will the Dictionary Keys be called what they are as before?
        }
    }
}

Many thanks!

Guy

[techgnome]

Ok... looks like a good start to me...
to declare a dictionary you need to decide what the key type will be, and what the value type will be... I'm guessign your key will be string, and the valeu double...

- quick note... you still should give your sum field a name...
SELECT Agent, MatType, SUM(RevenueValue), [Date], Delete FROM Orders
s/b something like this:
SELECT Agent, MatType, SUM(RevenueValue) as TotalRevenueValue, [Date], Delete FROM Orders

then declare your dictionary:

Dictionary myList<string, double> = new Dictionary<string, double>

now you can add to it:

myList.Add(put the key here, put the value here);

-tg

[Madcat1981]

Hi techgnome,

How exactly do I perform the loop through the data and add to the dictionary? That's where I've got stuck. Obviously once in the Dictionary, how should I then read those values as I add them to my chart series?

Many thanks,

Guy

[techgnome]

For that I'm going to refer you to our Database FAQ & Tutorial area... it should have just about everything you need. I will say that the fastest way to read the data will be to create a SQLDataReader, use the .ExecuteReader function of the command object, then while you can reader.read data, loop through your data. Within the loop, put the data into variables, then add them to the dictionary. Hopefully that's a big enough hint to get you going in the right direction.

-tg

[Madcat1981]

Morning,

I think I might have just nailed it!

Whilst my below code does work, please let me know if something should be done differently or could be tweaked, thanks.

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

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        Dictionary<string, double> d = new Dictionary<string, double>();
        int itemCount = 0;
        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);
        }

        void buildDAMTChart()
        {
            string queryString = "SELECT Agent, MatType, SUM(RevenueValue) as TotalRevenue, [Date], Delete FROM Orders WHERE Agent=? and [Date]=? and Delete<>? GROUP BY Agent, MatType, [Date], Delete HAVING SUM(RevenueValue)>0";

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

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

            try
            {
                mConnection.Open();
                OleDbDataReader reader = mCommand.ExecuteReader();
                if (reader.HasRows) //check to see if there are any rows before reading
                {
                    while (reader.Read())
                    {
                        string materialType = reader.GetString(1);
                        double sum = Convert.ToDouble(reader.GetDecimal(2));
                        d.Add(materialType, sum);
                    }
                }
                reader.Close();

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

            itemCount = d.Count;

            chartSeries.Clear();
            radChart1.PlotArea.XAxis.AutoScale = false;
            radChart1.PlotArea.XAxis.AddRange(1, itemCount, 1);
            int i = 0;

            foreach (KeyValuePair<string, double> pair in d)
            {
                chartSeries.AddItem(pair.Value);
                radChart1.PlotArea.XAxis[i].TextBlock.Text = pair.Key;
                i = i + 1;
            }
            radChart1.PlotArea.YAxis.AxisLabel.TextBlock.Text = "&#163; Revenue";
            radChart1.PlotArea.YAxis.AxisLabel.Visible = true;
            radChart1.Series.Add(chartSeries);
        }
    }
}

Guy

[Madcat1981]

Got a quick question regarding the Reader and adding items to the Dictionary.

I've got another part of my project I'm hoping to move over to the above methods used but got stuck and not sure if it's something I'm doing or something which just can't be done.

I have a table which multiple columns which when read will only have one row.

What I would like to do is add the field name and it's value as a Key and Value to a Dictionary. I have tried the following code but it just seems to only retrieve the first field and that's it.

Dictionary<string, double> dictionary = new Dictionary<string, double>();
                    int fieldInt = 0;
                    while (reader.Read())
                    {
                        string fieldName = reader.GetName(fieldInt).ToString();
                        double fieldVal = Convert.ToDouble(reader.GetDecimal(fieldInt));
                        dictionary.Add(fieldName, fieldVal);
                        fieldInt = fieldInt + 1;
                    }

Should I be using a different method in order to loop through the columns to successfully add them to my Dictionary?

Edit: Ok, I think I see the problem. It's only pulling one column as it is only reading the 1 row. I would then assume that the best approach from here would be to create the multiple Dictionary.Add()'s that I need and list them all whilst reading?

Many thanks,

Guy

[szlamany]

Quick observation - your use of DOUBLE instead of DECIMAL can lead to penny loss if you are adding monetary figures.