Hola mi codigo se demora 3-4 min en ejecutarse lo cual es demasiado, supongo yo no se optimizarlo, ojala alguien me pueda dar algunos tips respecto al codigo solamente, la BD es SQL SERVER COMPACT 3.5 las tablas ya tienen indices.
Código:
Dim sql As String = "SELECT RECIPE_FOOD.NDB_No_Food, RECIPE_FOOD.Nutr_Val FROM RECIPE_FOOD WHERE RECIPE_FOOD.NDB_No_Recipe = " & CodigoReceta
Dim cmd As SqlCeCommand = New SqlCeCommand(sql, conexionSql)
cmd.CommandType = CommandType.Text 'tipo comando text = sentencia sql
Try
Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)
If rs.HasRows Then
Do While rs.Read()
'POR CADA INGREDIENTE
Dim CodigoIngrediente As String = rs.GetOrdinal("NDB_No_Food")
Dim CanIngrediente As Decimal = rs.GetOrdinal("Nutr_Val")
'Codigo y cantidad del ingrediente
CodigoIngrediente = rs.GetString(CodigoIngrediente)
CanIngrediente = rs.GetDecimal(CanIngrediente)
Dim GramosIngrediente As Decimal = (CanIngrediente * 100) / CanTotalIngrediente
Dim sql2 As String = "SELECT Nutr_No, Units, NutrDesc, SR_Order FROM NUTR_DEF"
Dim cmd2 As SqlCeCommand = New SqlCeCommand(sql2, conexionSql)
cmd2.CommandType = CommandType.Text 'tipo comando text = sentencia sql
Dim rs2 As SqlCeResultSet = cmd2.ExecuteResultSet(ResultSetOptions.Scrollable)
If rs2.HasRows Then
Do While rs2.Read()
Dim Nutr_No As String = rs2.GetOrdinal("Nutr_No")
Dim Units As String = rs2.GetOrdinal("Units")
Dim NutrDesc As String = rs2.GetOrdinal("NutrDesc")
Dim SR_Order As String = rs2.GetOrdinal("SR_Order")
'datos del nutriente
Nutr_No = rs2.GetString(Nutr_No)
Units = rs2.GetString(Units)
NutrDesc = rs2.GetString(NutrDesc)
SR_Order = rs2.GetFloat(SR_Order)
Energia = (mIngredientes.InformacionNutriente(CodigoIngrediente, Nutr_No) * GramosIngrediente) / 100
EnergiaTotal = EnergiaTotal + Energia
Loop
End If
rs2.Close()
Loop
End If
MessageBox.Show(x)
Catch sqlexception As SqlCeException
MessageBox.Show(sqlexception.Message, "Error:", MessageBoxButtons.OK, MessageBoxIcon.Error)
Catch ex As Exception
MessageBox.Show(ex.Message, "Error:", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
mSqlServerCe.Desconectar()
End Try
Gracias por cualquier sugerencia.