SQLITE_SCHEMA can't happen during transaction, right?

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLITE_SCHEMA can't happen during transaction, right?

Derrell Lipman
Let's say I start a transaction with either BEGIN IMMEDIATE or BEGIN
EXCLUSIVE, by successfully going through the sqlite3_prepare(),
sqlite3_step(), and sqlite3_finalize() process.

1. I then prepare a SELECT statement with sqlite3_prepare().  Since the BEGIN
statement did not give me a SQLITE_SCHEMA error, am I correct in believing
that now that I'm in a transaction, the schema CAN NOT change, and I will not
get back a schema changed error on this sqlite3_prepare("SELECT ...")?

2. Similarly, after the SELECT statement has been prepared, I expect that I
will not get back a schema change error on any of the sqlite3_step() calls
retrieving the data from the SELECT, nor on the sqlite3_finalize() for the
SELECT, nor from any of the three functions on the subsequent COMMIT.
Correct?

Thanks,

Derrell
Reply | Threaded
Open this post in threaded view
|

How to improve performance of SELECT... LIKE...

Puneet Kishor
I started with three identical tables, first with no index, second with
regular index, third with unique index, about 2,35,000 rows.

CREATE TABLE t1 (i INTEGER PRIMARY KEY, a VARCHAR(200) UNIQUE, b
INTEGER DEFAULT 0);

CREATE TABLE t2 (i INTEGER PRIMARY KEY, a VARCHAR(200) UNIQUE, b
INTEGER DEFAULT 0);
CREATE INDEX t2_nx ON t2 (a);

CREATE TABLE t3 (i INTEGER PRIMARY KEY, a VARCHAR(200) UNIQUE, b
INTEGER DEFAULT 0);
CREATE UNIQUE INDEX t3_ux ON t3 (a);

@array = qw(find many more some that this what when where which with
and for);
$sql1 = "SELECT COUNT(*) FROM web2 WHERE word LIKE ?";
$sql2 = "SELECT COUNT(*) FROM web2 WHERE word = ?";

I want to SELECT COUNT(*) of each element in array using the sql
statements.
Benchmarking pseudo-code follows --

$iterations = 10

Benchmark: timing "LIKE" 10 iterations of t1, t2, t3...
    t1: 129 wallclock secs (105.66 usr + 16.68 sys = 122.34 CPU) @  
0.08/s (n=10)
    t2: 130 wallclock secs (105.21 usr + 17.16 sys = 122.37 CPU) @  
0.08/s (n=10)
    t3: 131 wallclock secs (105.34 usr + 17.11 sys = 122.45 CPU) @  
0.08/s (n=10)


---------------------------------------------
Benchmark: timing "=" 10 iterations of t1, t2, t3...
    t1:  0 wallclock secs ( 0.04 usr +  0.00 sys =  0.04 CPU) @ 250.00/s
(n=10)
             (warning: too few iterations for a reliable count)
    t2:  0 wallclock secs ( 0.04 usr +  0.00 sys =  0.04 CPU) @ 250.00/s
(n=10)
             (warning: too few iterations for a reliable count)
    t3:  1 wallclock secs ( 0.04 usr +  0.00 sys =  0.04 CPU) @ 250.00/s
(n=10)
             (warning: too few iterations for a reliable count)

Seems, at least in the cases above, the INDEXes themselves don't make
any difference to performance worth writing home about.

The main difference is in LIKE versus =.

Is there anything wrong that I am doing, or overlooking something? How
can I improve the performance of the LIKE selects?


--
Puneet Kishor

Reply | Threaded
Open this post in threaded view
|

Re: How to improve performance of SELECT... LIKE...

Cory Nelson
On 6/11/05, Puneet Kishor <[hidden email]> wrote:

