Optimize a UPDATECOMMAND of mysql under vb.net!

Question

Optimize a UPDATECOMMAND of mysql under vb.net!

Navigation

#1 by (1 votes)

1

Hello, how are you? I am working on a program under vb.net, under mysql, and when doing an extensive data update, it becomes very slow, around 2 - 15 minutes approximately. I clarify that this is not a DELETE COMMAND or INSERT COMMAND, that is, I do not delete records or insert, only update. The objects I use are the following:

A datagridview called productlist, which shows all the products of the database to be edited, I clarify that the user has the possibility to instantly edit many fields, for example: increase all prices by% 10.
Bindingsource called productdata
A datatable named productlist, which contains the data loaded from the database
A bindingmanagerbase called price listbinding, in order to update the changes made in the control to the DataTable
A MySqlDataAdapter called adapter, to perform the loading and updating of the datatable data and the database
A mysqlconnection called connection, called through the conexionstring variable, which is not shown for obvious reasons.
3 subroutines of which one, updatedata (), is the one that is
Call when you finish making changes to the form

The SELECT and UPDATE commands of the "adapter" are the ones that matter here, the select is perfect because the load is instantaneous, but the question is the UPDATE COMMAND, which is the following:

"UPDATE prices SET price =? predate, price2 =? price2new, cost =? new-cost, profit =? new-profit WHERE idproduct = ? new? UPDATE products Set stockminimo =? Stockminimonueva, category = IFNULL ((Select id FROM category of products WHERE name = ? categoryname), (SELECT id FROM category of products LIMIT 1)), provider = IFNULL ((Select id FROM providers WHERE name = ? providername), (SELECT id FROM suppliers LIMIT 1)), quantity = ? new quantity, name =? newname WHERE id =? idnew; "

My theory, Perform 3 queries per record, regardless of what was the parameter that was modified, that is, if only one parameter was modified, the others that are running are being processed unnecessarily.

Here the structure of the database:

Table products

