Insertar en tablas relacionadas N:M (maxima performance)

[enridp]

Hola a todos!
Estoy buscando si hay alguna forma más eficiente de hacer el/los siguiente INSERT:

Ante todo la estructura:


Digamos que deseo insertar un nuevo usuariro, con su direccion, entonces esto es lo que hago:

INSERT INTO usuarios (nombre) VALUES ('juancito')
ultimoIDUsuario = SELECT LAST_INSERT_ID()
INSERT INTO direcciones (direccion) VALUES ('callecita 155')
ultimoIDDireccion = SELECT LAST_INSERT_ID()
INSERT INTO usuarios_has_direcciones (usuarios_idusuarios, direcciones_iddirecciones) VALUES (ultimoIDUsuario, ultimoIDDireccion)

El problema es que son 3 inserts y 2 selects para insertar solo un usuario.
Ademas que si uso INSERT... ON DUPLICTE UPTADE ya tengo que tener cuidado con el LasID

Pero lo que mas me preocupa es la performance. Porque por querere normalizar mis tablas termine teniendo 3 tablas en lo que podria haber sido una sola (bastante fea pero bueno, tener el usuario y las direcciones en una sola tabla).

Pero normalizando tengo que hacer 3 inserts y 2 selects mientras que con una tabla con un Insert ya alcanza.
Estoy haciendo algo mal? se puede mejorar? o esto es así y es el precio a pagar por tener una BD mas organizada?

Saludos !!!
Enrique.

[gnzsoloyo]

Es el precio a pagar por hacer un diseño correctamente.
Inevitablemente parece menos eficiente, pero no te debes olvidar que donde se ve la eficiencia, la optimización de una base no es en las inserciones, sino en las consultas, porque es allí donde se desarrolla el verdadero trabajo de una base.
Para que lo entiendas: Por cada inserción que hagas, es posible que ese registro se consulte centenares de miles de veces. Entonces, ¿dónde es que necesitas optimización o rapidez? ¿En las inserciones o en las consultas?
La respuesta cae de cajón: Las consultas.
Por otro lado, lo único que se complica es la forma de hacer la inserción, y si la misma se hace dato por dato, en realidad es más el tiempo que tardas en crear la conexión, o el overhead del TCP, que lo que realmente tarda esa inserción en realizarse, así que ¿por qué te procupa que sea más compleja?
Es irrelevante, en definitiva. Tal vez te lleve más tiempo programar, pero ganarás en todo el resto...

[enridp]

Y respecto a la eficiencia y eficacia de las consultas?
Estan bien asi o hay una forma de mejorarla, por ejemplo con los LAST_INSERT_ID, es seguro hacerlo de esa manera?

[gnzsoloyo]

LAST_INSERT_ID() es una función que sirve para obtener el último ID autoincremental ingresado. Esa y sólo esa es su función, por lo que lo que has puesto sólo puede funcionar dentro de un stored procedure.
La pregunta sería: ¿Cómo estás realizando estas inserciones y cómo la recuperación del ID?
Esto último lo pregunto porque dependiendo del lenguaje usado, puede haber funciones nativas en el lenguaje para hacer eso mismo (caso PHP).

[enridp]

Estoy usando PHP, con mySQLi, se que puede hacer asi:
$mysqli->query($query);
$lastID = $mysqli->insert_id;
pero supongo que internamente lo que hace es llamar LAST_INSERT_ID().
Los pasos que puse arriba serian consultas directas hechas desde PHP, no con procedimientos (tu crees que sería mejor armar un procedimiento? creo que segun la ayuda de PHP no podemos usar $mysqli->insert_id; con procedimientos).

Hay alguna ganancia en cuanto a performance o seguridad en usar
$lastID = $mysqli->insert_id;
en lugar de esto?:
$lastID = $mysqli->query("SELECT LAST_INSERT_ID()");

[enridp]

dejo otra pregunta.
Es conveniente hacer esos 3 inserts (insertar usuario, insertar direccion, insertar las claves foraneas) dentro de una transacción?

