响应时间稍长 [英] Response time is a bit longer
问题描述
我正在使用的功能正常。唯一的问题是响应时间有点长。任何人都可以帮忙这个
感谢您提供
我尝试过:
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:
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
推荐答案
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
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
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.
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.
这篇关于响应时间稍长的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!