Re: [sqlite] Consistent reads

[Jared Albers]

Do SELECT statements automatically start a transaction in order to
obtain the SHARED lock? In other words, is the SHARED lock started as
a result of the SELECT statement or as a result of the transaction? I
was reading the "SQL as Understood by SQLite" documentation and it
says the following:

"No changes can be made to the database except within a transaction.
Any command that changes the database (basically, any SQL command
other than SELECT) will automatically start a transaction if one is
not already in effect."

Does this excerpt from the documentation mean to say that SELECT
statements don't use transactions in addition to a SELECT statement
not modifying the database?


> Date: Fri, 11 Oct 2013 15:15:55 +0700
> From: Dan Kennedy 
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Consistent reads
> Message-ID: <5257b3bb.8000...@gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> On 10/11/2013 02:29 PM, Paul Harris wrote:
>> Hi again,
>>
>> I am wondering if there is any performance benefit in *not* wrapping
>> multiple SELECT calls in a BEGIN/END transaction?
>>
>> As I understand it, with wrapping:
>> BEGIN -- does nothing (yet)
>> SELECT1 -- creates a SHARE-ONLY lock on the DB
>> SELECT2 -- nothing extra
>> SELECT3 -- nothing extra
>> END -- cancels SHARE lock
>>
>> Or, without wrapping, does it create and drop the SHARE LOCK each time?
>>   Does that add overhead?  ie:
>
> Yes. Adds the overhead of obtaining and releasing the SHARED lock
> each transaction.
>
>> SELECT1 -- creates a SHARE-ONLY lock on the DB, selects, then drops lock
>> SELECT2 -- creates a SHARE-ONLY lock on the DB, selects, then drops lock
>> SELECT3 -- creates a SHARE-ONLY lock on the DB, selects, then drops lock
>>
>>
>> Also, silly question, but if I want to find a row and then read a blob
>> using the sqlite3_blob_open API, I assume I must use transactions to get a
>> consistent read, right?
>> ie, without a transaction,
>>
>> SELECT rowid FROM table WHERE condition;
>> ** another client could make a change to a db here **
>> sqlite3_blob_open( rowid )
>> ** blob may now be for the "wrong row"
>
> Transaction is required, yes. Otherwise some other connection may
> delete the row in question between the SELECT and sqlite3_blob_open()
> calls.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regression: Query takes 10x longer when using version 3.8.x

[Jared Albers]

>After laboriously translating your schema and query into something
>readable, I get script shown below.

Apologies. I assumed you planned on recreating the database and that
readability wouldn't be an issue.

>We (all SQLite developers have been consulted and agree) conclude that the
>result of the query is undefined.  You are sorting on the rowid of a
>subquery.  But the rowid of a subquery is undefined, so anything can happen
>with there.

I was unsure if the rowid of a subquery even existed until I selected it. I
reviewed the output and determined that it was what I expected of a rowid
from a subquery (simply an auto-incremented int of the result set in
order). How/Why is the rowid of a subquery undefined when it always gives
me the result I expect?

>Was SQLite 3.7.17 really giving you the answer you expected?  If so, we
>think that was by luck.

Yes version 3.7.17 is giving me the results I expect, but so does the
latest version (3.8.0.2). The only difference is that the newest version
does it much slower.

>Can you recast the query in such a way that it does not sort on the rowid
>of a subquery?

The only reason why I am joining with a subquery and then sorting by its
rowid is for performance. Originally I was simply sorting by "name" (or any
other column or combination of columns) in the outer query but discovered
it was exceedingly slow on large databases. I figured it was slow because I
was ordering by a text column with an index that was not being used, which
may or may not be true. This lead me to my subquery option that allows me
to order by an int column (which I presume would be a "primary key" since
it is a rowid). This gave me the performance I was looking for (the
difference between ~1 second and ~15 seconds on large databases), but the
cost of not being able to order by columns outside of my "Items" table.

Note that my query does rely on an "ORDER BY" without a "LIMIT" in a
subquery making it far from pointless is my use-case (valid or not).

If I can get the performance I need while ordering in the outer query I
would much rather do that.

-Jared


Date: Thu, 19 Sep 2013 15:50:17 -0400
> From: Richard Hipp 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] Regression: Query takes 10x longer when using
> version 3.8.x
> Message-ID:
> 