> I started with three identical tables, first with no index, second with
> regular index, third with unique index, about 2,35,000 rows.
>
> CREATE TABLE t1 (i INTEGER PRIMARY KEY, a VARCHAR(200) UNIQUE, b
> INTEGER DEFAULT 0);
>
> CREATE TABLE t2 (i INTEGER PRIMARY KEY, a VARCHAR(200) UNIQUE, b
> INTEGER DEFAULT 0);
> CREATE INDEX t2_nx ON t2 (a);
>
> CREATE TABLE t3 (i INTEGER PRIMARY KEY, a VARCHAR(200) UNIQUE, b
> INTEGER DEFAULT 0);
> CREATE UNIQUE INDEX t3_ux ON t3 (a);
>
> @array = qw(find many more some that this what when where which with
> and for);
> $sql1 = "SELECT COUNT(*) FROM web2 WHERE word LIKE ?";
> $sql2 = "SELECT COUNT(*) FROM web2 WHERE word = ?";
>
> I want to SELECT COUNT(*) of each element in array using the sql
> statements.
> Benchmarking pseudo-code follows --
>
> $iterations = 10
>
> Benchmark: timing "LIKE" 10 iterations of t1, t2, t3...
>     t1: 129 wallclock secs (105.66 usr + 16.68 sys = 122.34 CPU) @
> 0.08/s (n=10)
>     t2: 130 wallclock secs (105.21 usr + 17.16 sys = 122.37 CPU) @
> 0.08/s (n=10)
>     t3: 131 wallclock secs (105.34 usr + 17.11 sys = 122.45 CPU) @
> 0.08/s (n=10)
>
>
> ---------------------------------------------
> Benchmark: timing "=" 10 iterations of t1, t2, t3...
>     t1:  0 wallclock secs ( 0.04 usr +  0.00 sys =  0.04 CPU) @ 250.00/s
> (n=10)
>              (warning: too few iterations for a reliable count)
>     t2:  0 wallclock secs ( 0.04 usr +  0.00 sys =  0.04 CPU) @ 250.00/s
> (n=10)
>              (warning: too few iterations for a reliable count)
>     t3:  1 wallclock secs ( 0.04 usr +  0.00 sys =  0.04 CPU) @ 250.00/s
> (n=10)
>              (warning: too few iterations for a reliable count)
>
> Seems, at least in the cases above, the INDEXes themselves don't make
> any difference to performance worth writing home about.
>
> The main difference is in LIKE versus =.
>
> Is there anything wrong that I am doing, or overlooking something? How
> can I improve the performance of the LIKE selects?

You're not doing anything wrong.  LIKE isn't able to use an index, so
it must scan the entire table.

>
> --
> Puneet Kishor
>
>


--
Cory Nelson
http://www.int64.org
Reply | Threaded
Open this post in threaded view
|

Re: How to improve performance of SELECT... LIKE...

BertV
Op zaterdag 11 juni 2005 17:04, schreef Cory Nelson:

> On 6/11/05, Puneet Kishor <[hidden email]> wrote:
> > I started with three identical tables, first with no index, second with
> > regular index, third with unique index, about 2,35,000 rows.
> >
> > CREATE TABLE t1 (i INTEGER PRIMARY KEY, a VARCHAR(200) UNIQUE, b
> > INTEGER DEFAULT 0);
> >
> > CREATE TABLE t2 (i INTEGER PRIMARY KEY, a VARCHAR(200) UNIQUE, b
> > INTEGER DEFAULT 0);
> > CREATE INDEX t2_nx ON t2 (a);
> >
> > CREATE TABLE t3 (i INTEGER PRIMARY KEY, a VARCHAR(200) UNIQUE, b
> > INTEGER DEFAULT 0);
> > CREATE UNIQUE INDEX t3_ux ON t3 (a);
> >
> > @array = qw(find many more some that this what when where which with
> > and for);
> > $sql1 = "SELECT COUNT(*) FROM web2 WHERE word LIKE ?";
> > $sql2 = "SELECT COUNT(*) FROM web2 WHERE word = ?";
> >
> > I want to SELECT COUNT(*) of each element in array using the sql
> > statements.
> > Benchmarking pseudo-code follows --
> >
> > $iterations = 10
> >
> > Benchmark: timing "LIKE" 10 iterations of t1, t2, t3...
> >     t1: 129 wallclock secs (105.66 usr + 16.68 sys = 122.34 CPU) @
> > 0.08/s (n=10)
> >     t2: 130 wallclock secs (105.21 usr + 17.16 sys = 122.37 CPU) @
> > 0.08/s (n=10)
> >     t3: 131 wallclock secs (105.34 usr + 17.11 sys = 122.45 CPU) @
> > 0.08/s (n=10)
> >
> >
> > ---------------------------------------------
> > Benchmark: timing "=" 10 iterations of t1, t2, t3...
> >     t1:  0 wallclock secs ( 0.04 usr +  0.00 sys =  0.04 CPU) @ 250.00/s
> > (n=10)
> >              (warning: too few iterations for a reliable count)
> >     t2:  0 wallclock secs ( 0.04 usr +  0.00 sys =  0.04 CPU) @ 250.00/s
> > (n=10)
> >              (warning: too few iterations for a reliable count)
> >     t3:  1 wallclock secs ( 0.04 usr +  0.00 sys =  0.04 CPU) @ 250.00/s
> > (n=10)
> >              (warning: too few iterations for a reliable count)
> >
> > Seems, at least in the cases above, the INDEXes themselves don't make
> > any difference to performance worth writing home about.
> >
> > The main difference is in LIKE versus =.
> >
> > Is there anything wrong that I am doing, or overlooking something? How
> > can I improve the performance of the LIKE selects?
>
> You're not doing anything wrong.  LIKE isn't able to use an index, so
> it must scan the entire table.
>
> > --
> > Puneet Kishor

