Response time is a bit longer

Question

1.00/5 (1 vote)

See more:

Below function i have which is working correctly. The only issue with is response time is a bit longer. Can anyone help with this

Thanks in adv

What I have tried:

VB.NET

Public Function GetMasterData_new(ByVal param_vessel As String, ByVal param_vesselType As String, ByVal param_voyageType As String, ByVal param_datefrom As String, ByVal param_dateto As String, ByVal location As String) As DataTable

        Dim result As String = "", voytype As String = "", DateBetween As String = "and L.complete_date <= convert (datetime,'" & param_dateto & "') and  F.seapassage_date >= convert (datetime, '" & param_datefrom & "')"
        Dim MasterData As New DataTable

        Dim dc1 As DataColumn = New DataColumn
        dc1.DataType = GetType(Integer)
        dc1.ColumnName = "ID"
        Dim dc2 As DataColumn = New DataColumn
        dc2.DataType = GetType(Date)
        dc2.ColumnName = "ReportDate"
        Dim dc3 As DataColumn = New DataColumn
        dc3.DataType = GetType(String)
        dc3.ColumnName = "VesselType"
        Dim dc4 As DataColumn = New DataColumn
        dc4.DataType = GetType(String)
        dc4.ColumnName = "VesselID"
        Dim dc5 As DataColumn = New DataColumn
        dc5.DataType = GetType(String)
        dc5.ColumnName = "VoyageID"
        Dim dc6 As DataColumn = New DataColumn
        dc6.DataType = GetType(String)
        dc6.ColumnName = "VoyageType"
        Dim dc7 As DataColumn = New DataColumn
        dc7.DataType = GetType(String)
        dc7.ColumnName = "ReportType"
        Dim dc8 As DataColumn = New DataColumn
        dc8.DataType = GetType(Double)
        dc8.ColumnName = "Days"
        Dim dc9 As DataColumn = New DataColumn
        dc9.DataType = GetType(Double)
        dc9.ColumnName = "TotalDistanceCovered"
        Dim dc10 As DataColumn = New DataColumn
        dc10.DataType = GetType(Double)
        dc10.ColumnName = "CargoOnBoard"
        Dim dc11 As DataColumn = New DataColumn
        dc11.DataType = GetType(Double)
        dc11.ColumnName = "Tot_IFO"
        Dim dc12 As DataColumn = New DataColumn
        dc12.DataType = GetType(Double)
        dc12.ColumnName = "Tot_IFOLS"
        Dim dc13 As DataColumn = New DataColumn
        dc13.DataType = GetType(Double)
        dc13.ColumnName = "Tot_MDO"
        Dim dc14 As DataColumn = New DataColumn
        dc14.DataType = GetType(Double)
        dc14.ColumnName = "Tot_MDOLS"
        Dim dc15 As DataColumn = New DataColumn
        dc15.DataType = GetType(String)
        dc15.ColumnName = "VesselName"
        Dim dc16 As DataColumn = New DataColumn
        dc16.DataType = GetType(Double)
        dc16.ColumnName = "avg_sulphur_ifo"
        Dim dc17 As DataColumn = New DataColumn
        dc17.DataType = GetType(Double)
        dc17.ColumnName = "avg_sulphur_mdo"
        Dim dc18 As DataColumn = New DataColumn
        dc18.DataType = GetType(Double)
        dc18.ColumnName = "avg_sulphur_ifols"
        Dim dc19 As DataColumn = New DataColumn
        dc19.DataType = GetType(Double)
        dc19.ColumnName = "avg_sulphur_mdols"
        Dim dc20 As DataColumn = New DataColumn
        dc20.DataType = GetType(Double)
        dc20.ColumnName = "CO2"
        Dim dc21 As DataColumn = New DataColumn
        dc21.DataType = GetType(Double)
        dc21.ColumnName = "EEOI"
        Dim dc22 As DataColumn = New DataColumn
        dc22.DataType = GetType(Double)
        dc22.ColumnName = "SOx"
        Dim dc23 As DataColumn = New DataColumn
        dc23.DataType = GetType(Double)
        dc23.ColumnName = "SOxEff"
        Dim dc24 As DataColumn = New DataColumn
        dc24.DataType = GetType(Double)
        dc24.ColumnName = "NOx"
        Dim dc25 As DataColumn = New DataColumn
        dc25.DataType = GetType(Double)
        dc25.ColumnName = "NOxEff"


        MasterData.Columns.Add(dc1)
        MasterData.Columns.Add(dc2)
        MasterData.Columns.Add(dc3)
        MasterData.Columns.Add(dc4)
        MasterData.Columns.Add(dc5)
        MasterData.Columns.Add(dc6)
        MasterData.Columns.Add(dc7)
        MasterData.Columns.Add(dc8)
        MasterData.Columns.Add(dc9)
        MasterData.Columns.Add(dc10)
        MasterData.Columns.Add(dc11)
        MasterData.Columns.Add(dc12)
        MasterData.Columns.Add(dc13)
        MasterData.Columns.Add(dc14)
        MasterData.Columns.Add(dc15)
        MasterData.Columns.Add(dc16)
        MasterData.Columns.Add(dc17)
        MasterData.Columns.Add(dc18)
        MasterData.Columns.Add(dc19)
        MasterData.Columns.Add(dc20)
        MasterData.Columns.Add(dc21)
        MasterData.Columns.Add(dc22)
        MasterData.Columns.Add(dc23)
        MasterData.Columns.Add(dc24)
        MasterData.Columns.Add(dc25)
        Dim VesselType As String = "", vesselID As String = "", VoyageID As String = "", voyageType = "", reportType As String = "", Days As Double = 0.0
        Dim TotalDistanceCovered As Double = 0.0, CargoOnBoard As Double = 0.0, Tot_IFO As Double = 0.0, Tot_IFOLS As Double = 0.0, Tot_MDO As Double = 0.0, Tot_MDOLS As Double = 0.0
        Dim count As Integer = 0
        Dim Tot_IFO1 As Double, Tot_IFOLS1 As Double, Tot_MDO1 As Double, Tot_MDOLS1 As Double, Tot_AvgSul_IFO1 As Double, Tot_AvgSul_IFOLS1 As Double, Tot_AvgSul_MDO1 As Double, Tot_AvgSul_MDOLS1 As Double, CO21 As Double, SOx1 As Double, NOx1 As Double, Tot_DistanceCovered1 As Double, CargoOnboard1 As Double

        Dim fromdate As Date = Date.Parse(param_datefrom)
        Dim Todate As Date = Date.Parse(param_dateto)
        param_vessel = param_vessel.TrimEnd(","c)
        Dim vesseltable As New DataTable
        vesseltable = SqlHelper.ReturnDataTable("Select v.vesselid,v.vesselname,vt.vessel_type from vessel v inner join tbl_vesselType vt On v.vesselID = vt.vesselid where v.vesselid in (" + param_vessel + ")")

        While (fromdate <= Todate)
            For Each vslrow As DataRow In vesseltable.Rows
                Dim NoonQuery As String
                Dim NoonTbl As DataTable
                NoonQuery = "select vesselid,voyage_id,steaming_time,distence_covered,consumed_aeifo,consumed_aemdo,consumed_aeifols,consumed_aemdols,tc_ifo,tc_mdo,tc_ifols,tc_mdols,inherting_ifo,inherting_mdo,inherting_ifols,inherting_mdols,drifting_ifo,drifting_mdo,drifting_ifols,drifting_mdols,other_ifo,other_mdo,other_ifols,other_mdols,ifo_ch,mdo_ch,ifols_ch,mdols_ch,txt_AE_ifo,txt_AE_mdo,txt_AE_ifols,txt_AE_mdols,local_date from tbl_noonreport where vesselid=" + vslrow("vesselid").ToString + " and convert(datetime,local_date) = convert(datetime,'" + fromdate + "')"
                NoonTbl = SqlHelper.ReturnDataTable(NoonQuery)

                If NoonTbl.Rows.Count > 0 Then

                    For Each noonrow As DataRow In NoonTbl.Rows
                        Dim dr As DataRow = MasterData.NewRow()
                        Dim FullawayData As DataTable
                        Dim EOSP_Data As DataTable
                        count = count + 1
                        FullawayData = GetFullAwayData(noonrow("voyage_id"), noonrow("vesselid"))
                        EOSP_Data = GetEOSP_Avg_SulphurData(noonrow("voyage_id"), noonrow("vesselid"))
                        dr("ID") = count
                        dr("VesselType") = vslrow("vessel_type").ToString
                        dr("VesselName") = vslrow("VesselName").ToString
                        dr("VesselID") = vslrow("vesselID").ToString
                        dr("VoyageID") = noonrow("voyage_id").ToString
                        dr("VoyageType") = FullawayData(0)("voyage_type").ToString
                        dr("ReportType") = "Noon Report"
                        dr("Days") = Math.Round(Convert.ToDouble(noonrow("steaming_time")), 2) / 24
                        Tot_DistanceCovered1 = Math.Round(Convert.ToDouble(Check_IsnullOREmpty(noonrow("distence_covered"))), 2)
                        dr("TotalDistanceCovered") = Tot_DistanceCovered1
                        CargoOnboard1 = Math.Round(Convert.ToDouble(Check_IsnullOREmpty(FullawayData(0)("total_cargo_onboard"))), 2)
                        dr("CargoOnBoard") = CargoOnboard1
                        Tot_IFO1 = Math.Round((Convert.ToDouble(Check_IsnullOREmpty(noonrow("consumed_aeifo"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("tc_ifo"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("inherting_ifo"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("drifting_ifo"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("other_ifo"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("ifo_ch"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("txt_AE_ifo")))), 2)
                        dr("Tot_IFO") = Tot_IFO1
                        Tot_IFOLS1 = Math.Round((Convert.ToDouble(Check_IsnullOREmpty(noonrow("consumed_aeifols"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("tc_ifols"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("inherting_ifols"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("drifting_ifols"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("other_ifols"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("ifols_ch"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("txt_AE_ifols")))), 2)
                        dr("Tot_IFOLS") = Tot_IFOLS1
                        Tot_MDO1 = Math.Round((Convert.ToDouble(Check_IsnullOREmpty(noonrow("consumed_aemdo"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("tc_mdo"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("inherting_mdo"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("drifting_mdo"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("other_mdo"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("mdo_ch"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("txt_AE_mdo")))), 2)
                        dr("Tot_MDO") = Tot_MDO1
                        Tot_MDOLS1 = Math.Round((Convert.ToDouble(Check_IsnullOREmpty(noonrow("consumed_aemdols"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("tc_mdols"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("inherting_mdols"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("drifting_mdols"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("other_mdols"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("mdols_ch"))) + Convert.ToDouble(Check_IsnullOREmpty(noonrow("txt_AE_mdols")))), 2)
                        dr("Tot_MDOLS") = Tot_MDOLS1
                        dr("ReportDate") = noonrow("local_date").ToString.ToString.Replace("-", "/")
                        Tot_AvgSul_IFO1 = Math.Round(Convert.ToDouble(Check_IsnullOREmpty(EOSP_Data(0)("avg_sulphur_ifo"))), 2)
                        If Tot_AvgSul_IFO1 = 0 Then
                            dr("avg_sulphur_ifo") = 3.3
                        Else
                            dr("avg_sulphur_ifo") = Tot_AvgSul_IFO1
                        End If

                        Tot_AvgSul_MDO1 = Math.Round(Convert.ToDouble(Check_IsnullOREmpty(EOSP_Data(0)("avg_sulphur_mdo"))), 2)
                        If Tot_AvgSul_MDO1 = 0 Then
                            dr("avg_sulphur_mdo") = 1.5
                        Else
                            dr("avg_sulphur_mdo") = Tot_AvgSul_MDO1
                        End If

                        Tot_AvgSul_IFOLS1 = Math.Round(Convert.ToDouble(Check_IsnullOREmpty(EOSP_Data(0)("avg_sulphur_ifols"))), 2)
                        If Tot_AvgSul_IFOLS1 = 0 Then
                            dr("avg_sulphur_ifols") = 0.4
                        Else
                            dr("avg_sulphur_ifols") = Tot_AvgSul_IFOLS1
                        End If

                        Tot_AvgSul_MDOLS1 = Math.Round(Convert.ToDouble(Check_IsnullOREmpty(EOSP_Data(0)("avg_sulphur_mdols"))), 2)

                        If Tot_AvgSul_MDOLS1 = 0 Then
                            dr("avg_sulphur_mdols") = 0.1
                        Else
                            dr("avg_sulphur_mdols") = Tot_AvgSul_MDOLS1
                        End If

                        CO21 = Get_CO2_Sox_Nox(Tot_IFO1, Tot_IFOLS1, Tot_MDO1, Tot_MDOLS1, Tot_AvgSul_IFO1, Tot_AvgSul_IFOLS1, Tot_AvgSul_MDO1, Tot_AvgSul_MDOLS1, "CO2")
                        dr("CO2") = CO21
                        dr("EEOI") = Math.Round((CO21 / (CargoOnboard1 * Tot_DistanceCovered1)) * 1000000, 2)
                        SOx1 = Get_CO2_Sox_Nox(Tot_IFO1, Tot_IFOLS1, Tot_MDO1, Tot_MDOLS1, Tot_AvgSul_IFO1, Tot_AvgSul_IFOLS1, Tot_AvgSul_MDO1, Tot_AvgSul_MDOLS1, "SOX")
                        dr("SOx") = SOx1
                        dr("SOxEff") = Math.Round((SOx1 * 1000000) / (CargoOnboard1 * Tot_DistanceCovered1), 2)
                        NOx1 = Get_CO2_Sox_Nox(Tot_IFO1, Tot_IFOLS1, Tot_MDO1, Tot_MDOLS1, Tot_AvgSul_IFO1, Tot_AvgSul_IFOLS1, Tot_AvgSul_MDO1, Tot_AvgSul_MDOLS1, "NOX")
                        dr("NOx") = NOx1
                        dr("NOxEff") = Math.Round((NOx1 * 1000000) / (CargoOnboard1 * Tot_DistanceCovered1), 2)

                        MasterData.Rows.Add(dr)
                    Next
                End If
                Dim EOSPQuery As String = "select voyage_id,vesselid,streaming_days,distance_streamed_last_noon,meae_ifo,meae_mdo,meae_ifols,meae_mdols,tank_cleaning_ifo_last_noon,tank_cleaning_mdo_last_noon,tank_cleaning_ifols_last_noon,tank_cleaning_mdols_last_noon,inherting_ifo_last_noon,inherting_mdo_last_noon,inherting_ifols_last_noon,inherting_mdols_last_noon,drifting_ifo_last_noon,drifting_mdo_last_noon,drifting_ifols_last_noon,drifting_mdols_last_noon,other_ifo,other_mdo,other_ifols,other_mdols,txt_NoonBCifo,txt_noonBCmdo,txt_noonBCifols,txt_noonBCmdols,lastnoonAE_ifo,lastnoonAE_mdo,lastnoonAE_ifols,lastnoonAE_mdols,date_sea_passage_end,avg_sulphur_ifo,avg_sulphur_mdo,avg_sulphur_ifols,avg_sulphur_mdols from tbl_eosparrival where vesselid =" + vslrow("vesselid").ToString + "  and convert(datetime,date_sea_passage_end) = convert(datetime,'" + fromdate + "')"
                Dim EOSPTbl As DataTable = SqlHelper.ReturnDataTable(EOSPQuery)
                If EOSPTbl.Rows.Count > 0 Then

                    For Each EOSPRow As DataRow In EOSPTbl.Rows
                        Dim dr As DataRow = MasterData.NewRow()
                        count = count + 1

                        Dim FullawayData As DataTable
                        FullawayData = GetFullAwayData(EOSPRow("voyage_id"), EOSPRow("vesselid"))
                        dr("ID") = count
                        dr("VesselType") = vslrow("vessel_type")
                        dr("VesselName") = vslrow("VesselName")
                        dr("VesselID") = vslrow("vesselID")
                        dr("VoyageID") = EOSPRow("voyage_id")
                        dr("VoyageType") = FullawayData(0)("voyage_type")
                        dr("ReportType") = "EOSP"
                        dr("Days") = Math.Round((Convert.ToDouble(EOSPRow("streaming_days").ToString) / 24), 2)
                        Tot_DistanceCovered1 = Math.Round(Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("distance_streamed_last_noon"))), 2)
                        dr("TotalDistanceCovered") = Tot_DistanceCovered1
                        CargoOnboard1 = Math.Round(Convert.ToDouble(Check_IsnullOREmpty(FullawayData(0)("total_cargo_onboard"))), 2)
                        dr("CargoOnBoard") = CargoOnboard1
                        Tot_IFO1 = Math.Round((Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("meae_ifo"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("tank_cleaning_ifo_last_noon"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("inherting_ifo_last_noon"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("drifting_ifo_last_noon"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("other_ifo"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("txt_NoonBCifo"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("lastnoonAE_ifo")))), 2)
                        dr("Tot_IFO") = Tot_IFO1
                        Tot_IFOLS1 = Math.Round((Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("meae_ifols"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("tank_cleaning_ifols_last_noon"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("inherting_ifols_last_noon"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("drifting_ifols_last_noon"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("other_ifols"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("txt_NoonBCifols"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("lastnoonAE_ifols")))), 2)
                        dr("Tot_IFOLS") = Tot_IFOLS1
                        Tot_MDO1 = Math.Round((Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("meae_mdo"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("tank_cleaning_mdo_last_noon"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("inherting_mdo_last_noon"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("drifting_mdo_last_noon"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("other_mdo"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("txt_NoonBCmdo"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("lastnoonAE_mdo")))), 2)
                        dr("Tot_MDO") = Tot_MDO1
                        Tot_MDOLS1 = Math.Round((Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("meae_mdols"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("tank_cleaning_mdols_last_noon"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("inherting_mdols_last_noon"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("drifting_mdols_last_noon"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("other_mdols"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("txt_NoonBCmdols"))) + Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("lastnoonAE_mdols")))), 2)
                        dr("Tot_MDOLS") = Tot_MDOLS1
                        dr("ReportDate") = EOSPRow("date_sea_passage_end").ToString.ToString.Replace("-", "/")
                        Tot_AvgSul_IFO1 = Math.Round(Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("avg_sulphur_ifo"))), 2)
                        If Tot_AvgSul_IFO1 = 0 Then
                            dr("avg_sulphur_ifo") = 3.3
                        Else
                            dr("avg_sulphur_ifo") = Tot_AvgSul_IFO1
                        End If

                        Tot_AvgSul_MDO1 = Math.Round(Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("avg_sulphur_mdo"))), 2)
                        If Tot_AvgSul_MDO1 = 0 Then
                            dr("avg_sulphur_mdo") = 1.5
                        Else
                            dr("avg_sulphur_mdo") = Tot_AvgSul_MDO1
                        End If

                        Tot_AvgSul_IFOLS1 = Math.Round(Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("avg_sulphur_ifols"))), 2)
                        If Tot_AvgSul_IFOLS1 = 0 Then
                            dr("avg_sulphur_ifols") = 0.4
                        Else
                            dr("avg_sulphur_ifols") = Tot_AvgSul_IFOLS1
                        End If

                        Tot_AvgSul_MDOLS1 = Math.Round(Convert.ToDouble(Check_IsnullOREmpty(EOSPRow("avg_sulphur_mdols"))), 2)

                        If Tot_AvgSul_MDOLS1 = 0 Then
                            dr("avg_sulphur_mdols") = 0.1
                        Else
                            dr("avg_sulphur_mdols") = Tot_AvgSul_MDOLS1
                        End If
                        CO21 = Get_CO2_Sox_Nox(Tot_IFO1, Tot_IFOLS1, Tot_MDO1, Tot_MDOLS1, Tot_AvgSul_IFO1, Tot_AvgSul_IFOLS1, Tot_AvgSul_MDO1, Tot_AvgSul_MDOLS1, "CO2")
                        dr("CO2") = CO21
                        dr("EEOI") = Math.Round((CO21 / (CargoOnboard1 * Tot_DistanceCovered1)) * 1000000, 2)
                        SOx1 = Get_CO2_Sox_Nox(Tot_IFO1, Tot_IFOLS1, Tot_MDO1, Tot_MDOLS1, Tot_AvgSul_IFO1, Tot_AvgSul_IFOLS1, Tot_AvgSul_MDO1, Tot_AvgSul_MDOLS1, "SOX")
                        dr("SOx") = SOx1
                        dr("SOxEff") = Math.Round(((SOx1 * 1000000) / (CargoOnboard1 * Tot_DistanceCovered1)), 2)
                        NOx1 = Get_CO2_Sox_Nox(Tot_IFO1, Tot_IFOLS1, Tot_MDO1, Tot_MDOLS1, Tot_AvgSul_IFO1, Tot_AvgSul_IFOLS1, Tot_AvgSul_MDO1, Tot_AvgSul_MDOLS1, "NOX")
                        dr("NOx") = NOx1
                        dr("NOxEff") = Math.Round(((NOx1 * 1000000) / (CargoOnboard1 * Tot_DistanceCovered1)), 2)
                        MasterData.Rows.Add(dr)
                    Next

                End If
                Dim IDLE_Report As String = "select record_id,vesselid,voyage_id,ME_ifo,ME_mdo,ME_ifols,ME_mdols,AE_ifo,AE_mdo,AE_ifols,AE_mdols,TC_ifo,TC_mdo,TC_ifols,TC_mdols,Inherting_ifo,Inherting_mdo,Inherting_ifols,Inherting_mdols,Heating_ifo,Heating_mdo,Heating_ifols,Heating_mdols,other_ifo,other_mdo,other_ifols,other_mdols, port_date,port_time  from tbl_idlereport where vesselid =" + vslrow("vesselid").ToString + "  and convert(datetime,port_date) = convert(datetime,'" + fromdate + "')"
                Dim IDLE_tbl As DataTable = SqlHelper.ReturnDataTable(IDLE_Report)
                If IDLE_tbl.Rows.Count > 0 Then
                    Dim flg As Integer = 0

                    Dim PortDays As String = ""
                    For Each IDLERow As DataRow In IDLE_tbl.Rows
                        Dim dr As DataRow = MasterData.NewRow()
                        count = count + 1
                        Dim FullawayData As DataTable
                        Dim EOSP_Data As DataTable
                        Dim Pre_PortData As DataTable



                        FullawayData = GetFullAwayData(IDLERow("voyage_id"), IDLERow("vesselid"))
                        EOSP_Data = GetEOSP_Avg_SulphurData(IDLERow("voyage_id"), IDLERow("vesselid"))
                        Dim query As String = "select top 1 port_date,port_time from tbl_idlereport where voyage_id =" + IDLERow("voyage_id").ToString() + "  and vesselid=" + IDLERow("vesselid").ToString() + " and convert(datetime,port_date) < convert(datetime,'" + fromdate + "') order by convert(datetime,port_date) desc "
                        Pre_PortData = SqlHelper.ReturnDataTable(query)
                        If Pre_PortData.Rows.Count > 0 Then
                            Dim pre_datetime As DateTime = DateTime.ParseExact(Pre_PortData(0)("port_date").ToString.Replace("-", "/") + " " + Pre_PortData(0)("port_time"), "MM/dd/yyyy HH:mm", Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy HH:mm")
                            Dim pdays As DateTime = DateTime.ParseExact(IDLERow("Port_Date").ToString.Replace("-", "/") + " " + IDLERow("Port_time"), "MM/dd/yyyy HH:mm", Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy HH:mm")
                            PortDays = (pdays - pre_datetime).TotalHours.ToString
                        Else
                            Dim eo_datetime As DateTime = DateTime.ParseExact(EOSP_Data(0)("Date_sea_passage_end").ToString.Replace("-", "/") + " " + EOSP_Data(0)("Time_sea_passage_end").ToString, "MM/dd/yyyy HH:mm", Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy HH:mm")
                            Dim pdays As DateTime = DateTime.ParseExact(IDLERow("Port_Date").ToString.Replace("-", "/") + " " + IDLERow("Port_time"), "MM/dd/yyyy HH:mm", Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy HH:mm")
                            PortDays = (pdays - eo_datetime).TotalHours.ToString
                        End If

                        dr("ID") = count
                        dr("VesselType") = vslrow("vessel_type")
                        dr("VesselName") = vslrow("VesselName")
                        dr("VesselID") = vslrow("vesselID")
                        dr("VoyageID") = IDLERow("voyage_id")
                        dr("VoyageType") = FullawayData(0)("voyage_type")
                        dr("ReportType") = "IDLE"
                        dr("Days") = Math.Round((Convert.ToDouble(PortDays) / 24), 2)
                        dr("TotalDistanceCovered") = 0.0
                        dr("CargoOnBoard") = 0.0

                        dr("Tot_IFO") = Math.Round((Convert.ToDouble(Check_IsnullOREmpty(IDLERow("ME_ifo"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("AE_ifo"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("TC_ifo"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("Inherting_ifo"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("Heating_ifo"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("other_ifo")))), 2)
                        dr("Tot_IFOLS") = Math.Round((Convert.ToDouble(Check_IsnullOREmpty(IDLERow("ME_ifols"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("AE_ifols"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("TC_ifols"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("Inherting_ifols"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("Heating_ifols"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("other_ifols")))), 2)
                        dr("Tot_MDO") = Math.Round((Convert.ToDouble(Check_IsnullOREmpty(IDLERow("ME_mdo"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("AE_mdo"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("TC_mdo"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("Inherting_mdo"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("Heating_mdo"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("other_mdo")))), 2)
                        dr("Tot_MDOLS") = Math.Round((Convert.ToDouble(Check_IsnullOREmpty(IDLERow("ME_mdols"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("AE_mdols"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("TC_mdols"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("Inherting_mdols"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("Heating_mdols"))) + Convert.ToDouble(Check_IsnullOREmpty(IDLERow("other_mdols")))), 2)
                        dr("ReportDate") = IDLERow("port_date").ToString.Replace("-", "/")

                        Tot_AvgSul_IFO1 = Math.Round(Convert.ToDouble(Check_IsnullOREmpty(EOSP_Data(0)("avg_sulphur_ifo"))), 2)
                        If Tot_AvgSul_IFO1 = 0 Then
                            dr("avg_sulphur_ifo") = 3.3
                        Else
                            dr("avg_sulphur_ifo") = Tot_AvgSul_IFO1
                        End If

                        Tot_AvgSul_MDO1 = Math.Round(Convert.ToDouble(Check_IsnullOREmpty(EOSP_Data(0)("avg_sulphur_mdo"))), 2)
                        If Tot_AvgSul_MDO1 = 0 Then
                            dr("avg_sulphur_mdo") = 1.5
                        Else
                            dr("avg_sulphur_mdo") = Tot_AvgSul_MDO1
                        End If

                        Tot_AvgSul_IFOLS1 = Math.Round(Convert.ToDouble(Check_IsnullOREmpty(EOSP_Data(0)("avg_sulphur_ifols"))), 2)
                        If Tot_AvgSul_IFOLS1 = 0 Then
                            dr("avg_sulphur_ifols") = 0.4
                        Else
                            dr("avg_sulphur_ifols") = Tot_AvgSul_IFOLS1
                        End If

                        Tot_AvgSul_MDOLS1 = Math.Round(Convert.ToDouble(Check_IsnullOREmpty(EOSP_Data(0)("avg_sulphur_mdols"))), 2)

                        If Tot_AvgSul_MDOLS1 = 0 Then
                            dr("avg_sulphur_mdols") = 0.1
                        Else
                            dr("avg_sulphur_mdols") = Tot_AvgSul_MDOLS1
                        End If
                        CO21 = Get_CO2_Sox_Nox(Tot_IFO1, Tot_IFOLS1, Tot_MDO1, Tot_MDOLS1, Tot_AvgSul_IFO1, Tot_AvgSul_IFOLS1, Tot_AvgSul_MDO1, Tot_AvgSul_MDOLS1, "CO2")
                        dr("CO2") = CO21
                        dr("EEOI") = 0
                        SOx1 = Get_CO2_Sox_Nox(Tot_IFO1, Tot_IFOLS1, Tot_MDO1, Tot_MDOLS1, Tot_AvgSul_IFO1, Tot_AvgSul_IFOLS1, Tot_AvgSul_MDO1, Tot_AvgSul_MDOLS1, "SOX")
                        dr("SOx") = SOx1
                        dr("SOxEff") = 0
                        NOx1 = Get_CO2_Sox_Nox(Tot_IFO1, Tot_IFOLS1, Tot_MDO1, Tot_MDOLS1, Tot_AvgSul_IFO1, Tot_AvgSul_IFOLS1, Tot_AvgSul_MDO1, Tot_AvgSul_MDOLS1, "NOX")
                        dr("NOx") = NOx1
                        dr("NOxEff") = 0

                        MasterData.Rows.Add(dr)
                    Next

                End If
            Next
            fromdate = fromdate.AddDays(1)
        End While


        Return MasterData
    End Function

Posted 7-Apr-16 2:18am

Rajeev Raj

Updated 12-Apr-16 23:53pm

Add a Solution

Comments

Thanks7872 7-Apr-16 8:21am

You are lucky enough that you have only 'bit' longer response!You need to refactor your code.

phil.o 7-Apr-16 8:28am

Longer than what?

Rajeev Raj 7-Apr-16 8:37am

i mean its taking around 1 or 1.5 mins

phil.o 7-Apr-16 8:47am

The time it takes could be because of your spaghetti code, or because there are some missing indices in your database, or because there really are a lot of records to be retrieved, or a combination of two or more of these reasons.
Try to separate your method into several groups that you will be able to benchmark individually with Stopwatches; when you will have identified where most of the time is spent, then you will be able to know what to change.

F-ES Sitecore 7-Apr-16 9:41am

Without access to your database it's impossible to answer this. Do what phil.o suggested and benchmark individual aspects to see what is performing badly, also use tools like SQL Profiler to examine the sql statements being executed, look at their execution plans, see if indexes will help you etc etc. Performance tuning database access is a big job and it's rarely the code that's the issue so your code dump doesn't really help, it's more likely your data.

Ivan_FM 8-Apr-16 14:16pm

Agree with the previous comments, and let me add one more thing.

Avoid the "dim" inside a for-next statement, it will save a lot of time of response.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

VPSingh malhi · Accepted Answer · 2016-04-12T23:53:00

It too long code to execute for each request. Try to create simplified objects a
for each database query.Creating a datatable on the go retrieve DataTable from database as
an object and manipulate that DataTable as per requirement. After looking at your queries
it seems your database needs to be normalized to get better efficiency and quick response.

Please see the code part

VB

 While (fromdate <= Todate)
            For Each vslrow As DataRow In vesseltable.Rows
                Dim NoonQuery As String
                Dim NoonTbl As DataTable
               ______________________________
_____________________________________________________-
______________________________________________________
..........................................................


................................................................

          Next
            fromdate = fromdate.AddDays(1)
   End While

this part shows database design needs more care. A single query can retrieve whole database
so why query in loop? A single hit at server takes some specific time. If a single query
is hitting 1000 times the response delay will increase exponentially.

So my suggestion is to re-design database and a single query should bring up a DataSet and
then play with dataset in memory. It needs more RAM but page can be loaded
within 5 seconds.