3.0/LINQ Very slow performance reading data-VBForums
Results 1 to 12 of 12

Thread: Very slow performance reading data

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2007
    Location
    Essex
    Posts
    84

    Very slow performance reading data

    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).

    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";
    
            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

  2. #2

    Thread Starter
    Lively Member
    Join Date
    Mar 2007
    Location
    Essex
    Posts
    84

    Re: Very slow performance reading data

    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.

    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

  3. #3
    PowerPoster
    Join Date
    May 2002
    Posts
    25,741

    Re: Very slow performance reading data

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Mar 2007
    Location
    Essex
    Posts
    84

    Re: Very slow performance reading data

    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

  5. #5
    PowerPoster
    Join Date
    May 2002
    Posts
    25,741

    Re: Very slow performance reading data

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Mar 2007
    Location
    Essex
    Posts
    84

    Re: Very slow performance reading data

    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.


    Code:
    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

  7. #7
    PowerPoster
    Join Date
    May 2002
    Posts
    25,741

    Re: Very slow performance reading data

    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:
    Code:
    Dictionary myList<string, double> = new Dictionary<string, double>
    now you can add to it:
    Code:
    myList.Add(put the key here, put the value here);
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Mar 2007
    Location
    Essex
    Posts
    84

    Re: Very slow performance reading data

    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

  9. #9
    PowerPoster
    Join Date
    May 2002
    Posts
    25,741

    Re: Very slow performance reading data

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Mar 2007
    Location
    Essex
    Posts
    84

    Re: Very slow performance reading data

    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.

    Code:
    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
    Last edited by Madcat1981; Feb 13th, 2012 at 10:20 AM. Reason: Updated code

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Mar 2007
    Location
    Essex
    Posts
    84

    Re: Very slow performance reading data

    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.

    Code:
    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
    Last edited by Madcat1981; Feb 14th, 2012 at 05:27 AM. Reason: extra info

  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,871

    Re: Very slow performance reading data

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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.