23 Replies Latest reply on Jul 7, 2015 9:50 AM by shekar

    Need to optimize the code which is used to bind the data to the asp.net microsoft chart line control using PI OLEDB Or Any other way

    shekar

      Hi Team,

       

      I am developing the Microsoft Line Chart control to show the 24 hours data by using PI OLEDB for the particular 2 tags. For time being I have taken 'Sinusoid and CDT158' tags. I have binded the data to the Line Chart control by using the below code.

       

      .ASPX PAGE

      ----------------

       

      <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="WebApplication5.WebForm2" %>

      <%@ Register Assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"

          Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %>

       

      <table>

                          <tr>

                              <td align="center" style="background-color:rgb(46,117,182);font-family:Microsoft Sans Serif;font-size:15px">

                                  <asp:Label ID="lbl_WP" runat="server" Text="Water Production (m3/h)" Width="300px" ForeColor="White">

                                  </asp:Label>

                                  <asp:Label ID="lbl" runat="server"></asp:Label>

                              </td>

                          </tr>

                          <tr>

                              <td>               

                      <asp:Chart ID="Chart1" runat="server">

                          <Series>

                              <asp:Series Name="Series1" ChartType="Line" Color="Green" YValuesPerPoint="6" ToolTip="Y Value: #VALY \n X Value: #VALX"> <%--XValueType="DateTime"--%>

                              </asp:Series>

                              <asp:Series Name="Series2" ChartType="Line" Color="#0c9eff" YValuesPerPoint="6" ToolTip="Y Value: #VALY \n X Value: #VALX"> <%--XValueType="DateTime"--%>

                              </asp:Series>

                          </Series>

                          <ChartAreas>

                              <asp:ChartArea Name="ChartArea1" BorderDashStyle="Dash" BorderWidth="1">

                                  <AxisY>

                                      <MajorGrid Enabled="true" />

                                      <%--<LabelStyle Format="{#}%" />--%>                                                              

                                  </AxisY>

                                  <AxisX >                               

                                      <MajorGrid Enabled="false" />

                                      <%--<LabelStyle Format="hh:mm:ss" IntervalType="Minutes"  Interval="10"/>--%>                                                                                                                             

                                  </AxisX>

                              </asp:ChartArea>

                          </ChartAreas>

                      </asp:Chart>               

                              </td>

                          </tr>

                      </table>

       

      .ASPX.CS PAGE

      ---------------------

       

      using System;

      using System.Collections.Generic;

      using System.Linq;

      using System.Web;

      using System.Web.UI;

      using System.Web.UI.WebControls;

      using System.Data.OleDb;

      using System.Web.UI.DataVisualization.Charting;

      using System.Data;

       

      namespace WebApplication5

      {

          public partial class WebForm2 : System.Web.UI.Page

          {

              string strConPI = "Data Source=E20052000;Integrated Security=true;Initial Catalog=piarchive;Provider=PIOLEDB.1;Always Return Rowset=True";

              DataTable dt = new DataTable();

              DataTable dt2 = new DataTable();

              DataView dv = new DataView();

              double intervalSet;

              string[] x, p = null;

              int[] y, q = null;

              protected void Page_Load(object sender, EventArgs e)

              {

                  //Label1.Text = "Page loaded time : " + DateTime.Now.ToString();

                  UpdateLineChartData();

                  //else

                  //    lblMsgLoad.Text = "No Data...!";

              }

             

              private void UpdateLineChartData()

              {

                  lbl.Text = DateTime.Now.ToString();

                  //Series 1

                  string strCmd = "SELECT time,value "

                                  + "FROM [piarchive].[picomp2] "

                                  + "WHERE tag = 'sinusoid' "

                                  + "AND time BETWEEN '*-24h' AND '*'";

                  OleDbDataAdapter oledbDA = new OleDbDataAdapter(strCmd, strConPI);

                  oledbDA.Fill(dt);

                  if (dt != null && dt.Rows.Count > 0)

                  {

                      //dv = dt.DefaultView;

                      x = new string[dt.Rows.Count];

                      y = new int[dt.Rows.Count];

                      for (int i = 0; i < dt.Rows.Count; i++)

                      {

                          if (!string.IsNullOrEmpty(dt.Rows[i][0].ToString()))

                          {

                              x[i] = Convert.ToDateTime(dt.Rows[i][0].ToString()).ToString("hh");

                              x[i] = x[i] + ":00";

                          }

       

                          if (!string.IsNullOrEmpty(dt.Rows[i][1].ToString()))

                              y[i] = Convert.ToInt32(dt.Rows[i][1]);

                          //break;

                      }

                     

                      //Chart1.Series["Series1"].Points.DataBindXY(dv, "hour", dv, "value");

                  }

                  //Series 2

                  string strCmd_2 = "SELECT time,value "

                          + "FROM [piarchive].[picomp2] "

                          + "WHERE tag = 'cdt158' "

                          + "AND time BETWEEN '*-24h' AND '*'";

                  OleDbDataAdapter oledbDA_2 = new OleDbDataAdapter(strCmd_2, strConPI);

                  oledbDA_2.Fill(dt2);

                  if (dt2 != null && dt2.Rows.Count > 0)

                  {

                      //dv = dt2.DefaultView;

                      p = new string[dt2.Rows.Count];

                      q = new int[dt2.Rows.Count];

                      for (int i = 0; i < dt2.Rows.Count; i++)

                      {

                          if (!string.IsNullOrEmpty(dt2.Rows[i][0].ToString()))

                          {

                              p[i] = Convert.ToDateTime(dt2.Rows[i][0].ToString()).ToString("hh");

                              p[i] = p[i] + ":00";

                          }

       

                          if (!string.IsNullOrEmpty(dt2.Rows[i][1].ToString()))

                              q[i] = Convert.ToInt32(dt2.Rows[i][1]);

                          //break;

                      }               

                      //Chart1.Series["Series2"].Points.DataBindXY(dv, "hour", dv, "value");

                  }

                  if (dt.Rows.Count > dt2.Rows.Count)

                      Chart1.ChartAreas[0].AxisX.Interval = Convert.ToInt32(dt.Rows.Count)/5;

                  else

                      Chart1.ChartAreas[0].AxisX.Interval = Convert.ToInt32(dt2.Rows.Count)/5;

       

                  if(y!=null && y.Length > 0)

                      Chart1.Series["Series1"].Points.DataBindXY(x, y);

                  if (q != null && q.Length > 0)

                      Chart1.Series["Series2"].Points.DataBindXY(p, q);

              }      

          }

      }

       

      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

       

      By using the above PI SQL query it is taking lot of time to load the output page if there are more than 50 values for the tags. I want to optimize the above code to load the the page immediately.

       

      Also is there any query to retrieve the data in interval times like every 4 hours data upto 24 hours.

       

      Request to provide any better solution to optimise the above code.

       

      thanks....

        • Re: Need to optimize the code which is used to bind the data to the asp.net microsoft chart line control using PI OLEDB Or Any other way
          Asle Frantzen

          Hi

           

          It seems you are using the OLEDB queries as recommended. Have you run the queries in PI SQL Commander and seen the execution time there? If I run the last one I get 266 values in 0.063 seconds.

           

          An alternative could be to use the SDK or the WebAPI though.

           

           

          If you want to receive evenly spaced values you should query the piinterp2 table instead, and give a timestep of '4h'.

          • Re: Need to optimize the code which is used to bind the data to the asp.net microsoft chart line control using PI OLEDB Or Any other way
            Eugene Lee

            Hi madhu,

             

            I could reproduce your issue. However, it seems that the bottleneck is not PI OLEDB but rather the web page loading. sinusoid and cdt158 took about 2.5 seconds in total to be queried while the webpage took about 30 seconds to load for me. Let me see if there is a better way to transfer info from datatable to the enumerable rather than looping

             

            • Re: Need to optimize the code which is used to bind the data to the asp.net microsoft chart line control using PI OLEDB Or Any other way
              shekar

              Hi Eugene,

               

              You understood exactly the problem where I am facing.

               

              I also tried to bind the datatable direct to the Chart line control, by using the below code after getting data in to datatable.

               

              Query for Series 1

              -----------------------

              SELECT Hour(time) as hour, value

              FROM [piarchive].[picomp2]

              WHERE tag = 'sinusoid'

                   AND time BETWEEN '*-24h' AND '*'

               

              Code:

              -------

              if(dt.rows.count > 0)

              {

                   dataview dv = dt.Defaultview

                   Chart1.Series[1].Points.DataBindXY(dv,"hour",dv,"time")

              }

               

              Query for Series 2

              -----------------------

              SELECT Hour(time) as hour, value

              FROM [piarchive].[picomp2]

              WHERE tag = 'cdt158'

                   AND time BETWEEN '*-24h' AND '*'

               

              Code:

              -------

              if(dt2.rows.count > 0)

              {

                   dataview dv1 = dt2.Defaultview

                   Chart1.Series[2].Points.DataBindXY(dv1,"hour",dv1,"time")

              }

              By doing this way I am getting wrong 'X-axis values' i.e, in Ascending order. Also I am getting only 2 values in 'X-axis'

               

              MicrosoftLineControl.png

               

              Request to provide any better solution.

               

              thanks....

              • Re: Need to optimize the code which is used to bind the data to the asp.net microsoft chart line control using PI OLEDB Or Any other way
                shekar

                Hi Eugene,

                 

                thank you very much for your reply.

                 

                After removing 'Tool Tip', the page is loaded with 49 seconds but after placing 'Tool tip' page is loaded with 59 seconds. By removing 'Tool tip', the page performance is increased by 10 seconds only.

                 

                Also I have applied 'Ajax timer control' to refresh the trend control page automatically for every 10 seconds. After applying this ajax, when the page loads first time i.e, before ajax code execution showing the 'X-axis' time values in one way. After auto page refresh i.e, ajax code execution showing the 'X-axis' time values different.

                 

                I am not understanding the problem. I am placing the before and after images and complete code for reference.

                 

                Before Auto Refresh i.e, Page load code executed

                ---------------------------------------------------------------

                Before Auto Refresh.jpg

                After Auto Refresh i.e, Ajax code executed

                -----------------------------------------------------

                After Auto Refresh.png

                I am placing the complete code for reference.

                 

                Design Code (aspx code)

                -----------------------------------

                <html xmlns="http://www.w3.org/1999/xhtml">

                <head runat="server">

                    <title></title>

                </head>

                <body>

                    <form id="form1" runat="server">

                    <div>

                        <asp:ScriptManager ID="ScriptManager1" runat="server">

                        </asp:ScriptManager>

                        <asp:Timer ID="Timer1" runat="server" ontick="Timer1_Tick" Interval="10000">

                        </asp:Timer>

                        <asp:UpdatePanel ID="UpdatePanel1" runat="server">

                            <ContentTemplate>

                                <table>

                                    <tr>

                                        <td align="center" style="background-color:rgb(46,117,182);font-family:Microsoft Sans Serif;font-size:15px">

                                            <asp:Label ID="lbl_WP" runat="server" Text="Water Production (m3/h)" Width="300px" ForeColor="White">

                                            </asp:Label>

                                            <asp:Label ID="lbl" runat="server"></asp:Label>

                                        </td>

                                    </tr>

                                    <tr>

                                        <td>               

                                <asp:Chart ID="Chart1" runat="server">

                                    <Series>

                                        <asp:Series Name="Series1" ChartType="Line" XValueType="String" Color="Green" YValuesPerPoint="6" > <%--ToolTip="Y Value: #VALY \n X Value: #VALX" XValueType="DateTime"--%>

                                        </asp:Series>

                                        <asp:Series Name="Series2" ChartType="Line" XValueType="String" Color="#0c9eff" YValuesPerPoint="6"  > <%--ToolTip="Y Value: #VALY \n X Value: #VALX"  XValueType="DateTime"--%>

                                        </asp:Series>

                                    </Series>

                                    <ChartAreas>

                                        <asp:ChartArea Name="ChartArea1" BorderDashStyle="Dash" BorderWidth="1">

                                            <AxisY>

                                                <MajorGrid Enabled="true" />

                                                <%--<LabelStyle Format="{#}%" />--%>                                                              

                                            </AxisY>

                                            <AxisX >                               

                                                <MajorGrid Enabled="false" />

                                                <%--<LabelStyle Format="hh:mm:ss" IntervalType="Minutes"  Interval="10"/>--%>                                                                                                                             

                                            </AxisX>

                                        </asp:ChartArea>

                                    </ChartAreas>

                                </asp:Chart>               

                                        </td>

                                    </tr>

                                </table>

                            </ContentTemplate>

                            <Triggers>

                                <asp:AsyncPostBackTrigger ControlID="Timer1" EventName="Tick" />

                            </Triggers>

                        </asp:UpdatePanel>

                    </div>

                    </form>

                </body>

                </html>

                 

                Aspx.cs code

                ------------------

                using System;

                using System.Collections.Generic;

                using System.Linq;

                using System.Web;

                using System.Web.UI;

                using System.Web.UI.WebControls;

                using System.Data.OleDb;

                using System.Web.UI.DataVisualization.Charting;

                using System.Data;

                 

                namespace WebApplication5

                {

                    public partial class WebForm2 : System.Web.UI.Page

                    {

                        string strConPI = "Data Source=E20052000;Integrated Security=true;Initial Catalog=piarchive;Provider=PIOLEDB.1;Always Return Rowset=True";

                        DataTable dt = new DataTable();

                        DataTable dt2 = new DataTable();

                        DataView dv = new DataView();

                        DataView dv_1 = new DataView();

                        double intervalSet;

                        string[] x, p = null;

                        int[] y, q = null;

                        protected void Page_Load(object sender, EventArgs e)

                        {

                            //Label1.Text = "Page loaded time : " + DateTime.Now.ToString();

                            Timer1_Tick(sender, e);

                            //UpdateLineChartData();

                            //else

                            //    lblMsgLoad.Text = "No Data...!";           

                        }

                        protected void Timer1_Tick(object sender, EventArgs e)

                        {

                            UpdateLineChartData();

                        }

                        private void UpdateLineChartData()

                        {

                            lbl.Text = DateTime.Now.ToString();

                            //Series 1

                            string strCmd = "SELECT time,value "

                                            + "FROM [piarchive].[picomp2] "

                                            + "WHERE tag = 'sinusoid' "

                                            + "AND time BETWEEN '*-24h' AND '*'";

                            OleDbDataAdapter oledbDA = new OleDbDataAdapter(strCmd, strConPI);

                            oledbDA.Fill(dt);

                            if (dt != null && dt.Rows.Count > 0)

                            {

                                //dt.DefaultView.Sort = "hour";

                                //dv = dt.DefaultView;

                                x = new string[dt.Rows.Count];

                                y = new int[dt.Rows.Count];

                                for (int i = 0; i < dt.Rows.Count; i++)

                                {

                                    if (!string.IsNullOrEmpty(dt.Rows[i][0].ToString()))

                                    {

                                        //string[] strSplitDate = dt.Rows[i][0].ToString().Split(' ');

                                        //string[] strSplitTime = strSplitDate[1].ToString().Split(':');

                                        //x[i] = strSplitTime[0];

                                        x[i] = Convert.ToDateTime(dt.Rows[i][0].ToString()).ToString("hh");

                                        x[i] = x[i] + ":00";

                                    }

                 

                                    if (!string.IsNullOrEmpty(dt.Rows[i][1].ToString()))

                                        y[i] = Convert.ToInt32(dt.Rows[i][1]);

                 

                                }

                               

                                //Chart1.Series["Series1"].Points.DataBindXY(dv, "hour", dv, "value");

                            }

                            //Series 2

                            string strCmd_2 = "SELECT time,value "

                                    + "FROM [piarchive].[picomp2] "

                                    + "WHERE tag = 'cdt158' "

                                    + "AND time BETWEEN '*-24h' AND '*'";

                            OleDbDataAdapter oledbDA_2 = new OleDbDataAdapter(strCmd_2, strConPI);

                            oledbDA_2.Fill(dt2);

                            if (dt2 != null && dt2.Rows.Count > 0)

                            {

                                //dt2.DefaultView.Sort = "hour";

                                dv_1 = dt2.DefaultView;

                                p = new string[dt2.Rows.Count];

                                q = new int[dt2.Rows.Count];

                                for (int i = 0; i < dt2.Rows.Count; i++)

                                {

                                    if (!string.IsNullOrEmpty(dt2.Rows[i][0].ToString()))

                                    {

                                        //string[] strSplitDate_1 = dt2.Rows[i][0].ToString().Split(' ');

                                        //string[] strSplitTime_1 = strSplitDate_1[1].ToString().Split(':');

                                        //p[i] = strSplitTime_1[0];

                                        p[i] = Convert.ToDateTime(dt2.Rows[i][0].ToString()).ToString("hh");

                                        p[i] = p[i] + ":00";

                                    }

                 

                                    if (!string.IsNullOrEmpty(dt2.Rows[i][1].ToString()))

                                        q[i] = Convert.ToInt32(dt2.Rows[i][1]);

                 

                                }               

                                //Chart1.Series["Series2"].Points.DataBindXY(dv, "hour", dv, "value");

                            }

                            if (dt.Rows.Count > dt2.Rows.Count)

                                Chart1.ChartAreas[0].AxisX.Interval = Convert.ToInt32(dt.Rows.Count)/5;

                            else

                                Chart1.ChartAreas[0].AxisX.Interval = Convert.ToInt32(dt2.Rows.Count)/5;

                 

                            if (y != null && y.Length > 0)

                                Chart1.Series["Series1"].Points.DataBindXY(x, y);

                            if (q != null && q.Length > 0)

                                Chart1.Series["Series2"].Points.DataBindXY(p, q);

                 

                            //Chart1.Series["Series1"].Points.DataBindXY(dv, "hour", dv, "value");

                            //Chart1.Series["Series2"].Points.DataBindXY(dv_1, "hour", dv_1, "value");

                            //if (dt != null && dt.Rows.Count > 0)

                            //{

                            //    Chart1.Series["Series1"].Points.DataBind(dv, "hour", "value",null);             

                            //}

                            //if (dt2 != null && dt2.Rows.Count > 0)

                            //{

                            //    Chart1.Series["Series1"].Points.DataBind(dv_1, "hour", "value", null);

                            //}

                            //if (dt != null && dt.Rows.Count > 0)

                            //    Chart1.Series["Series1"].Points.DataBindXY(dt.Columns["time"].ToString(), dt.Columns["value"]);

                            //if (dt2 != null && dt2.Rows.Count > 0)

                            //    Chart1.Series["Series1"].Points.DataBindXY(dt.Columns["time"].ToString(),Conv);

                        }      

                    }

                }

                 

                Request you to provide solution.

                 

                thanks...

                • Re: Need to optimize the code which is used to bind the data to the asp.net microsoft chart line control using PI OLEDB Or Any other way
                  shekar

                  Hi Eugene,

                   

                  Yes I tested the query in SQL commander, the query is executing within 00:00:00.187 and it's varying within this seconds. But from web page the code is loading too slow for the first time and from the second time onwards the page is loading very fast.

                   

                  Also I have cleared the Datatable, but here my problem is X-axis time values are not getting double, when the first time page loads the 'X-axis' values are coming in ascending order after when page refreshes automatically 'X-axis' values are changing to normal way. How can I overcome this problem.

                   

                  thanks....

                  • Re: Need to optimize the code which is used to bind the data to the asp.net microsoft chart line control using PI OLEDB Or Any other way
                    shekar

                    Thanks Rhys Kirk for reply, but if the client wants to load this page in different pc's everytime he faces this same problem while loading. Is there any other possibility to overcome this problem.

                     

                    thanks...

                    • Re: Need to optimize the code which is used to bind the data to the asp.net microsoft chart line control using PI OLEDB Or Any other way
                      Rhys Kirk

                      Make a connection to the PI Server during your web application start event, then each client should negate the need for the initial conneciton and re-use the already open connection.