Re: [sqlite] Regression: Query takes 10x longer when using version 3.8.x

[Jared Albers]

What is the status on this? Is there an official ticket for this so that I
can track the issue without having to look for updates on the mailing list?

Do you need more information from me?

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

[sqlite] Regression: Query takes 10x longer when using version 3.8.x

[Jared Albers]

This query takes 10x longer to execute when using versions 3.8.x. Step back
to 3.7.17 or older and the query is much faster. I checked the query plans
of both versions and they are identical.

-Jared

Query:
SELECT R.child, R.instance, R.owner, R.relationship, I.*, NS.rowid AS sort,
COALESCE(L1.may_play, L2.may_play, L3.may_play, L4.may_play, L5.may_play,
L6.may_play, L7.may_play, L8.may_play, D.may_play) AS may_play,
COALESCE(L1.may_pause, L2.may_pause, L3.may_pause, L4.may_pause,
L5.may_pause, L6.may_pause, L7.may_pause, L8.may_pause, D.may_pause) AS
may_pause, COALESCE(L1.may_seek, L2.may_seek, L3.may_seek, L4.may_seek,
L5.may_seek, L6.may_seek, L7.may_seek, L8.may_seek, D.may_seek) AS
may_seek, COALESCE(L1.may_next, L2.may_next, L3.may_next, L4.may_next,
L5.may_next, L6.may_next, L7.may_next, L8.may_next, D.may_next) AS
may_next, COALESCE(L1.may_previous, L2.may_previous, L3.may_previous,
L4.may_previous, L5.may_previous, L6.may_previous, L7.may_previous,
L8.may_previous, D.may_previous) AS may_previous, COALESCE(L1.may_queue,
L2.may_queue, L3.may_queue, L4.may_queue, L5.may_queue, L6.may_queue,
L7.may_queue, L8.may_queue, D.may_queue) AS may_queue,
COALESCE(L1.is_queued, L2.is_queued, L3.is_queued, L4.is_queued,
L5.is_queued, L6.is_queued, L7.is_queued, L8.is_queued, D.is_queued) AS
is_queued, COALESCE(L1.may_order, L2.may_order, L3.may_order, L4.may_order,
L5.may_order, L6.may_order, L7.may_order, L8.may_order, D.may_order) AS
may_order, COALESCE(L1.may_like, L2.may_like, L3.may_like, L4.may_like,
L5.may_like, L6.may_like, L7.may_like, L8.may_like, D.may_like) AS
may_like, COALESCE(L1.is_liked, L2.is_liked, L3.is_liked, L4.is_liked,
L5.is_liked, L6.is_liked, L7.is_liked, L8.is_liked, D.is_liked) AS
is_liked, COALESCE(L1.may_rate, L2.may_rate, L3.may_rate, L4.may_rate,
L5.may_rate, L6.may_rate, L7.may_rate, L8.may_rate, D.may_rate) AS
may_rate, COALESCE(L1.rating, L2.rating, L3.rating, L4.rating, L5.rating,
L6.rating, L7.rating, L8.rating, D.rating) AS rating, COALESCE(L1.may_star,
L2.may_star, L3.may_star, L4.may_star, L5.may_star, L6.may_star,
L7.may_star, L8.may_star, D.may_star) AS may_star, COALESCE(L1.is_starred,
L2.is_starred, L3.is_starred, L4.is_starred, L5.is_starred, L6.is_starred,
L7.is_starred, L8.is_starred, D.is_starred) AS is_starred,
COALESCE(L1.played_date, L2.played_date, L3.played_date, L4.played_date,
L5.played_date, L6.played_date, L7.played_date, L8.played_date,
D.played_date) AS played_date, COALESCE(L1.added_date, L2.added_date,
L3.added_date, L4.added_date, L5.added_date, L6.added_date, L7.added_date,
L8.added_date, D.added_date) AS added_date FROM Items I INNER JOIN Defaults
D ON I.type = D.type INNER JOIN Relationships R ON R.child = I.uid INNER
JOIN (SELECT uid FROM Items ORDER BY name COLLATE NOCASE ASC) NS ON NS.uid
= I.uid LEFT OUTER JOIN Attributes L1 ON L1.parent = R.parent AND L1.child
= R.child AND L1.instance = R.instance AND L1.owner = '' LEFT OUTER JOIN
Attributes L2 ON L2.parent = R.parent AND L2.child = R.child AND
L2.instance = R.instance AND L2.owner IS NULL LEFT OUTER JOIN Attributes L3
ON L3.parent = R.parent AND L3.child = R.child AND L3.instance IS NULL AND
L3.owner = '' LEFT OUTER JOIN Attributes L4 ON L4.parent = R.parent AND
L4.child = R.child AND L4.instance IS NULL AND L4.owner IS NULL LEFT OUTER
JOIN Attributes L5 ON L5.parent = R.parent AND L5.child IS NULL AND
L5.instance IS NULL AND L5.owner = '' LEFT OUTER JOIN Attributes L6 ON
L6.parent = R.parent AND L6.child IS NULL AND L6.instance IS NULL AND
L6.owner IS NULL LEFT OUTER JOIN Attributes L7 ON L7.parent IS NULL AND
L7.child = R.child AND L7.instance IS NULL AND L7.owner = '' LEFT OUTER
JOIN Attributes L8 ON L8.parent IS NULL AND L8.child = R.child AND
L8.instance IS NULL AND L8.owner IS NULL WHERE R.parent = (SELECT parent
FROM Relationships WHERE rowid = 2) ORDER BY sort ASC LIMIT 100;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table name length in sqlite affects performance. Why?