You can replace LIKE by example
field>'Aa'  and field<'Ab'

finds all words that begin with ' Aa' and is able to use an index

--
Met vriendelijke groet
Bert Verhees
ROSA Software
Reply | Threaded
Open this post in threaded view
|

Seeking optimal way of UNION-ing

Puneet Kishor
tbl1(a INTEGER PRIMARY KEY, b VARCHAR(200) UNIQUE) with 200k+ records
tbl2(a INTEGER PRIMARY KEY, b VARCHAR(200) UNIQUE) with a few k records

values in col(b) in tbl1 are not common with the values in col(b) in
tbl2

I want to find a given string in either tbl1 or tbl2. Which of the
following is better?

SELECT *
FROM ((SELECT * FROM tbl1) UNION (SELECT * FROM tbl2))
WHERE b = 'foo'

or

SELECT * FROM tbl1 WHERE b = 'foo'
UNION
SELECT * FROM tbl1 WHERE b = 'foo'

In my experience, the first SELECT is much slower, but are there any
gotchas with the second SELECT? Is there a better way?


--
Puneet Kishor

Reply | Threaded
Open this post in threaded view
|

Re: Seeking optimal way of UNION-ing

Puneet Kishor
Rats... itchy fingers on the send button...

On Jun 11, 2005, at 12:32 PM, Puneet Kishor wrote:

> tbl1(a INTEGER PRIMARY KEY, b VARCHAR(200) UNIQUE) with 200k+ records
> tbl2(a INTEGER PRIMARY KEY, b VARCHAR(200) UNIQUE) with a few k records
>
> values in col(b) in tbl1 are not common with the values in col(b) in
> tbl2
>
> I want to find a given string in either tbl1 or tbl2. Which of the
> following is better?
>
> SELECT *
> FROM ((SELECT * FROM tbl1) UNION (SELECT * FROM tbl2))
> WHERE b = 'foo'
>
> or
>
> SELECT * FROM tbl1 WHERE b = 'foo'
> UNION
> SELECT * FROM tbl1 WHERE b = 'foo'
>
> In my experience, the first SELECT is much slower, but are there any
> gotchas with the second SELECT? Is there a better way?
>

My question stems from my understanding that I can convert the first
SELECT into a VIEW and then conveniently SELECT against that VIEW.

CREATE VIEW bigview AS (
        SELECT * FROM ((SELECT * FROM tbl1) UNION (SELECT * FROM tbl2))
)

SELECT * FROM bigview WHERE b = 'foo'

But, that will be slow, no?

--
Puneet Kishor

Reply | Threaded
Open this post in threaded view
|

Re: Seeking optimal way of UNION-ing

D. Richard Hipp
In reply to this post by Puneet Kishor
On Sat, 2005-06-11 at 12:32 -0500, Puneet Kishor wrote:
> tbl1(a INTEGER PRIMARY KEY, b VARCHAR(200) UNIQUE) with 200k+ records
> tbl2(a INTEGER PRIMARY KEY, b VARCHAR(200) UNIQUE) with a few k records
>

> SELECT *
> FROM ((SELECT * FROM tbl1) UNION (SELECT * FROM tbl2))
> WHERE b = 'foo'
>
> or
>
> SELECT * FROM tbl1 WHERE b = 'foo'
> UNION
> SELECT * FROM tbl1 WHERE b = 'foo'
>

The second form will be faster because of the index
on column b.  (Whenever you use the UNIQUE keyword in
a column declaration, an index is created on that column
automatically.)

It will be faster still if you use UNION ALL instead
of UNION.
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Seeking optimal way of UNION-ing

Puneet Kishor

On Jun 11, 2005, at 2:03 PM, D. Richard Hipp wrote:

