Ndtyjky

I have a web page that can create multiple pie charts based on an SQL query that executes when the page is loading. However I have noticed that the page loading takes too long (around 30 seconds). I checked the SQL formula and can confirm that it is working fine (results given almost instantly).

There are 40 different pie charts the page needs to create, and the page also have the option to generate a single pie chart and that works fast enough.

So I can deduce that it's the pie chart generation that is slow when it needs to create 40 of them. Is there a way to help speed up the pie chart creation process?

Here is my code for reference.

 Sub draw_chart1() 'All Machines



    Dim check1, check2, fi, cnt



    Dim sql

    Dim myConnectionString As String = "Provider=SQLOLEDB;" & SQLDB_pp.ConnectionString

    fi = 0

    cnt = 0



    PlaceHolder1.Dispose()

    PlaceHolder2.Dispose()

    'Get all model

    Dim query1 As String = String.Format("SELECT DISTINCT A.MacID FROM dbo.tblMachine A LEFT JOIN  dbo.tblDataHdr b on a.MacID = b.MacID where DayID between '" & TextBox1.Text & "' AND '" & TextBox3.Text & "' ORDER BY A.MACID")

    Dim dt1 As DataTable = GetData(query1)

    For Q As Integer = 0 To dt1.Rows.Count - 1

        'For Q As Integer = 0 To 1

        check1 = dt1.Rows(Q)(0).ToString()



'This is the slow query

        Dim query As String = String.Format("SELECT Z.MacID, Z.EventName, ISNULL(DIFF,0) AS DIFF FROM (select distinct MacID, EventName from dbo.tblMachine a  join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur where EventName <> 'ON'  ) b on b.EventName <> ''  and MacID in ('" & check1 & "') ) Z LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A LEFT JOIN ( SELECT MacID, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff FROM  dbo.tblEvtDur  WHERE DayID between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  GROUP BY MacID ) B ON  A.MacID = B.MacID WHERE DayID between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  AND A.MacID in ('" & check1 & "') group by A.MacID, A.EventName) a ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName")



        Dim mychart As Chart = New Chart

        ' Dim myplace As PlaceHolder = New PlaceHolder

        Dim ChartArea1 As ChartArea = New ChartArea

        Dim Legend1 As Legend = New Legend



        Dim dt As DataTable = GetData(query)

        Dim x As String() = New String(dt.Rows.Count - 1) {}

        Dim y As Integer() = New Integer(dt.Rows.Count - 1) {}

        For i As Integer = 0 To dt.Rows.Count - 1



            x(i) = dt.Rows(i)(1).ToString()

            ' y(i) = dt.Rows(i)(2).ToString()

            y(i) = Convert.ToInt32(dt.Rows(i)(2))

        Next



        'Dim myConnection As New OleDbConnection(myConnectionString)

        'Dim myCommand As New OleDbCommand(sql, myConnection)

        'mychart.Width = Unit.Pixel(Session("sw") - 100)

        'mychart.Height = Unit.Pixel((Session("sh") / 2) - 88)



        mychart.Width = 600

        mychart.Height = 400



        mychart.ChartAreas.Clear()

        mychart.ChartAreas.Add("ChartArea1")



        mychart.Series.Clear()

        mychart.Series.Add(0)

        mychart.Series(0).Points.DataBindXY(x, y)



        mychart.Titles.Clear()

        mychart.Titles.Add("[" & Q + 1 & "] " & check1.ToString.ToUpper)

        mychart.Titles(0).Font = New System.Drawing.Font("Tahoma", 12, System.Drawing.FontStyle.Bold)

        mychart.Titles(0).BackColor = Color.PaleTurquoise

        mychart.Titles(0).ForeColor = Color.Black



        mychart.Series(0).ChartType = SeriesChartType.Pie

        '  mychart.Series(0).Points.DataBindXY(x, y)

        mychart.Series(0).LegendText = "#VALX"

        mychart.Series(0)("BarLabelStyle") = "Center"

        mychart.Series(0)("pointWidth") = "1"

        mychart.Series(0).BorderDashStyle = ChartDashStyle.Solid

        mychart.Series(0).BorderWidth = 2

        mychart.Series(0).Label = "#PERCENT"

        mychart.Series(0).ShadowColor = Color.Gray

        mychart.Series(0).ShadowOffset = 10



        mychart.Series(0).LabelBackColor = Drawing.Color.Cornsilk

        mychart.Series(0).Font = New Font("Tahoma", 9, FontStyle.Bold)



        'Chart1.Series(0).LabelToolTip = "#LABEL Percent: #PERCENT"

        mychart.Series(0).LegendToolTip = "#VALX - #PERCENT"

        mychart.Series(0).ToolTip = "#VALX - #PERCENT"



        mychart.ChartAreas("ChartArea1").Area3DStyle.Enable3D = True



        mychart.Series(0).CustomProperties = "DrawingStyle=LightToDark"

        'new

        Chart1.Series(0).CustomProperties = "PieLabelStyle=Outside"



        mychart.ChartAreas("ChartArea1").BorderDashStyle = BorderStyle.Solid

        mychart.Palette = ChartColorPalette.None

        mychart.Series(0).BorderDashStyle = ChartDashStyle.Solid

        mychart.Series(0).BorderWidth = 2

        mychart.Series(0).BorderColor = Color.Black



        mychart.PaletteCustomColors = {Drawing.Color.Black, Drawing.Color.LightGray, Drawing.Color.Blue, Drawing.Color.Yellow, Drawing.Color.Red, Drawing.Color.Orange, Drawing.Color.Green}



        mychart.Legends.Clear()

        mychart.Legends.Add(0)

        'Chart1.Legends(0).Enabled = True

        ''Chart1.Legends(0).BackColor = Drawing.Color.LightGreenplace

        mychart.Legends(0).Font = New Font("Tahoma", 10, FontStyle.Bold)

        mychart.Legends(0).Docking = System.Web.UI.DataVisualization.Charting.Docking.Bottom

        'Chart1.Legends(0).Alignment = Drawing.StringAlignment.Center

        'Chart1.Legends(0).BackColor = System.Drawing.Color.Transparent



        mychart.DataBind()



        'myplace.Visible = True

        If (Q + 1) Mod 2 <> 0 Then

            PlaceHolder1.Controls.Add(mychart)

            ' Dim spacer As LiteralControl = New LiteralControl("<p />")

            ' PlaceHolder1.Controls.Add(spacer)

        End If

        'Exit For

        If (Q + 1) Mod 2 = 0 Then



            PlaceHolder2.Controls.Add(mychart)

            'Dim spacer As LiteralControl = New LiteralControl("<p />")

            ' PlaceHolder2.Controls.Add(spacer)



        End If



    Next

End Sub