No puedo encontrar ejemplos de como se hace esto, la verdad que en internet hay ausencia de estas cosas, sobre todo en lo que se refiere a "buenas prácticas", esta lleno de codigo muy ineficiente e inseguro y cuando uno esta aprendiendo se vuelve MUY dificil no caer en los mismos errores.

[gnzsoloyo]

Es conveniente hacer esos 3 inserts (insertar usuario, insertar direccion, insertar las claves foraneas) dentro de una transacción?

Eso es más una decisión de diseño que una de práctica buena o mala.
El sentido de una transacción es el crear operaciones atómicas, por lo que te puede convenir si, por ejemplo, existe la posibilidad de violar resticciones en la segunda o tercera inserción, y quieres que sólo si todas funcionan se considere bien terminada.
Pero puede darse que te interese que la primera o segunda se cumplan siempre, y que sólo la tercera se considere como transacción. Esto implicaría que prefieres que esos datos primarios se carguen si o sí. En ese caso la transacción sólo contemplaría la tercera inserción.

Como verás, no hay una regla fija: depende de tu decisión y tu decisión dependerá de las reglas del negocio.
¿A qué me refiero?
Si el cliente te dice que "Un cliente es un cliente, aunque no haya realizado una compra, sino simplemente se suscriba como tal", entonces una transacción de compra de un cliente sólo incluirá la compra dentro de la transacción confirmable, pero no el ingreso del cliente, que compondría una transacción distinta, y eso porque el cliente debe ingresar siempre, aunque la compra se cancele...

¿Se entiende la idea?

Por eso decía que es una decisión de diseño que surge de las reglas del negocio.

[enridp]

Si entiendo, pero yo estaba pensando en esta situacion:
El registro de un usuario en mi caso, por el diseño normalizado que opté requiere la insercion en 3 tablas.
Si la insercion en la segunda o tercer tabla falla entonces quedaria en mi BD un registro incompleto.
En ese sentido habia pensado que tal vez lo mejor era usar una transaccion para asegurarse que si fallo la insersion (por X motivo, no se me ocurre que podria pasar, tal vez cayo justo un rayo en el servidor no se), entonces no se hace ningun cambio, es o todo o nada.