> On Sat, 2005-06-11 at 12:32 -0500, Puneet Kishor wrote:
>> tbl1(a INTEGER PRIMARY KEY, b VARCHAR(200) UNIQUE) with 200k+ records
>> tbl2(a INTEGER PRIMARY KEY, b VARCHAR(200) UNIQUE) with a few k
>> records
>>
>
>> SELECT *
>> FROM ((SELECT * FROM tbl1) UNION (SELECT * FROM tbl2))
>> WHERE b = 'foo'
>>
>> or
>>
>> SELECT * FROM tbl1 WHERE b = 'foo'
>> UNION
>> SELECT * FROM tbl1 WHERE b = 'foo'
>>
>
> The second form will be faster because of the index
> on column b.  (Whenever you use the UNIQUE keyword in
> a column declaration, an index is created on that column
> automatically.)
>

Fair enough. A couple of questions follow --

If I do have UNIQUE in a col declaration, will creating another index
on that column slow things down? Before I knew what you write above, I
had gone ahead and create a UNIQUE INDEX on col b. I am not sure if
that was slowing the operations, so I just want to confirm that.

Second, it seems logical that if I have UNIQUE then I don't have to
declare NOT NULL. For example,

b VARCHAR(200) UNIQUE NOT NULL

would be redundant.

If I have declared UNIQUE NOT NULL, should I go back and ALTER the
table to just UNIQUE so as to not experience and perf hits?



--
Puneet Kishor

Reply | Threaded
Open this post in threaded view
|

Re: Seeking optimal way of UNION-ing

Dan Kennedy

> If I do have UNIQUE in a col declaration, will creating another index
> on that column slow things down? Before I knew what you write above, I
> had gone ahead and create a UNIQUE INDEX on col b. I am not sure if
> that was slowing the operations, so I just want to confirm that.

Slows down writes and makes the database larger.

> Second, it seems logical that if I have UNIQUE then I don't have to
> declare NOT NULL. For example,

Not redundant. UNIQUE does not imply NOT NULL.

> If I have declared UNIQUE NOT NULL, should I go back and ALTER the
> table to just UNIQUE so as to not experience and perf hits?

In SQLite, ALTER can't do that. All it can do is add new columns or
change the name of an existing table.



               
__________________________________
Discover Yahoo!
Find restaurants, movies, travel and more fun for the weekend. Check it out!
http://discover.yahoo.com/weekend.html 

Reply | Threaded
Open this post in threaded view
|

Re: Seeking optimal way of UNION-ing

Puneet Kishor

On Jun 12, 2005, at 10:12 PM, Dan Kennedy wrote:

>
>> If I do have UNIQUE in a col declaration, will creating another index
>> on that column slow things down? Before I knew what you write above, I
>> had gone ahead and create a UNIQUE INDEX on col b. I am not sure if
>> that was slowing the operations, so I just want to confirm that.
>
> Slows down writes and makes the database larger.
>
>> Second, it seems logical that if I have UNIQUE then I don't have to
>> declare NOT NULL. For example,
>
> Not redundant. UNIQUE does not imply NOT NULL.

thanks for all the valuable clarification. However, the above seems
contradictory. After all, if more than one row can be NULL, then they
won't be UNIQUE! what gives?


--
Puneet Kishor

Reply | Threaded
Open this post in threaded view
|

Re: Seeking optimal way of UNION-ing

Dan Kennedy
 
> thanks for all the valuable clarification. However, the above seems
> contradictory. After all, if more than one row can be NULL, then they
> won't be UNIQUE! what gives?

SQL NULLs are weird. The expression NULL==NULL evaluates to NULL. It's
and SQL thing, not particular to SQLite.



               
__________________________________
Discover Yahoo!
Have fun online with music videos, cool games, IM and more. Check it out!
http://discover.yahoo.com/online.html
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_SCHEMA can't happen during transaction, right?

Jay Sprenkle
In reply to this post by Derrell Lipman
It's my understanding once you have an exclusive lock no other process
can modify the database and therefore can't trigger a schema change.



On 6/11/05, [hidden email]
<[hidden email]> wrote:

