Pfthb

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