Joanne Pham wrote: > I have read one of the performance document and it stated that "prepared > statements must be generated inside transaction". Is that correct. > > So I have to do this: > begin transaction > prepared statement > .............. > end transaction. > > I though the prepare statement must be outside of the transaction. Can any > one confirm this? > I believe that used to be the case with early versions of sqlite 3. It is no longer true.
You can see that sqlite 3.6.14 generates exactly the same opcodes when it prepares a statement either inside or outside a transaction using the explain command. SQLite version 3.6.14 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t(a,b); sqlite> .explain on sqlite> explain insert into t values(1,2); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 Goto 0 10 0 00 2 OpenWrite 0 2 0 2 00 3 NewRowid 0 2 0 00 4 Integer 1 3 0 00 5 Integer 2 4 0 00 6 MakeRecord 3 2 5 bb 00 7 Insert 0 5 2 t 1b 8 Close 0 0 0 00 9 Halt 0 0 0 00 10 Transaction 0 1 0 00 11 VerifyCookie 0 1 0 00 12 TableLock 0 2 1 t 00 13 Goto 0 2 0 00 sqlite> begin; sqlite> explain insert into t values(1,2); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 Goto 0 10 0 00 2 OpenWrite 0 2 0 2 00 3 NewRowid 0 2 0 00 4 Integer 1 3 0 00 5 Integer 2 4 0 00 6 MakeRecord 3 2 5 bb 00 7 Insert 0 5 2 t 1b 8 Close 0 0 0 00 9 Halt 0 0 0 00 10 Transaction 0 1 0 00 11 VerifyCookie 0 1 0 00 12 TableLock 0 2 1 t 00 13 Goto 0 2 0 00 sqlite> The older versions of sqlite generated different code in these two cases. If a statement was to be executed inside a transaction it was necessary to compile (i.e. prepare) it inside a transaction (thought not necessarily the same transaction that it was to be executed in) in order for sqlite to generate the correct code. If my memory serves me correctly, I seem to recall it added some kind of a COMMIT opcode to the end of a statement when it was compiled outside a transaction. This opcode would incorrectly close the transaction when executed inside a transaction. HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
