Re: [sqlite] Prepared statements must be generated inside your transaction

[Dennis Cote]

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 p1p2p3p4 p5  comment 
  -        -  --  -
0 Trace  0 0 000  
1 Goto   0 10000  
2 OpenWrite  0 2 0 2  00  
3 NewRowid   0 2 000  
4 Integer1 3 000  
5 Integer2 4 000  
6 MakeRecord 3 2 5 bb 00  
7 Insert 0 5 2 t  1b  
8 Close  0 0 000  
9 Halt   0 0 000  
10Transaction0 1 000  
11VerifyCookie   0 1 000  
12TableLock  0 2 1 t  00  
13Goto   0 2 000  
sqlite> begin;
sqlite> explain insert into t values(1,2);
addr  opcode p1p2p3p4 p5  comment 
  -        -  --  -
0 Trace  0 0 000  
1 Goto   0 10000  
2 OpenWrite  0 2 0 2  00  
3 NewRowid   0 2 000  
4 Integer1 3 000  
5 Integer2 4 000  
6 MakeRecord 3 2 5 bb 00  
7 Insert 0 5 2 t  1b  
8 Close  0 0 000  
9 Halt   0 0 000  
10Transaction0 1 000  
11VerifyCookie   0 1 000  
12TableLock  0 2 1 t  00  
13Goto   0 2 000  
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

Re: [sqlite] Prepared statements must be generated inside your transaction

[John Stanton]

Joanne Pham wrote:
> Hi,
> Thanks for information!
> Regarding "batch" insert why we need to put the column binding 
> (sqlite3_bind...) before running sqlite3_step. For example:
>  sqlite_prepare_v2 
>  begin transaction
> loop thru all the changes
> sqlite3_bind 
> sqlite3_step.
> end loop
> end transaction
>
> For other database like Microsoft Sql server I only bind the column once(bind 
> statement outside the loop to the data structure) in the loop I don't need to 
> bind column again but just copy the new inserted row to the data structure 
> that already binded outside of the loop. In this case we don't need to bind 
> the columns in the loop. Why this way didn't work for SQLite3 database.
> Thanks,
> JP
>
>
>
>
> 
> From: John Stanton <jo...@viacognis.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Tuesday, May 12, 2009 12:09:09 PM
> Subject: Re: [sqlite] Prepared statements must be generated inside your 
> transaction
>
> The confusion is in the names.  When you "PREPARE" a statement you 
> actually compile the SQL.  Compiling a program each time you run it is a 
> waste of resources, and so is compiling the SQL each time you use it.
>
> Prepare your statements once and use them many times, binding data to 
> the compiled code at execution time.
>
> The design of Sqlite is such that it is possible to store compiled SQL 
> permanently and use it when you run your application.  I beleive there 
> is, or was a version of Sqlite tailored for embedded use which does just 
> that.
>
> In our Sqlite programs we  like toprepare all SQL in an initialization 
> phase and have two wins.  First we get faster execution and secondly we 
> detect database errors or mismatches before entering the main functions 
> of the program and avoid having to backtrack in error recovery.
>
> Pavel Ivanov wrote:
>   
>> I believe, these matters are very specific for each database server
>> (though I can't recall server for which it's true what you say). What
>> specific server is talked about in this book? What's the name of this
>> book?
>>
>> As long as SQLite is a concern, I prepare statements outside of
>> transaction and then use them across different transactions without
>> any problems but with huge performance improvement compared to when
>> I've prepared statements before each transaction.
>>
>> Pavel
>>
>> On Tue, May 12, 2009 at 12:32 PM, Joanne Pham <joannekp...@yahoo.com> wrote:
>>   
>> 
>>> Hi All,
>>> I have read one of the performance document and it stated that "prepared 
>>> statements must be generated inside transaction". Is that correct.
>>> The document also stated that " While trying to improve the performance of 
>>> bulk imports in our C++ project, we found that creating the prepared 
>>> statements was a large hit. Creating them once at the
>>> construction of our class, though, made the problem worse! It turns 
>>> out that prepared statements that are generated before the transaction 
>>> start do not work with the transaction. The fix was simply to
>>> create new prepared statements once per transaction."
>>>
>>> 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?
>>> Thanks,
>>> JP
>>>
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>> 
>>>   
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>   
>> 
> If you do not need to bind more than one data address, do not use the "?" (or 
> alternative) in your SQL.  You use the bind capability to bind different data 
> addresses to the compiled SQL statement
>   
The Sqlite binding method gives great flexibility in the use of 
compiled/prepared statements.
>
>   
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepared statements must be generated inside your transaction

