Hagamos que México crezca..
Prefiere el consumo de lo Hecho en México
Prefiere el consumo de lo Hecho en México
Somos Binniza - Los Hombres de Las Nubes
BúsquedaPosts Recientes
VisitantesConversación |
by Robert Simpson.. " rel="bookmark">"The Importance of Transactions" by Robert Simpson..Un interesante Post de Robert Simpson desarrollador del Ado Net Provider para SQLite y Visual Studio .NET 2005, dónde explica el porqué usar transacciones es importante, la principal a mi gusto es que disminuye considerablemente el tiempo de ejecución de las consultas, ya anotada en la lista de mis mejores prácticas.. La información fué tomada de la siguiente URL http://petesbloggerama.blogspot.com/2007/02/sqlite-adonet-prepared-statements.html
If you are inserting data in SQLite without first starting a transaction: DO NOT PASS GO! Call BeginTransaction() right now, and finish with Commit()! If you think I'm kidding, think again. SQLite's A.C.I.D. design means that every single time you insert any data outside a transaction, an implicit transaction is constructed, the insert made, and the transaction destructed. EVERY TIME. If you're wondering why in the world your inserts are taking 100x longer than you think they should, look no further. Prepared Statements Lets have a quick look at the following code and evaluate its performance: </em></em> using (SQLiteCommand mycommand = new SQLiteCommand(myconnection)) { int n; for (n = 0; n < 100000; n ++) { mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId]) VALUES({0})", n + 1); mycommand.ExecuteNonQuery(); } } <em><em> This code seems pretty tight, but if you think it performs well, you're dead wrong. Here's what's wrong with it: I didn't start a transaction first! This insert is dog slow! The CLR is calling "new" implicitly 100,000 times because I am formatting a string in the loop for every insert Since SQLite precompiles SQL statements, the engine is constructing and deconstructing 100,000 SQL statements and allocating/deallocating their memory All this construction and destruction is involving about 300,000 more native to managed interop calls than an optimized insert. So lets rewrite that code slightly: </em></em> using (SQLiteTransaction mytransaction = myconnection.BeginTransaction()) { using (SQLiteCommand mycommand = new SQLiteCommand(myconnection)) { SQLiteParameter myparam = new SQLiteParameter(); int n; mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)"; mycommand.Parameters.Add(myparam); for (n = 0; n < 100000; n ++) { myparam.Value = n + 1; mycommand.ExecuteNonQuery(); } } mytransaction.Commit(); }<em> </em><em><em> </em><em> this is a blazing fast insert for any database engine, not just SQLite. The SQL statement is prepared one time -- on the first call to ExecuteNonQuery(). Once prepared, it never needs re-evaluating. Furthermore, we're allocating no memory in the loop and doing a very minimal number of interop transitions. Surround the entire thing with a transaction, and the performance of this insert is so far and away faster than the original that it merits a hands-on-the-hips pirate-like laugh. Every database engine worth its salt utilizes prepared statements. If you're not coding for this, you're not writing optimized SQL, and that's the bottom line. Many developers do not fully understand the importance of using prepared statements with parameters where stored procedures (the preferred method in almost all cases) cannot or should not be used. The importance of this, combined with the practice of wrapping repeating inserts or updates in a transaction, cannot be underestimated. If the above all makes sense, then you'll like my next installment, "Did Chuck Norris kill SOA?".
Fuentes XML de comentario: RSS | Atom
Emblemas
|
Nube de Etiquetas
¿ Where The Hell Am I ?   EventosEncuestaComentarios Recientes
|
Dejar un comentario