> Another question - What kind of impact does a limit clause have? The columns
> being used in the where clause are indexed. My current design is bad, I am
> forced to use limit to get one row at a time. Since I have an index the
> impact should be minimal. Please let me know if I am wrong.
You wrong. First of all LIMIT ... OFFSET ... clauses have undefined
results if you don't have ORDER BY clause. And second although you are
selecting only one row SQLite should scan and count all i rows to
return the (i + 1)th. So you better select everything, remember it and
then update it one-by-one (preferably in smallest number of
transactions possible).
And it's not understandable from your pseudo code what is "step" and
does thread 1 execute 1 insert statement or several in a loop (which I
suppose you intended to show).
Pavel
On Thu, Dec 2, 2010 at 2:38 PM, cricketfan <srtedul...@yahoo.co.in> wrote:
>
> I have 2 threads in my program can someone take a look at them and provide
> some comments
> Pseudo code for thread 1 (a is the primary key)
> Thread1()
> {
> insert into tbl1(a,b,c,d,e,f,g,h,i,j,k);
> }
> So my pseudo code is(b and c have an index, a is primary key)
> Thread2()
> {
> sleep(200);
> prepare;
> while(step)
> {
> Select a from tbl1 where b='yyy' c ='xxx' limit i offset (i + 1) ;
> Update tbl1 set e=1 where a = some value from the select;
> i = i + 1
> }
> }
>
> If I run these 2 threads individually the tps is acceptable but if I run
> them together the performance is woeful. I am running in serialized mode.
> Both threads use different handles to DB. Please advice what I can do in
> order to improve performance.
>
> Another question - What kind of impact does a limit clause have? The columns
> being used in the where clause are indexed. My current design is bad, I am
> forced to use limit to get one row at a time. Since I have an index the
> impact should be minimal. Please let me know if I am wrong.
>
>
> cricketfan wrote:
>>
>> Hello I have a basic question and would be glad if someone can answer it.
>> I understand that if we have "BEGIN TRANSACTION" and "END TRANSACTION"
>> then the transaction(s) within that block would be committed, number of
>> transactions could be 10,100,1000 or 10000..... . Otherwise, SQLITE by
>> default commits for every single insert,update,delete.
>> I have a prepare statement(using limit clause so will get only 10 rows
>> back) followed by step(during which I use an update clause) so how would
>> the transaction concept behave in this case? If I wrap the loop I use for
>> stepping with a begin transaction(followed by an end transaction when the
>> loop ends) would it be sufficient?
>>
>
> --
> View this message in context:
> http://old.nabble.com/Transactions-while-using-prepare-and-step-tp30359695p30361890.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> _______________________________________________
> 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