[Joanne Pham]

Hi,
Thanks for information!
Regarding "batch" insert why we need to put the column binding 
(sqlite3_bind...) before running sqlite3_step. For example:
 sqlite_prepare_v2 
 begin transaction
    loop thru all the changes
            sqlite3_bind 
            sqlite3_step.
    end loop
end transaction

For other database like Microsoft Sql server I only bind the column once(bind 
statement outside the loop to the data structure) in the loop I don't need to 
bind column again but just copy the new inserted row to the data structure that 
already binded outside of the loop. In this case we don't need to bind the 
columns in the loop. Why this way didn't work for SQLite3 database.
Thanks,
JP





From: John Stanton <jo...@viacognis.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tuesday, May 12, 2009 12:09:09 PM
Subject: Re: [sqlite] Prepared statements must be generated inside your 
transaction

The confusion is in the names.  When you "PREPARE" a statement you 
actually compile the SQL.  Compiling a program each time you run it is a 
waste of resources, and so is compiling the SQL each time you use it.

Prepare your statements once and use them many times, binding data to 
the compiled code at execution time.

The design of Sqlite is such that it is possible to store compiled SQL 
permanently and use it when you run your application.  I beleive there 
is, or was a version of Sqlite tailored for embedded use which does just 
that.

In our Sqlite programs we  like toprepare all SQL in an initialization 
phase and have two wins.  First we get faster execution and secondly we 
detect database errors or mismatches before entering the main functions 
of the program and avoid having to backtrack in error recovery.

Pavel Ivanov wrote:
> I believe, these matters are very specific for each database server
> (though I can't recall server for which it's true what you say). What
> specific server is talked about in this book? What's the name of this
> book?
>
> As long as SQLite is a concern, I prepare statements outside of
> transaction and then use them across different transactions without
> any problems but with huge performance improvement compared to when
> I've prepared statements before each transaction.
>
> Pavel
>
> On Tue, May 12, 2009 at 12:32 PM, Joanne Pham <joannekp...@yahoo.com> wrote:
>  
>> Hi All,
>> I have read one of the performance document and it stated that "prepared 
>> statements must be generated inside transaction". Is that correct.
>> The document also stated that " While trying to improve the performance of 
>> bulk imports in our C++ project, we found that creating the prepared 
>> statements was a large hit. Creating them once at the
>>        construction of our class, though, made the problem worse! It turns 
>>out that prepared statements that are generated before the transaction start 
>>do not work with the transaction. The fix was simply to
>>        create new prepared statements once per transaction."
>>
>> 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?
>> Thanks,
>> JP
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>    
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>  

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepared statements must be generated inside your transaction

[John Stanton]

The confusion is in the names.  When you "PREPARE" a statement you 
actually compile the SQL.  Compiling a program each time you run it is a 
waste of resources, and so is compiling the SQL each time you use it.

Prepare your statements once and use them many times, binding data to 
the compiled code at execution time.

The design of Sqlite is such that it is possible to store compiled SQL 
permanently and use it when you run your application.  I beleive there 
is, or was a version of Sqlite tailored for embedded use which does just 
that.

In our Sqlite programs we  like toprepare all SQL in an initialization 
phase and have two wins.  First we get faster execution and secondly we 
detect database errors or mismatches before entering the main functions 
of the program and avoid having to backtrack in error recovery.

Pavel Ivanov wrote:
> I believe, these matters are very specific for each database server
> (though I can't recall server for which it's true what you say). What
> specific server is talked about in this book? What's the name of this
> book?
>
> As long as SQLite is a concern, I prepare statements outside of
> transaction and then use them across different transactions without
> any problems but with huge performance improvement compared to when
> I've prepared statements before each transaction.
>
> Pavel
>
> On Tue, May 12, 2009 at 12:32 PM, Joanne Pham  wrote:
>   
>> Hi All,
>> I have read one of the performance document and it stated that "prepared 
>> statements must be generated inside transaction". Is that correct.
>> The document also stated that " While trying to improve the performance of 
>> bulk imports in our C++ project, we found that creating the prepared 
>> statements was a large hit. Creating them once at the
>> construction of our class, though, made the problem worse! It turns 
>> out that prepared statements that are generated before the transaction start 
>> do not work with the transaction. The fix was simply to
>> create new prepared statements once per transaction."
>>
>> 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?
>> Thanks,
>> JP
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepared statements must be generated inside your transaction

[Pavel Ivanov]