Pero como nunca he visto ejemplos de esto no se si es una buena practica.
Tal vez es una "exageración" de seguridad que termina perjudicando demasiado la performance. O tal vez si es algo que siempre deberiamos hacer, no se :(

[gnzsoloyo]

Pero como nunca he visto ejemplos de esto no se si es una buena practica.
Tal vez es una "exageración" de seguridad que termina perjudicando demasiado la performance. O tal vez si es algo que siempre deberiamos hacer, no se :(

Te insisto: No es una práctica buena o mala per se. Es una decisión de diseño de sistemas.
Si la quieres hacer así, porque la necesitas así, pon las tres inserciones en una transacción.
Lo único que debes tener en cuenta es que en MySQL, para que se pueda cumplir debes usar si o sí tablas InnoDB, y que no debes cerrar y abrir la conexión entre las inserciones (un cierre de transacción genera un rollback implícito).
Nada más.

[enridp]

y pero entonces cual es la contra de hacerlo con una transacción?
porque en principio me parece que lo ideal seria siempre hacerlo asi, es decir, la insercion de un usuario requiere las 3 inserciones.
Que ventaja tendria no usar una transacción en este caso?

[gnzsoloyo]

Son decisiones de diseño de sistemas, te repito, basadas en los requerimientos del usuario, a los que se agregan los requerimientos surgidos de la concurrencia de procesos.
No es que sean prácticos, buenos, malos o ideales por sí mismos.
La declaración de una transacción provoca bloqueos en las tablas administradas en la base, lo que si no se sincroniza bien puede provocar lecturas fantasma, o irreales entre diferentes usuarios concurrentes, lo cual también es un problema grave a considerar.
Si vas a usar diseñar un sistema monousuario, las transacciones se usan para mantener consistencia entre inserciones diferentes consideradas como una sola unidad. Pero si vas a usarlo en un sistema multiusuario, debes considerar otros factores que generarán problemas... y entonces vuelve todo al mismo punto: depende del diseño del sistema que estás haciendo. No de las inserciones en sí mismas...

Como idea, usar transacciones es bueno para tu caso por la consistencia que lograrás, y porque existe una serie de inserciones que están muy relacionadas una con otra. Pero eso no significa que sea ideal o no usar transacciones en todas las inserciones del sistema.
Debes analizar el caso específico, por las implicancias.
En un sistema mayor, bloquear una tabla que usas es mejor que bloquear todas... que puede que no uses.

No hay una respuesta única para tu pregunta.

[enridp]

Es que justamente eso es lo que no se :(
Por eso comentaba que creo hace falta eso en Internet, o al menos las fuentes oficiales deberian tener consejos o "best practices" para diferentes casos.

Yo no tengo la experiencia como para poder decidir con seguridad que me conviene más.

Por ejemplo no sabía que al empezar una transacción se bloqueaban las tablas, estas seguro esto es asi? o tal vez te entendí mal. Porque de hecho una de las mayores ventajas de InnoDB frente a MyISAM es precisamente que no bloquea las tablas solo la fila que está modificando.
Y al ser una transacción supuse que ni eso bloquearía ya que no se hace ninguna modificacion hasta que no se termine. Pero de nuevo, estoy leyendo el tema asique ya ni se si estoy en lo correcto.

Buscando un poco más encontre esto:

A secondary benefit of using transactions is speed. There is often an overhead associated with actually committing the data to the database. If you've got 1000 rows to insert, committing after every row can cause quite a performance hit compared to committing once after all the inserts. Of course, this can work the other way too — if you do too much work in a transaction then the database engine can consume lots of space storing the not-yet-committed data or caching data for use by other database connections in order to maintain consistency, which causes a performance hit

fuente: http://www.justsoftwaresolutions.co....nsactions.html

Because of the significant amount of disk I/O required to commit every operation on the database server and because of the extra network round trips that occur between the driver and the database, in most cases you will want to turn off auto-commit mode in your application. By doing this, your application can control when the database work is committed, which provides dramatically better performance.

Although turning off auto-commit mode can help application performance, do not take this tip too far. Leaving transactions active can reduce throughput by holding locks on rows for longer than necessary, preventing other users from accessing the rows. Typically, committing transactions in intervals provides the best performance as well as acceptable concurrency

In addition, be aware that leaving transactions active consumes database memory.

fuente: http://searchdatamanagement.techtarg...ion-management

PARA LOS MAS VAGOS RESUMO:

Segun los articulos que encontre usar transacciones siempre es conveniente. No solo porque mantiene la integridad sino porque aumenta la performance (contrariamente a lo que yo creia), ya que solo escribimos en el disco una vez finalizado, y las escrituras al disco son las operaciones mas lentas.
Cual es la desventaja de las transacciones entonces? solo encuentro dos:
Consume memoria
Bloquea las filas sobre las que trabaja hasta que terminemos la transaccion (las bloquea para escritura no lectura)

Asique mientras nuestras transacciones no sea muy grandes, no van a consumir demasiada memoria no bloquear por demasiado tiempo las filas, y por lo tanto es conveniente usarlas ya que aumentan la performance y mantienen la integridad.

En mi caso creo ya que son solo 3 inserts y la integridad es importante lo ideal es usar una transacción. Al menos es esa la conclusión que puedo elaborar sin la experiencia real, solo leyendo sobre el tema.
Si estoy equivocado por favor háganmelo saber.

[gnzsoloyo]

Porque de hecho una de las mayores ventajas de InnoDB frente a MyISAM es precisamente que no bloquea las tablas solo la fila que está modificando.

Bueno, la primera ventaja entre MyISAM e InnoDB es que en InnoDB existen las transacciones, mientras que en MyISAM no.
Con respecto a la concurrencia de transacciones, te recomiendo darle una leída al tema, por ejemplo en esta página: Concurrencia
Allí verás los temas de los diferentes accesos a lecturas, bloqueos en MySQL, y al final un ejercicio sobre concurrencia.

[enridp]

parece muy completo, voy a leerlo.
Gracias gonzalo!

PD: dejo otra pagina donde hay recomendaciones sobre el uso de transacciones:
http://sqlserverpedia.com/wiki/Recom...r_Transactions