CREATE TABLE 'productos' (
    'id' INT(11) NOT NULL AUTO_INCREMENT,
    'nombre' TEXT NOT NULL,
    'tipodeproducto' INT(11) NOT NULL DEFAULT '0',
    'cantidadbultocerrado' DECIMAL(11,2) NOT NULL DEFAULT '0.00',
    'cantidadbultoabierto' DECIMAL(11,2) NOT NULL DEFAULT '0.00',
    'especial' TINYINT(4) NOT NULL DEFAULT '0',
    'ultimamodificacion' DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    'ingresosbrutos' DECIMAL(3,2) NOT NULL DEFAULT '0.00',
    'categoria' INT(11) NOT NULL,
    'proveedor' INT(11) NOT NULL,
    'cantidad' DECIMAL(11,2) NOT NULL DEFAULT '0.00',
    'stockminimo' DECIMAL(11,2) NOT NULL DEFAULT '4.00',
    'precio' DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    'costo' DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    'precio2' DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    'pesoprecio' DECIMAL(10,2) NOT NULL DEFAULT '1.00',
    'fecha' DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    'codigobarras' VARCHAR(50) NOT NULL DEFAULT '',
    'imagen' BLOB NULL,
    'eliminado' TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY ('id'),
    UNIQUE INDEX 'id' ('id'),
    INDEX 'eliminado' ('eliminado')
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=9031
;

Table Categories of products

CREATE TABLE 'categoriadeproductos' (
    'id' INT(11) NOT NULL AUTO_INCREMENT,
    'nombre' TEXT NOT NULL,
    'ganancia' DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    PRIMARY KEY ('id')
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5
;

Price table

CREATE TABLE 'precios' (
    'id' INT(11) NOT NULL AUTO_INCREMENT,
    'porcentaje' DECIMAL(4,2) NOT NULL DEFAULT '0.00',
    'precio' DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    'precio2' DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    'ganancia' DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    'costo' DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    'idproducto' INT(11) NOT NULL DEFAULT '0',
    'numerodepreciodelista' INT(10) NOT NULL DEFAULT '0',
    'impuesto' DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    'ingresosbrutos' DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    PRIMARY KEY ('id'),
    INDEX 'idproducto' ('idproducto')
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=6149
;

Provider table

CREATE TABLE 'proveedores' (
    'id' INT(11) NOT NULL AUTO_INCREMENT,
    'nombre' VARCHAR(50) NOT NULL DEFAULT '0',
    'descripcion' TEXT NULL,
    'domicilio' VARCHAR(50) NULL DEFAULT NULL,
    'localidad' VARCHAR(50) NULL DEFAULT NULL,
    'telefono' VARCHAR(50) NULL DEFAULT NULL,
    'fax' VARCHAR(50) NULL DEFAULT NULL,
    'codigo postal' VARCHAR(50) NULL DEFAULT NULL,
    'contacto' VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY ('id')
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2
;

Codecode table

CREATE TABLE 'codigobarras' (
    'id' INT(11) NOT NULL AUTO_INCREMENT,
    'idproducto' INT(11) NOT NULL DEFAULT '0',
    'codigobarras' TEXT NOT NULL,
    'idrubro' INT(11) NOT NULL DEFAULT '0',
    'idpromocion' INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY ('id'),
    INDEX 'idproducto' ('idproducto'),
    INDEX 'idpromocion' ('idpromocion'),
    INDEX 'idrubro' ('idrubro')
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=9192
;

And then the code that I use to complete the update already said:

'Declaracion de variables y objetos
public listadodeproductos As New DataGridView
Public datosdeproductos As New BindingSource
Private listadepreciosbinding As BindingManagerBase
Private listadodeproducto As New DataTable
'iniciar parametros al cargar el formulario
Private Sub ME_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    AddHandler listadeproductosbinding.BindingComplete, AddressOf BindingComplete
    datosdeproductos.datasource = listadodeproducto
    listadeproductosbinding = me.bindingcontext(datosdeproductos, "listadodeproductos")
    listadodeproductos.DataSource = listadodeproductossource
    cargardatos
End Sub
'subrutina para guardar los cambios realizados en el datagridview (listadodeproductos)
sub Actualizardatos()
    datosdeproductos.EndEdit()
    listadeproductosbinding.EndCurrentEdit()
    listadodeproducto.GetChanges()
    ' abro la conexion con el servidor
    Using conexion As New MySqlConnection(conexionstring)
        conexion.Open()
        'declaro el adaptador para realizar el cambio en la base de datos
        Using adaptador As New MySqlDataAdapter
            adaptador.SelectCommand = New MySqlCommand("SELECT productos.id, productos.nombre AS Nombre,  categoriadeproductos.nombre AS Categoría, proveedores.nombre AS Proveedor, cantidad AS Cantidad, stockminimo AS ' Stock Mínimo ', (precios.precio) AS Precio, (precios.precio2) AS ' Precio % ', (precios.costo) AS ' P.costo ', (precios.ganancia) AS Ganancia, ultimamodificacion AS ' Fecha última modificación ', codigobarras.codigobarras AS ' Código de Barras ' FROM productos INNER JOIN precios ON precios.numerodepreciodelista = 0 AND precios.idproducto = productos.id INNER JOIN codigobarras ON codigobarras.idproducto = productos.id INNER JOIN (proveedores, categoriadeproductos) ON proveedores.id = productos.proveedor AND categoriadeproductos.id = productos.categoria WHERE productos.eliminado = 0 GROUP BY productos.id", conexion)
            adaptador.UpdateCommand = New MySqlCommand("UPDATE precios SET precio = ?precionueva, precio2 = ?precio2nueva, costo = ?costonueva, ganancia = ?ganancianueva WHERE idproducto = ?idnueva; UPDATE productos Set stockminimo = ?stockminimonueva, categoria = IFNULL((Select id FROM categoriadeproductos WHERE nombre = ?nombrecategoria), (SELECT id FROM categoriadeproductos LIMIT 1)), proveedor = IFNULL((Select id FROM proveedores WHERE nombre = ?nombreproveedor), (SELECT id FROM proveedores LIMIT 1)), cantidad = ?cantidadnueva, nombre = ?nombrenuevo WHERE id = ?idnueva;", conexion)
            adaptador.UpdateCommand.Parameters.Add("stockminimonueva", MySqlDbType.Decimal, 11.2, "Stock Mínimo")
            adaptador.UpdateCommand.Parameters.Add("cantidadnueva", MySqlDbType.Decimal, 11.2, "cantidad")
            adaptador.UpdateCommand.Parameters.Add("precionueva", MySqlDbType.Decimal, 11.2, "Precio")
            adaptador.UpdateCommand.Parameters.Add("precio2nueva", MySqlDbType.Decimal, 11.2, "Precio %")
            adaptador.UpdateCommand.Parameters.Add("costonueva", MySqlDbType.Decimal, 11.2, "P. costo")
            adaptador.UpdateCommand.Parameters.Add("ganancianueva", MySqlDbType.Decimal, 11.2, "Ganancia")
            adaptador.UpdateCommand.Parameters.Add("idnueva", MySqlDbType.Int32, 11, "id")
            adaptador.UpdateCommand.Parameters.Add("nombrecategoria", MySqlDbType.Text, 0, "Categoría")
            adaptador.UpdateCommand.Parameters.Add("nombreproveedor", MySqlDbType.Text, 0, "Proveedor")
            adaptador.UpdateCommand.Parameters.Add("nombrenuevo", MySqlDbType.Text, 0, "Nombre")
            listadodeproducto.GetChanges()
            adaptador.Update(listadodeproducto)
        End Using
    End Using
END SUB
Sub BindingComplete(ByVal sender As Object, _
    ByVal e As BindingCompleteEventArgs)
    If e.BindingCompleteContext = BindingCompleteContext.DataSourceUpdate _
        AndAlso e.Exception Is Nothing Then     
        e.Binding.BindingManagerBase.EndCurrentEdit()
    End If
End Sub
'Cargar los datos de la base de datos al datatable
SUB cargardatos()
    Using conexion As New MySqlConnection(conexionstring)
        conexion.Open()
        ' declaro el adaptador para realizar el cambio en la base de datos
        Using adaptador As New MySqlDataAdapter
            adaptador.SelectCommand = New MySqlCommand("SELECT productos.id, productos.nombre AS Nombre,  categoriadeproductos.nombre AS Categoría, proveedores.nombre AS Proveedor, cantidad AS Cantidad, stockminimo AS 'Stock Mínimo', (precios.precio) AS Precio, (precios.precio2) AS 'Precio %', (precios.costo) AS 'P. costo', (precios.ganancia) AS Ganancia, ultimamodificacion AS 'Fecha última modificación', codigobarras.codigobarras AS 'Código de Barras' FROM productos INNER JOIN precios ON precios.numerodepreciodelista = 0 AND precios.idproducto = productos.id INNER JOIN codigobarras ON codigobarras.idproducto = productos.id INNER JOIN (proveedores, categoriadeproductos) ON proveedores.id = productos.proveedor AND categoriadeproductos.id = productos.categoria WHERE productos.eliminado = 0 GROUP BY productos.id", conexion)
            adaptador.Fill(listadodeproducto)
            listadodeproducto.tablename = "listadodeproductos"
        End Using
    End Using
END sub

I hope to find some way to make a more optimal query to perform the update. PD: the only data that can be modified in the datagridview are the parameters loaded in UPDATE COMMAND, the rest is only as READ ONLY

mysql vb.net

asked by Bernardo Harreguy 30.08.2017 в 21:00

source

1 answer

How to make an increase to an amount after the payment date has expired? Mocks vs Stubs in PHPUnit

score 1 · Accepted Answer

I will focus on what seems to be the main problem that is the UPDATE of productos . I write it with another format just to make it clearer:

UPDATE  productos 
    Set     stockminimo = ?stockminimonueva, 
        categoria = IFNULL((Select id FROM categoriadeproductos WHERE nombre = ?nombrecategoria), (SELECT id FROM categoriadeproductos LIMIT 1)), 
        proveedor = IFNULL((Select id FROM proveedores WHERE nombre = ?nombreproveedor), (SELECT id FROM proveedores LIMIT 1)), 
        cantidad = ?cantidadnueva, 
        nombre = ?nombrenuevo 
    WHERE id = ?idnueva;

The first serious problem is that you are looking for a id from a name in both categoriadeproductos as in proveedores , as mentioned by @gbianchi if you do not have an index on the name you will fall in full scan that could be extremely expensive, depending on the number of records.

But beyond that, this is a design problem: What happens if there are two categories with the same name? Or two suppliers that call each other? , the only one who can decide which corresponds is the user, therefore you must save the selection that you make at the level of id and not of the nombre , it is worth overemphasizing it, a id or pk o whatever you want to call it is the data that univocally identifies a record. The user will select a category or provider name, but that selection has to be "tied" to the id of each data, it is usually offered to see / search the user the id and the nombre so that it is he who decides if he wants to (1) - Empresa A or (2) - Empresa A

Solving this is all simpler since what you would do is:

UPDATE  productos 
    Set     stockminimo = ?stockminimonueva, 
        categoria = IFNULL(?id_categoria, ?valor_default_categoria), 
        proveedor = IFNULL(?id_proveedor, ?valor_default_proveedor), 
        cantidad = ?cantidadnueva, 
        nombre = ?nombrenuevo 
    WHERE id = ?idnueva;

The values valor_default_categoria and valor_default_proveedor if they are values that do not change often, You can:

Pass them in a "hard / hardcoded" way (not the most elegant)
Set them in some table of parameters of your system and read them at the beginning only once
Add some DEFAULT to the columns, something like categoria INT(11) NOT NULL DEFAULT 1 or proveedor INT(11) NOT NULL DEFAULT 1 , in both cases we configure that the default value will be 1 (choose the one that corresponds)
But if you want to use your query, I recommend invoking it once (not with every UPDATE ) SELECT id FROM proveedores order by id LIMIT 1 , I would add the ORDER BY so that you always get the first id, I do not know exactly how MySql behaves , in SQL Server at least, without the ORDER BY you do not have security that the query always returns the same order.
Do not allow the user not to select any of these data, and thus avoid the need for a default value