I think last-last paragraph answers your question and proves
incorrectness of previous paragraph: "I've used this technique and it
seems to work just fine. I had to remember to reset() the prepared
statements immediately after I used them, however." :)

Pavel

On Tue, May 12, 2009 at 12:48 PM, Joanne Pham <joannekp...@yahoo.com> wrote:
> Thanks for quick responde my email
> This is sqlite documentation. Below is the link and last paragraph in this 
> document has stated that.
>
> http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning
>
>
> 
> From: Pavel Ivanov <paiva...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Tuesday, May 12, 2009 9:43:01 AM
> Subject: Re: [sqlite] Prepared statements must be generated inside your 
> transaction
>
> I believe, these matters are very specific for each database server
> (though I can't recall server for which it's true what you say). What
> specific server is talked about in this book? What's the name of this
> book?
>
> As long as SQLite is a concern, I prepare statements outside of
> transaction and then use them across different transactions without
> any problems but with huge performance improvement compared to when
> I've prepared statements before each transaction.
>
> Pavel
>
> On Tue, May 12, 2009 at 12:32 PM, Joanne Pham <joannekp...@yahoo.com> wrote:
>> Hi All,
>> I have read one of the performance document and it stated that "prepared 
>> statements must be generated inside transaction". Is that correct.
>> The document also stated that " While trying to improve the performance of 
>> bulk imports in our C++ project, we found that creating the prepared 
>> statements was a large hit. Creating them once at the
>>         construction of our class, though, made the problem worse! It turns 
>> out that prepared statements that are generated before the transaction start 
>> do not work with the transaction. The fix was simply to
>>         create new prepared statements once per transaction."
>>
>> 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?
>> Thanks,
>> JP
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepared statements must be generated inside your transaction

[Joanne Pham]

Thanks for quick responde my email
This is sqlite documentation. Below is the link and last paragraph in this 
document has stated that.

http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning



From: Pavel Ivanov <paiva...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tuesday, May 12, 2009 9:43:01 AM
Subject: Re: [sqlite] Prepared statements must be generated inside your 
transaction

I believe, these matters are very specific for each database server
(though I can't recall server for which it's true what you say). What
specific server is talked about in this book? What's the name of this
book?

As long as SQLite is a concern, I prepare statements outside of
transaction and then use them across different transactions without
any problems but with huge performance improvement compared to when
I've prepared statements before each transaction.

Pavel

On Tue, May 12, 2009 at 12:32 PM, Joanne Pham <joannekp...@yahoo.com> wrote:
> Hi All,
> I have read one of the performance document and it stated that "prepared 
> statements must be generated inside transaction". Is that correct.
> The document also stated that " While trying to improve the performance of 
> bulk imports in our C++ project, we found that creating the prepared 
> statements was a large hit. Creating them once at the
>         construction of our class, though, made the problem worse! It turns 
> out that prepared statements that are generated before the transaction start 
> do not work with the transaction. The fix was simply to
>         create new prepared statements once per transaction."
>
> 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?
> Thanks,
> JP
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepared statements must be generated inside your transaction

[Pavel Ivanov]

I believe, these matters are very specific for each database server
(though I can't recall server for which it's true what you say). What
specific server is talked about in this book? What's the name of this
book?

As long as SQLite is a concern, I prepare statements outside of
transaction and then use them across different transactions without
any problems but with huge performance improvement compared to when
I've prepared statements before each transaction.

Pavel

On Tue, May 12, 2009 at 12:32 PM, Joanne Pham  wrote:
> Hi All,
> I have read one of the performance document and it stated that "prepared 
> statements must be generated inside transaction". Is that correct.
> The document also stated that " While trying to improve the performance of 
> bulk imports in our C++ project, we found that creating the prepared 
> statements was a large hit. Creating them once at the
>         construction of our class, though, made the problem worse! It turns 
> out that prepared statements that are generated before the transaction start 
> do not work with the transaction. The fix was simply to
>         create new prepared statements once per transaction."
>
> 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?
> Thanks,
> JP
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Prepared statements must be generated inside your transaction

[Joanne Pham]

Hi All,
I have read one of the performance document and it stated that "prepared 
statements must be generated inside transaction". Is that correct.
The document also stated that " While trying to improve the performance of bulk 
imports in our C++ project, we found that creating the prepared statements was 
a large hit. Creating them once at the   
        construction of our class, though, made the problem worse! It turns out 
that prepared statements that are generated before the transaction start do not 
work with the transaction. The fix was simply to 
        create new prepared statements once per transaction."
 
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?
Thanks,
JP


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users