none
Guardar registros en tabla generados previamente con una consulta en VB.NET RRS feed

  • Pregunta

  • Hola, necesito de ayuda si este código fuente esta bien estructurado porque me genera los resultados muy lento,

    o si hay una forma mas fácil y sencilla de hacerlo. 

    estoy trabajando en MYSQL y VB.NET

    Private Sub bgwComprasFH_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles bgwFH.DoWork
            TrucateFallas_ProductosFH()
            GenerarProductosEnFallasFH()
        End Sub
        Private Sub bgwComprasFH_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bgwFH.RunWorkerCompleted
            bgwFH.CancelAsync()
            bgwFH.Dispose()
            Me.Close()
        End Sub

    Private Sub GenerarProductosEnFallasFH()
            Dim dtProductos As DataTable
            Dim adaptador As MySqlDataAdapter
            Dim consulta As String

            Try
                conectarFH()
                conexionFH.Open()
                consulta = ("SELECT productos.codprod,productos.nombre,productos.tipoproducto,productos.stock,productos.sublinea FROM productos,facturas_dat WHERE productos.codprod=facturas_dat.codprod AND (facturas_dat.fecha BETWEEN DATE_ADD(CURDATE(), INTERVAL -30 DAY) AND CURDATE()) AND productos.codtipoproducto=1 GROUP BY facturas_dat.codprod ORDER BY productos.codprod ASC")
                adaptador = New MySqlDataAdapter(consulta, conexionFH)
                dtProductos = New DataTable
                adaptador.Fill(dtProductos)
                conexionFH.Close()

                Dim dtProductosEnFallas As New DataTable("fallas_inventario")
                dtProductosEnFallas.Rows.Clear()
                dtProductosEnFallas.Columns.Clear()

                dtProductosEnFallas.Columns.Add(New DataColumn("codprod", Type.GetType("System.Int32")))
                dtProductosEnFallas.Columns.Add(New DataColumn("nombre", Type.GetType("System.String")))
                dtProductosEnFallas.Columns.Add(New DataColumn("tipo", Type.GetType("System.String")))
                dtProductosEnFallas.Columns.Add(New DataColumn("stock", Type.GetType("System.Int32")))
                dtProductosEnFallas.Columns.Add(New DataColumn("costo", Type.GetType("System.Decimal")))
                dtProductosEnFallas.Columns.Add(New DataColumn("precio", Type.GetType("System.Decimal")))
                dtProductosEnFallas.Columns.Add(New DataColumn("venta_dias", Type.GetType("System.Decimal")))
                dtProductosEnFallas.Columns.Add(New DataColumn("promedio_venta", Type.GetType("System.Decimal")))
                dtProductosEnFallas.Columns.Add(New DataColumn("sobre_stock", Type.GetType("System.Decimal")))
                dtProductosEnFallas.Columns.Add(New DataColumn("farmacia", Type.GetType("System.String")))
                dtProductosEnFallas.Columns.Add(New DataColumn("codsublinea", Type.GetType("System.Int32")))
                dtProductosEnFallas.Columns.Add(New DataColumn("sublinea", Type.GetType("System.String")))
                dtProductosEnFallas.Columns.Add(New DataColumn("codproveedor", Type.GetType("System.Int32")))
                dtProductosEnFallas.Columns.Add(New DataColumn("proveedor", Type.GetType("System.String")))
                dtProductosEnFallas.Columns.Add(New DataColumn("generico", Type.GetType("System.String")))
                dtProductosEnFallas.Columns.Add(New DataColumn("ventas", Type.GetType("System.String")))

                For Each Producto As DataRow In dtProductos.Rows
                    Dim codprod As String = Producto("codprod").ToString
                    Dim MysqlConexion As New MySqlConnection
                    Dim MysqlCommand As New MySqlCommand
                    Dim dr As MySqlDataReader
                    Dim MySql As String
                    conectarFH()
                    conexionFH.Open()
                    MysqlCommand.Connection = conexionFH
                    MysqlCommand.CommandType = CommandType.Text
                    MySql = "SELECT fe.codprod AS CODIGO,fe.nombre AS NOMBRE,IF(fe.tipo = 1,'Farma','MiniM') AS TIPO,ROUND(fe.stock,0) AS STOCK,fe.costoactual AS COSTO,fe.precioactual AS PRECIO,IF(fe.ventas_dias IS NULL,0,fe.ventas_dias) AS VENTAS_DIAS,IF(ROUND(fe.ventas_dias/COUNT(stock_x_dia),2)IS NULL,0,ROUND(fe.ventas_dias/COUNT(stock_x_dia),2)) AS PROMEDIO_VENTA,ROUND((fe.stock-((IF(ROUND(fe.ventas_dias/COUNT(stock_x_dia),2)IS NULL,0,ROUND(fe.ventas_dias/COUNT(stock_x_dia),2)))* 15 )),2) AS SOBRE_STOCK,IF(fe.codsublinea IS NULL,0,fe.codsublinea) AS CODSUBLINEA,IF(fe.sublinea IS NULL,'SIN SUBLINEA',fe.sublinea) AS SUBLINEA,IF(fe.procodigo IS NULL,0,fe.procodigo) AS CODPROVEEDOR,IF(fe.proveedor IS NULL,'SIN PROVEEDOR',fe.proveedor) AS PROVEEDOR,fe.generico AS GENERICO FROM (SELECT productos.generico AS generico,almacen.stock AS stock,productos.codtipoproducto AS tipo,productos.codsublinea AS codsublinea,productos.sublinea AS sublinea,comprageneral.procodigo AS procodigo,comprageneral.proveedor AS proveedor,productos.codprod AS codprod,productos.nombre AS nombre,productos.stock AS stock_actual,productos.costo AS costoactual,productos.precio AS precioactual,SUM(ventas.venta)AS ventas_dias,(SELECT SUM(k.entradas-k.salidas) AS stock FROM kardex k WHERE k.fecha <= rango_fecha.fecha AND k.codprod=productos.codprod) AS stock_x_dia FROM productos LEFT JOIN (SELECT SUM(facturas_dat.cantidad) AS venta,facturas_dat.codprod FROM facturas_dat,facturas WHERE facturas_dat.codfact=facturas.keycodigo AND facturas.codgrupo <> 7 AND (facturas_dat.fecha BETWEEN DATE_ADD(CURDATE(), INTERVAL -14 DAY) AND CURDATE()) GROUP BY facturas_dat.codprod,facturas_dat.fecha) AS ventas ON productos.codprod = ventas.codprod LEFT JOIN (SELECT keycodigo, cierre, MAX(cierre), codprod, costo, cantidad, procodigo,proveedor FROM compras GROUP BY codprod DESC) AS comprageneral ON productos.codprod = comprageneral.codprod, (SELECT kardex.fecha FROM kardex WHERE (fecha BETWEEN DATE_ADD(CURDATE(), INTERVAL -14 DAY) AND CURDATE()) GROUP BY fecha) AS rango_fecha,(SELECT codprod,SUM(stock) AS stock FROM prodalma WHERE codalma IN(1,3,4,7) GROUP BY codprod) AS almacen WHERE productos.codprod=almacen.codprod AND productos.codprod=" & codprod & " GROUP BY productos.codprod,rango_fecha.fecha HAVING stock_x_dia > 0) AS fe GROUP BY fe.codprod ORDER BY PROMEDIO_VENTA DESC"
                    MysqlCommand.CommandText = MySql
                    dr = MysqlCommand.ExecuteReader
                    If dr.Read Then
                        Dim CODIGO As String = Convert.ToString(dr("CODIGO"))
                        Dim NOMBRE As String = Convert.ToString(dr("NOMBRE"))
                        Dim TIPO As String = Convert.ToString(dr("TIPO"))
                        Dim STOCK As Integer = Convert.ToInt32(dr("STOCK"))
                        Dim COSTO As String = Convert.ToString(dr("COSTO"))
                        Dim PRECIO As String = Convert.ToString(dr("PRECIO"))
                        Dim VENTAS_DIAS As String = Convert.ToString(dr("VENTAS_DIAS"))
                        Dim PROMEDIO_VENTA As String = Convert.ToString(dr("PROMEDIO_VENTA"))
                        Dim SOBRE_STOCK As String = Convert.ToString(dr("SOBRE_STOCK"))
                        Dim FARMACIA As String = "FH"
                        Dim CODSUBLINEA As String = Convert.ToString(dr("CODSUBLINEA"))
                        Dim SUBLINEA As String = Convert.ToString(dr("SUBLINEA"))
                        Dim CODPROVEEDOR As String = Convert.ToString(dr("CODPROVEEDOR"))
                        Dim PROVEEDOR As String = Convert.ToString(dr("PROVEEDOR"))
                        Dim GENERICO As String = Convert.ToString(dr("GENERICO"))
                        Dim VENTAS As String = 1
                        conexionFH.Close()

                        Dim Row As DataRow = dtProductosEnFallas.NewRow()
                        Row("codprod") = CODIGO
                        Row("nombre") = NOMBRE
                        Row("tipo") = TIPO
                        Row("stock") = STOCK
                        Row("costo") = COSTO
                        Row("precio") = PRECIO
                        Row("venta_dias") = VENTAS_DIAS
                        Row("promedio_venta") = PROMEDIO_VENTA
                        Row("sobre_stock") = SOBRE_STOCK
                        Row("farmacia") = FARMACIA
                        Row("codsublinea") = CODSUBLINEA
                        Row("sublinea") = SUBLINEA
                        Row("codproveedor") = CODPROVEEDOR
                        Row("proveedor") = PROVEEDOR
                        Row("generico") = GENERICO
                        Row("ventas") = VENTAS
                        dtProductosEnFallas.Rows.Add(Row)

                        Try
                            conectarSC_FH()
                            conexionSC_FH.Open()
                            Dim cm As MySqlCommand
                            Dim i As Integer
                            cm = New MySqlCommand("INSERT INTO fallas_inventario (fecha,codprod,nombre,tipo,stock,costo,precio,unidades_vendidas,promedio_venta,falla_sobrestock,farmacia,codsublinea,sublinea,codproveedor,proveedor,generico,ventas,registrado) VALUES (NOW(),?codprod,?nombre,?tipo,?stock,?costo,?precio,?unidades_vendidas,?promedio_venta,?falla_sobrestock,?farmacia,?codsublinea,?sublinea,?codproveedor,?proveedor,?generico,?ventas,NOW())")
                            cm.Parameters.Add("?codprod", MySqlDbType.Int24)
                            cm.Parameters.Add("?nombre", MySqlDbType.VarChar)
                            cm.Parameters.Add("?tipo", MySqlDbType.VarChar)
                            cm.Parameters.Add("?stock", MySqlDbType.Int24)
                            cm.Parameters.Add("?costo", MySqlDbType.Decimal)
                            cm.Parameters.Add("?precio", MySqlDbType.Decimal)
                            cm.Parameters.Add("?unidades_vendidas", MySqlDbType.Decimal)
                            cm.Parameters.Add("?promedio_venta", MySqlDbType.Decimal)
                            cm.Parameters.Add("?falla_sobrestock", MySqlDbType.Decimal)
                            cm.Parameters.Add("?farmacia", MySqlDbType.VarChar)
                            cm.Parameters.Add("?codsublinea", MySqlDbType.Int24)
                            cm.Parameters.Add("?sublinea", MySqlDbType.VarChar)
                            cm.Parameters.Add("?codproveedor", MySqlDbType.Int24)
                            cm.Parameters.Add("?proveedor", MySqlDbType.VarChar)
                            cm.Parameters.Add("?generico", MySqlDbType.VarChar)
                            cm.Parameters.Add("?ventas", MySqlDbType.TinyText)
                            cm.Connection = conexionSC_FH
                            For i = 0 To dtProductosEnFallas.Rows.Count - 1
                                cm.Parameters("?codprod").Value = dtProductosEnFallas.Rows(i)("codprod").ToString()
                                cm.Parameters("?nombre").Value = dtProductosEnFallas.Rows(i)("nombre").ToString()
                                cm.Parameters("?tipo").Value = dtProductosEnFallas.Rows(i)("tipo").ToString()
                                cm.Parameters("?stock").Value = dtProductosEnFallas.Rows(i)("stock").ToString()
                                cm.Parameters("?costo").Value = dtProductosEnFallas.Rows(i)("costo").ToString()
                                cm.Parameters("?precio").Value = dtProductosEnFallas.Rows(i)("precio").ToString()
                                cm.Parameters("?unidades_vendidas").Value = dtProductosEnFallas.Rows(i)("venta_dias").ToString()
                                cm.Parameters("?promedio_venta").Value = dtProductosEnFallas.Rows(i)("promedio_venta").ToString()
                                cm.Parameters("?falla_sobrestock").Value = dtProductosEnFallas.Rows(i)("sobre_stock").ToString()
                                cm.Parameters("?farmacia").Value = dtProductosEnFallas.Rows(i)("farmacia").ToString()
                                cm.Parameters("?codsublinea").Value = dtProductosEnFallas.Rows(i)("codsublinea").ToString()
                                cm.Parameters("?sublinea").Value = dtProductosEnFallas.Rows(i)("sublinea").ToString()
                                cm.Parameters("?codproveedor").Value = dtProductosEnFallas.Rows(i)("codproveedor").ToString()
                                cm.Parameters("?proveedor").Value = dtProductosEnFallas.Rows(i)("proveedor").ToString()
                                cm.Parameters("?generico").Value = dtProductosEnFallas.Rows(i)("generico").ToString()
                                cm.Parameters("?ventas").Value = dtProductosEnFallas.Rows(i)("ventas").ToString()
                                cm.ExecuteNonQuery()
                            Next
                            conexionSC_FH.Close()
                            dtProductosEnFallas.Rows.Clear()
                        Catch ex As Exception
                            MsgBox("InsertarProductosEnFallasFH: " & ex.Message)
                            conexionSC_FH.Close()
                        End Try
                    End If
                Next

            Catch ex As Exception
                MessageBox.Show("GenerarProductosEnFallasFH: " & ex.Message)
            End Try
        End Sub

    Gracias de antemano.

    miércoles, 21 de abril de 2021 17:32