[Jared Albers]

I really just wanted to understand the "why" in the table creation performance.

I can create a database where the table names are instead described as
rows, and the creation of the database is *much* faster. An order of
magnitude faster. The amount of data inserted into a row doesn't
affect performance nearly as much as the amount of data used to
describe a table name. This is the part I'm trying to understand.

-Jared

On Wed, Sep 4, 2013 at 10:21 PM, James K. Lowden
<jklow...@schemamania.org> wrote:
> On Tue, 3 Sep 2013 18:43:52 -0600
> Jared Albers <jalb...@mymail.mines.edu> wrote:
>
>> When using relatively long table names like `TABLE_{table #}_{some
>> unique identifying name that adds 120 or so characters}`, creation of
>> a database with 10,000 tables takes approximately 60 seconds.
>
> I find this a very strange course of interrogation.  Tables are created
> once.  Databases with 10,000 tables should be created never (to a
> reasonable approximation).
>
> Is this just an exercise, or is there some horrible real application
> out there being slowed down because it's creating thousands of tables a
> minute?
>
> --jkl
>
> ___
> 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] Table name length in sqlite affects performance. Why?

[Jared Albers]

Hi Richard and others,

The code to reproduce this problem can be found in a Stack Overflow
question I created here:
http://stackoverflow.com/questions/18603123/table-name-length-in-sqlite-affects-performance-why

-Jared

On Wed, Sep 4, 2013 at 5:36 AM, Richard Hipp <d...@sqlite.org> wrote:
>
>
>
> On Tue, Sep 3, 2013 at 8:43 PM, Jared Albers <jalb...@mymail.mines.edu>
> wrote:
>>
>> Attached is a code example that reproduces
>> the problem.
>
>
> The sqlite-users@sqlite.org mailing list strips off attachments.  Can you
> send a link to your code?
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Table name length in sqlite affects performance. Why?

[Jared Albers]

I'm noticing that the length of table names affects performance during
creation of those tables. Attached is a code example that reproduces
the problem.

To compile the example:
gcc main.c sqlite3.c -O3 -DLONG_NAMES -DNDEBUG
gcc main.c sqlite3.c -O3 -DNDEBUG

On my machine, when using relatively short table names like
`TABLE_{table #}`, creation of a database with 10,000 tables takes
approximately 14 seconds. These table names vary from 7 to a max of 11
characters.

When using relatively long table names like `TABLE_{table #}_{some
unique identifying name that adds 120 or so characters}`, creation of
a database with 10,000 tables takes approximately 60 seconds.

Creating the database with long table names took over 4 times longer!

Why is this the case? Is this expected behavior or a bug?

And since creating tables with long names negatively affects
performance, this leads me to wonder if query performance on such a
database would also be negatively affected.

Thoughts?

P.S.: I'm using the latest amalgamated version of sqlite (3.8). There
is also a Stack Overflow question covering this topic here:
http://stackoverflow.com/questions/18603123/table-name-length-in-sqlite-affects-performance-why

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