-
Feb 8th, 2012, 09:46 AM
#1
Thread Starter
Lively Member
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
Last edited by Madcat1981; Jan 6th, 2016 at 10:11 AM.
-
Feb 9th, 2012, 04:24 PM
#2
Thread Starter
Lively Member
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,
Last edited by Madcat1981; Jan 6th, 2016 at 10:11 AM.
-
Feb 9th, 2012, 05:19 PM
#3
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
-
Feb 9th, 2012, 05:32 PM
#4
Thread Starter
Lively Member
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.
Last edited by Madcat1981; Jan 6th, 2016 at 10:11 AM.
-
Feb 9th, 2012, 07:49 PM
#5
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
-
Feb 10th, 2012, 11:31 AM
#6
Thread Starter
Lively Member
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!
Last edited by Madcat1981; Jan 6th, 2016 at 10:11 AM.
-
Feb 10th, 2012, 12:33 PM
#7
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
-
Feb 11th, 2012, 05:17 PM
#8
Thread Starter
Lively Member
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,
Last edited by Madcat1981; Jan 6th, 2016 at 10:11 AM.
-
Feb 11th, 2012, 09:07 PM
#9
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
-
Feb 13th, 2012, 05:05 AM
#10
Thread Starter
Lively Member
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 = "£ Revenue";
radChart1.PlotArea.YAxis.AxisLabel.Visible = true;
radChart1.Series.Add(chartSeries);
}
}
}
Last edited by Madcat1981; Jan 6th, 2016 at 10:10 AM.
Reason: Updated code
-
Feb 14th, 2012, 05:12 AM
#11
Thread Starter
Lively Member
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,
Last edited by Madcat1981; Jan 6th, 2016 at 10:10 AM.
Reason: extra info
-
Feb 14th, 2012, 06:23 AM
#12
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|