Respuestas

  • le bucle que utilizo para realizar la segunda consulta es ta bien estructurado?

    En líneas generales, sí. Hay algunas cosas que yo cambiaría en cuanto al estilo; en particular, la sentencia debería estar parametrizada en lugar de usar concatenación de variables. Pero desde el punto de vista del rendimiento esto tendrá un impacto mínimo; no es ahí donde se consume la mayor parte del tiempo.
    la primera consulta es vital debido a que allí obtengo la N cantidad de productos

    Eso no sería problema. Con un buen dominio del sql y un hábil uso de subconsultas y/o expresiones comunes de tabla es factible escribir una única consulta con un Join en la que además intervenga la cantidad de productos. La cuestión es que para hacer este tipo de consultas se necesita un buen dominio del lenguaje SQL y entender perfectamente la consulta que se está construyendo. Otra opción es enviar una primera consulta muy breve que únicamente devuelva la cantidad, y después con ella construir una segunda sentencia que sea la que trae TODOS los datos de una sola vez usando un Join (en lugar de un bucle que genere repetidas sentencias en lado cliente).

    jueves, 22 de abril de 2021 6:38

Todas las respuestas

  • Así a primera vista, me da la impresión de que el problema de lentitud se debe a que primero haces una consulta que trae registros, y luego recorres esos registros en un bucle y por cada uno de ellos vuelves a enviar una consulta a la base de datos. Esto implica que si, por ejemplo, la primera consulta retorna 10000 registros, entonces estarías ejecutando en total 10001 consultas. No es extraño que sea lento, sobre todo teniendo en cuenta que cada una de ellas es a su vez muy compleja, con subconsultas, agrupaciones, ordenación, etc.

    En estos casos la forma de optimizarlo consiste en refactorizar las dos consultas anidadas de forma que se obtengan todos los daos de una sola vez usando una única sentencia con JOIN. Por desgracia, la consulta es tan compleja que no se ve a simple vista la forma de hacer este join. Tendrá que hacerlo alguien que entienda bien la sentencia y la organización de las tablas.

    miércoles, 21 de abril de 2021 19:34
  • Hola,

    Gracias por levantar tu consulta en los foros de MSDN.

    Eric Ruiz

    ____________________________

    Por favor recuerde "Marcar como respuesta" las respuestas que hayan resuelto su problema, es una forma común de reconocer a aquellos que han ayudado, y hace que sea más fácil para los otros visitantes encontrar la solución más tarde.

    Si tiene algún cumplido o reclamo sobre el soporte de MSDN siéntase en la libertad de contactar MSDNFSF@microsoft.com.

    miércoles, 21 de abril de 2021 19:53
    Moderador
  • gracias por tomarte el tiempo de responder.

    le bucle que utilizo para realizar la segunda consulta es ta bien estructurado?

    o hay otra forma de hacerlo que sea mas optima y minimizar los tiempos de respuesta.

    la primera consulta es vital debido a que allí obtengo la N cantidad de productos

    que necesito para realizar la segunda consulta o obtener por cada registro los datos correspondientes.

    miércoles, 21 de abril de 2021 20:47
  • le bucle que utilizo para realizar la segunda consulta es ta bien estructurado?

    En líneas generales, sí. Hay algunas cosas que yo cambiaría en cuanto al estilo; en particular, la sentencia debería estar parametrizada en lugar de usar concatenación de variables. Pero desde el punto de vista del rendimiento esto tendrá un impacto mínimo; no es ahí donde se consume la mayor parte del tiempo.
    la primera consulta es vital debido a que allí obtengo la N cantidad de productos

    Eso no sería problema. Con un buen dominio del sql y un hábil uso de subconsultas y/o expresiones comunes de tabla es factible escribir una única consulta con un Join en la que además intervenga la cantidad de productos. La cuestión es que para hacer este tipo de consultas se necesita un buen dominio del lenguaje SQL y entender perfectamente la consulta que se está construyendo. Otra opción es enviar una primera consulta muy breve que únicamente devuelva la cantidad, y después con ella construir una segunda sentencia que sea la que trae TODOS los datos de una sola vez usando un Join (en lugar de un bucle que genere repetidas sentencias en lado cliente).

    jueves, 22 de abril de 2021 6:38