Re: [sqlite] Simple question about optimization
Thanks Dan, your answer it's exactly what i want to know. Thanks again!
On 10/12/2007, Kees Nuyt <[EMAIL PROTECTED]> wrote:
On Mon, 10 Dec 2007 12:22:53 +0100, Clodo <[EMAIL PROTECTED]>
wrote:
In this case:
-
CREATE TABLE test (
Field01 text PRIMARY KEY NOT NULL,
Field02 text
);
insert into test values ('alpha','beta');
update test set Field01='alpha', Field02='gamma';
Under the hood, the UPDATE statement above updates both the
table and index. SQLite does not realize that the index already
contains the correct data.
This:
UPDATE test set Field02='gamma';
does not touch the index.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Simple question about optimization
On 10/12/2007, Kees Nuyt <[EMAIL PROTECTED]> wrote:
On Mon, 10 Dec 2007 12:22:53 +0100, Clodo <[EMAIL PROTECTED]>
wrote:
In this case:
-
CREATE TABLE test (
Field01 text PRIMARY KEY NOT NULL,
Field02 text
);
insert into test values ('alpha','beta');
update test set Field01='alpha', Field02='gamma';
Under the hood, the UPDATE statement above updates both the
table and index. SQLite does not realize that the index already
contains the correct data.
This:
UPDATE test set Field02='gamma';
does not touch the index.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Simple question about optimization
On 10/12/2007, Cesar D. Rodas <[EMAIL PROTECTED]> wrote:
>
> What Kess Nuyt is asking is if you want to replace a column with a data,
> which is the data, will SQLite update it or SQLite is smart enough for
> avoid write the same thing?, very important for keep executing time,
> because write something to HDD is very expensive.
>
> I hope I understand well your question Kees, unfortunately I couldn't
> answer your question, because I don't know, I think Dr. Hipp will be able to
> answer your question :-)
It was Clodo question, sorry :-)
On 10/12/2007, Kees Nuyt <[EMAIL PROTECTED]> wrote:
> >
> > On Mon, 10 Dec 2007 12:22:53 +0100, Clodo <[EMAIL PROTECTED]>
> > wrote:
> >
> > >In this case:
> > >
> > >-
> > >CREATE TABLE test (
> > > Field01 text PRIMARY KEY NOT NULL,
> > > Field02 text
> > >);
> > >
> > >insert into test values ('alpha','beta');
> > >
> > >update test set Field01='alpha', Field02='gamma';
> > >-
> > >
> > >In the "update" statement, i re-set the primary field "Field01" to a
> > >value that field already have.
> >
> > >Sqlite detect this situation and don't update the primary index,
> >
> > SQLite will update the primary key, verifying all constraints
> > (NOT NULL, UNIQUE). The performance impact isn't very big,
> > because the relevant pages will be loaded in the cache anyway.
> >
> > EXPLAIN UPDATE test SET Field01='alpha', Field02='gamma';
> > tells the whole story.
> >
> > >or suppose that developers optimize situation like that?
> >
> > The statement does not do what you seem to need.
> > Usually you want to do:
> > UPDATE test SET Field02='gamma' WHERE Field01='alpha';
> >
> > Without the WHERE clause ALL rows will be updated, which will
> > fail because column Field01 will not be unique anymore.
> >
> > Extend your test set with:
> > insert into test values ('delta','kappa');
> > and rerun your test to see what happens.
> >
> > >P.s. in our program, the "update" statement are generated from a
> > >database-layer, and optimize the statement generation is a big work..
> > >for that i'm trying to understand if will be a biggest optimization or
> > >not..
> > >thanks for feedback!
> >
> > HTH
> > --
> > ( Kees Nuyt
> > )
> > c[_]
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >
>
>
> --
> Best Regards
>
> Cesar D. Rodas
> http://www.cesarodas.com
> http://www.thyphp.com
> http://www.phpajax.org
> Phone: +595-961-974165
--
Best Regards
Cesar D. Rodas
http://www.cesarodas.com
http://www.thyphp.com
http://www.phpajax.org
Phone: +595-961-974165
Re: [sqlite] Simple question about optimization
What Kess Nuyt is asking is if you want to replace a column with a data,
which is the data, will SQLite update it or SQLite is smart enough for
avoid write the same thing?, very important for keep executing time,
because write something to HDD is very expensive.
I hope I understand well your question Kees, unfortunately I couldn't
answer your question, because I don't know, I think Dr. Hipp will be able to
answer your question :-)
On 10/12/2007, Kees Nuyt <[EMAIL PROTECTED]> wrote:
>
> On Mon, 10 Dec 2007 12:22:53 +0100, Clodo <[EMAIL PROTECTED]>
> wrote:
>
> >In this case:
> >
> >-
> >CREATE TABLE test (
> > Field01 text PRIMARY KEY NOT NULL,
> > Field02 text
> >);
> >
> >insert into test values ('alpha','beta');
> >
> >update test set Field01='alpha', Field02='gamma';
> >-
> >
> >In the "update" statement, i re-set the primary field "Field01" to a
> >value that field already have.
>
> >Sqlite detect this situation and don't update the primary index,
>
> SQLite will update the primary key, verifying all constraints
> (NOT NULL, UNIQUE). The performance impact isn't very big,
> because the relevant pages will be loaded in the cache anyway.
>
> EXPLAIN UPDATE test SET Field01='alpha', Field02='gamma';
> tells the whole story.
>
> >or suppose that developers optimize situation like that?
>
> The statement does not do what you seem to need.
> Usually you want to do:
> UPDATE test SET Field02='gamma' WHERE Field01='alpha';
>
> Without the WHERE clause ALL rows will be updated, which will
> fail because column Field01 will not be unique anymore.
>
> Extend your test set with:
> insert into test values ('delta','kappa');
> and rerun your test to see what happens.
>
> >P.s. in our program, the "update" statement are generated from a
> >database-layer, and optimize the statement generation is a big work..
> >for that i'm trying to understand if will be a biggest optimization or
> >not..
> >thanks for feedback!
>
> HTH
> --
> ( Kees Nuyt
> )
> c[_]
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
--
Best Regards
Cesar D. Rodas
http://www.cesarodas.com
http://www.thyphp.com
http://www.phpajax.org
Phone: +595-961-974165
Re: [sqlite] Simple question about optimization
On Mon, 10 Dec 2007 12:22:53 +0100, Clodo <[EMAIL PROTECTED]>
wrote:
>In this case:
>
>-
>CREATE TABLE test (
> Field01 text PRIMARY KEY NOT NULL,
> Field02 text
>);
>
>insert into test values ('alpha','beta');
>
>update test set Field01='alpha', Field02='gamma';
>-
>
>In the "update" statement, i re-set the primary field "Field01" to a
>value that field already have.
>Sqlite detect this situation and don't update the primary index,
SQLite will update the primary key, verifying all constraints
(NOT NULL, UNIQUE). The performance impact isn't very big,
because the relevant pages will be loaded in the cache anyway.
EXPLAIN UPDATE test SET Field01='alpha', Field02='gamma';
tells the whole story.
>or suppose that developers optimize situation like that?
The statement does not do what you seem to need.
Usually you want to do:
UPDATE test SET Field02='gamma' WHERE Field01='alpha';
Without the WHERE clause ALL rows will be updated, which will
fail because column Field01 will not be unique anymore.
Extend your test set with:
insert into test values ('delta','kappa');
and rerun your test to see what happens.
>P.s. in our program, the "update" statement are generated from a
>database-layer, and optimize the statement generation is a big work..
>for that i'm trying to understand if will be a biggest optimization or
>not..
>thanks for feedback!
HTH
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Simple question about optimization
In this case:
-
CREATE TABLE test (
Field01 text PRIMARY KEY NOT NULL,
Field02 text
);
insert into test values ('alpha','beta');
update test set Field01='alpha', Field02='gamma';
-
In the "update" statement, i re-set the primary field "Field01" to a
value that field already have.
Sqlite detect this situation and don't update the primary index, or
suppose that developers optimize situation like that?
P.s. in our program, the "update" statement are generated from a
database-layer, and optimize the statement generation is a big work..
for that i'm trying to understand if will be a biggest optimization or
not..
thanks for feedback!
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