> Let's say I start a transaction with either BEGIN IMMEDIATE or BEGIN
> EXCLUSIVE, by successfully going through the sqlite3_prepare(),
> sqlite3_step(), and sqlite3_finalize() process.
>
> 1. I then prepare a SELECT statement with sqlite3_prepare().  Since the BEGIN
> statement did not give me a SQLITE_SCHEMA error, am I correct in believing
> that now that I'm in a transaction, the schema CAN NOT change, and I will not
> get back a schema changed error on this sqlite3_prepare("SELECT ...")?
>
> 2. Similarly, after the SELECT statement has been prepared, I expect that I
> will not get back a schema change error on any of the sqlite3_step() calls
> retrieving the data from the SELECT, nor on the sqlite3_finalize() for the
> SELECT, nor from any of the three functions on the subsequent COMMIT.
> Correct?
Reply | Threaded
Open this post in threaded view
|

Re: Seeking optimal way of UNION-ing

Brad-19
In reply to this post by Puneet Kishor
> >> Second, it seems logical that if I have UNIQUE then I don't have to
> >> declare NOT NULL. For example,
> >
> > Not redundant. UNIQUE does not imply NOT NULL.
>
> thanks for all the valuable clarification. However, the above seems
> contradictory. After all, if more than one row can be NULL, then they
> won't be UNIQUE! what gives?

If only one value in the table is null, and the rest have non-null unique values, that satisfies the UNIQUE requirement.
Reply | Threaded
Open this post in threaded view
|

RE: How to improve performance of SELECT... LIKE...

Thomas Briggs
In reply to this post by Puneet Kishor

   Though other posters have already pointed out that LIKE can't use an
index, I think that it's also worth mentioning that in all three forms
of the table you've created, there's an index on b - specifying the
UNIQUE keywords silently creates an index for you on that column, to
enforce uniqueness.  That's why your equality tests all ran in the same
amount of time - there was always at least one index present.

   -Tom

> -----Original Message-----
> From: Puneet Kishor [mailto:[hidden email]]
> Sent: Saturday, June 11, 2005 10:35 AM
> To: [hidden email]
> Subject: [sqlite] How to improve performance of SELECT... LIKE...
>
> I started with three identical tables, first with no index,
> second with
> regular index, third with unique index, about 2,35,000 rows.
>
> CREATE TABLE t1 (i INTEGER PRIMARY KEY, a VARCHAR(200) UNIQUE, b
> INTEGER DEFAULT 0);
>
> CREATE TABLE t2 (i INTEGER PRIMARY KEY, a VARCHAR(200) UNIQUE, b
> INTEGER DEFAULT 0);
> CREATE INDEX t2_nx ON t2 (a);
>
> CREATE TABLE t3 (i INTEGER PRIMARY KEY, a VARCHAR(200) UNIQUE, b
> INTEGER DEFAULT 0);
> CREATE UNIQUE INDEX t3_ux ON t3 (a);
>
> @array = qw(find many more some that this what when where which with
> and for);
> $sql1 = "SELECT COUNT(*) FROM web2 WHERE word LIKE ?";
> $sql2 = "SELECT COUNT(*) FROM web2 WHERE word = ?";
>
> I want to SELECT COUNT(*) of each element in array using the sql
> statements.
> Benchmarking pseudo-code follows --
>
> $iterations = 10
>
> Benchmark: timing "LIKE" 10 iterations of t1, t2, t3...
>     t1: 129 wallclock secs (105.66 usr + 16.68 sys = 122.34 CPU) @  
> 0.08/s (n=10)
>     t2: 130 wallclock secs (105.21 usr + 17.16 sys = 122.37 CPU) @  
> 0.08/s (n=10)
>     t3: 131 wallclock secs (105.34 usr + 17.11 sys = 122.45 CPU) @  
> 0.08/s (n=10)
>
>
> ---------------------------------------------
> Benchmark: timing "=" 10 iterations of t1, t2, t3...
>     t1:  0 wallclock secs ( 0.04 usr +  0.00 sys =  0.04 CPU)
> @ 250.00/s
> (n=10)
>              (warning: too few iterations for a reliable count)
>     t2:  0 wallclock secs ( 0.04 usr +  0.00 sys =  0.04 CPU)
> @ 250.00/s
> (n=10)
>              (warning: too few iterations for a reliable count)
>     t3:  1 wallclock secs ( 0.04 usr +  0.00 sys =  0.04 CPU)
> @ 250.00/s
> (n=10)
>              (warning: too few iterations for a reliable count)
>
> Seems, at least in the cases above, the INDEXes themselves don't make
> any difference to performance worth writing home about.
>
> The main difference is in LIKE versus =.
>
> Is there anything wrong that I am doing, or overlooking
> something? How
> can I improve the performance of the LIKE selects?
>
>
> --
> Puneet Kishor
>
>