[sqlite] Bad performance with large joins?
Hi! I'm trying to use the database of the OpenGeoDB project (http://opengeodb.hoppe-media.com/index.php?FrontPage_en) with SQLite. Simple example queries from opengeodb work ok, like SELECT text_val FROM geodb_textdata WHERE text_type=50010 /* NAME */ AND loc_id=27431; But the less trivial queries are very slow: SELECT code.text_val as area code, name.text_val as town FROM geodb_textdata code, geodb_textdata name WHERE name.loc_id=code.loc_id AND code.text_type=50030 /* AREA_CODE */ AND name.text_type=50010 /* NAME */ ORDER by 2; Results just dripple out slowly. Particularly I have to use it *without* the ORDER BY, else the result takes forever. Postgresql solves it much faster, with or without ORDER BY. The only difference in the data is that I used INTEGER instead of Postgres' Boolean type. The OpenGeoDB examples are taken from here: http://sourceforge.net/docman/display_doc.php?docid=27614group_id=132421 If you want to look at the DB, I have importable data here: http://renormalist.net/opengeodb/opengeodb-sqlite.sql.gz http://renormalist.net/opengeodb/opengeodb-postgres.sql.gz (2 MB each, 26 MB after gunzipping) The Database is described here: http://sourceforge.net/docman/index.php?group_id=132421 Is there something I can optimize in SQLite? E.g., I'm not sure, that the indexes really work. Syntax seems to match the documentation, but I'm a sqlite newbie and no db wizard. (Greeti+Tha)nX Steffen -- Steffen Schwigon [EMAIL PROTECTED] Dresden Perl Mongers http://dresden-pm.org/
Re: [sqlite] Bad performance with large joins?
Have you tried creating indexes on your rows. I am working with a particularly large database, with more than 40 000 Records, i had a timeout problem, but as soon as i created indexes on my key rows, the speed was amazing and i use complex queries especially for my reports. I suggest you add indexes on text_val On Fri, 2006-03-24 at 09:30 +0100, Steffen Schwigon wrote: Hi! I'm trying to use the database of the OpenGeoDB project (http://opengeodb.hoppe-media.com/index.php?FrontPage_en) with SQLite. Simple example queries from opengeodb work ok, like SELECT text_val FROM geodb_textdata WHERE text_type=50010 /* NAME */ AND loc_id=27431; But the less trivial queries are very slow: SELECT code.text_val as area code, name.text_val as town FROM geodb_textdata code, geodb_textdata name WHERE name.loc_id=code.loc_id AND code.text_type=50030 /* AREA_CODE */ AND name.text_type=50010 /* NAME */ ORDER by 2; Results just dripple out slowly. Particularly I have to use it *without* the ORDER BY, else the result takes forever. Postgresql solves it much faster, with or without ORDER BY. The only difference in the data is that I used INTEGER instead of Postgres' Boolean type. The OpenGeoDB examples are taken from here: http://sourceforge.net/docman/display_doc.php?docid=27614group_id=132421 If you want to look at the DB, I have importable data here: http://renormalist.net/opengeodb/opengeodb-sqlite.sql.gz http://renormalist.net/opengeodb/opengeodb-postgres.sql.gz (2 MB each, 26 MB after gunzipping) The Database is described here: http://sourceforge.net/docman/index.php?group_id=132421 Is there something I can optimize in SQLite? E.g., I'm not sure, that the indexes really work. Syntax seems to match the documentation, but I'm a sqlite newbie and no db wizard. (Greeti+Tha)nX Steffen
Re: [sqlite] Bad performance with large joins?
Roger [EMAIL PROTECTED] writes: Have you tried creating indexes on your rows. [..] I suggest you add indexes on text_val Yes. I use create index text_val_idx on geodb_textdata(text_val); I just experimented with dropping/recreating indexes and it sometimes even feels a bit faster *without* the index. Anyway, both variants are slow, nearly same speed. Another idea: the data are utf-8, can this be a problem? Do I have to declare this somewhere at import ore runtime? Can I set it to non-unicode, to see performance difference? (Greeti+Tha)nX Steffen -- Steffen Schwigon [EMAIL PROTECTED] Dresden Perl Mongers http://dresden-pm.org/
Re: [sqlite] Bad performance with large joins?
I will research on that one! On Fri, 2006-03-24 at 09:57 +0100, Steffen Schwigon wrote: Roger [EMAIL PROTECTED] writes: Have you tried creating indexes on your rows. [..] I suggest you add indexes on text_val Yes. I use create index text_val_idx on geodb_textdata(text_val); I just experimented with dropping/recreating indexes and it sometimes even feels a bit faster *without* the index. Anyway, both variants are slow, nearly same speed. Another idea: the data are utf-8, can this be a problem? Do I have to declare this somewhere at import ore runtime? Can I set it to non-unicode, to see performance difference? (Greeti+Tha)nX Steffen
AW: [sqlite] Bad performance with large joins?
Have you tried creating indexes on your rows. [..] I suggest you add indexes on text_val Yes. I use create index text_val_idx on geodb_textdata(text_val); This index seems pretty useless. You're querying against geodb_textdata.loc_id and geodb_textdata.text_type. So you should create an index over these columns. Greetings, Christian
Re: AW: [sqlite] Bad performance with large joins?
Christian Schwarz [EMAIL PROTECTED] writes: Have you tried creating indexes on your rows. [..] I suggest you add indexes on text_val Yes. I use create index text_val_idx on geodb_textdata(text_val); This index seems pretty useless. You're querying against geodb_textdata.loc_id and geodb_textdata.text_type. So you should create an index over these columns. Sorry, I just named this one in my reply. In the DB there are much more indexes: [...] create index text_lid_idx on geodb_textdata(loc_id); create index text_val_idx on geodb_textdata(text_val); create index text_type_idx on geodb_textdata(text_type); create index text_locale_idx on geodb_textdata(text_locale); create index text_native_idx on geodb_textdata(is_native_lang); create index text_default_idx on geodb_textdata(is_default_name); create index text_since_idx on geodb_textdata(valid_since); create index text_until_idx on geodb_textdata(valid_until); [...] Practically one such line for each table and each column. If you want to see the whole db as import script, have a look at http://renormalist.net/opengeodb/opengeodb-sqlite.sql.gz This DB was originally a Postgres one. I just changed the boolean true/false into integer-0/1 and everything else at least syntactically worked. Maybe I'm missing some other syntax that SQLite accepts but silently ignores or handles differently. GreetinX Steffen -- Steffen Schwigon [EMAIL PROTECTED] Dresden Perl Mongers http://dresden-pm.org/
[sqlite] SQLite: which platforms are supported?
Hi, We consider using SQLite for some of our applications and I would like to ask whether there are existing cases of using SQLite on the following OSes: - Mac - Windows - Linux and with following architectures - x86 - x86_64 - ia64 I just would like to get some information about whether there are any showstoppers or serious problems that we can face with if we try to use SQLite on those platforms. I appreciate your help, colleagues. Thanks a lot! -- Alexei Alexandrov
AW: [sqlite] Bad performance with large joins?
Have you tried creating indexes on your rows. [..] I suggest you add indexes on text_val Yes. I use create index text_val_idx on geodb_textdata(text_val); This index seems pretty useless. You're querying against geodb_textdata.loc_id and geodb_textdata.text_type. So you should create an index over these columns. Practically one such line for each table and each column. Why on each column? If you want to see the whole db as import script, have a look at http://renormalist.net/opengeodb/opengeodb-sqlite.sql.gz I suggest analyzing your queries and creating only those indexes that are really needed. There's no need, and it's surely a bad practice, to create an index for each and every column. For example, when your where-clause contains columns A, B and C (in this order) you should create *one* index on A, B and C. Separate indexes on column A, B and C are not that useful. In this case, SQLite would most probably use the separate index on column A. Greetings, Christian
[sqlite] Stored procedures in triggers
Hi, Can you tell me how to create a stored procedure in an sqlite3 database and use the same in a trigger? Please provide an example (as complete as possible). In the stored procedure I need to execute few queries on some tables. Can you tell me how to do that also? Any help is deeply appreciated. Best Regards, Chethana
Re: AW: [sqlite] Bad performance with large joins?
Christian Schwarz [EMAIL PROTECTED] writes: Practically one such line for each table and each column. Why on each column? I just took the existing DB-import-script from that project. But ... For example, when your where-clause contains columns A, B and C (in this order) you should create *one* index on A, B and C. ... you are right, creating a combined index solves the speed problem. Thanks. Separate indexes on column A, B and C are not that useful. In this case, SQLite would most probably use the separate index on column A. Which would be a pity, wouldn't it? Postgres for instance seems to do something more clever there, at least it's much faster, even with the trivial setting of an index on each column. Anyway, thanks for your answer. GreetinX Steffen -- Steffen Schwigon [EMAIL PROTECTED] Dresden Perl Mongers http://dresden-pm.org/
Re: [sqlite] Stored procedures in triggers
See the email of Igor Tandetnik from 18-Dec-2005: Vishal Kashyap wrote Is their any way we can write simple stored procedures or functions in sqlite. If yes please do guide me I need this functionality in one of my open source project. Not in the usual sense, meaning some language that gets stored in the database itself together with the data. The only thing that comes somewhat close is a trigger. It is possible to create a poor man's stored procedure like this: create table sp_dosomething (param1 int, param2 char); create trigger sp_dosomething_impl instead of insert on sp_dosomething begin -- one or more sql statements possibly referring to -- new.param1 and new.param2 end; -- To invoke: insert into sp_dosomething values(1, 'hello'); Note that triggers are rather limited in what they can do. They are just a bunch of SQL statements, there is no control flow (loops, if then else, goto) beyond what little you can implement in pure SQL. They cannot return values, except indirectly by inserting or updating some table. SQLite does not support cascading triggers, so if your stored procedure manipulates some table to which regular triggers are attached (perhaps ensuring data integrity), those triggers won't run. SQLite supports custom functions - see sqlite3_create_function[16]. You write them in C (or any other language that has bindings to SQLite API) and you have to install them every time you open a DB handle with sqlite3_open, before you can refer to them in your SQL statements. They are not stored in the database file itself. Finally, SQLite prepared statements (sqlite_prepare) can be thought of as simple stored procedures defined in your program. Similar to custom functions, you can prepare a statement right after opening the database, then keep it around. Igor Tandetnik Ran On 3/24/06, Chethana, Rao (IE10) [EMAIL PROTECTED] wrote: Hi, Can you tell me how to create a stored procedure in an sqlite3 database and use the same in a trigger? Please provide an example (as complete as possible). In the stored procedure I need to execute few queries on some tables. Can you tell me how to do that also? Any help is deeply appreciated. Best Regards, Chethana
Re: [sqlite] Safe maximum numbers of tables?
Tito Ciuro [EMAIL PROTECTED] wrote: Hello, I was reading the FAQ and I came across this statement: In practice, SQLite must read and parse the original SQL of all table and index declarations everytime a new database file is opened, so for the best performance of sqlite3_open() it is best to keep down the number of declared tables. Is there a safe maximum number of tables that doesn't negatively impact too much sqlite3_open()? I wonder if it's 10, 25...? The more tables you have, the slower the first query will run and the more memory SQLite will use. For long-running applications where the startup time is not a significant factor, 100s or 1000s of tables is fine. For a CGI script that starts itself up anew several times per second, then you should try to keep the number of tables below a 100, I think. Less than that if you can. You should also try and keep down the number of tables in low-memory embedded applications, in order to save on memory usages. Each table takes a few hundred bytes of memory - depending on the number of columns and features. -- D. Richard Hipp [EMAIL PROTECTED]
[sqlite] Re: Stored procedures in triggers
A small correction: I was wrong about SQLite not supporting cascading triggers. Cascading triggers are supported, recursive triggers are not. That is, if you have an insert trigger on table A which, say, inserts into table B, and there's an insert trigger on table B, it will run. But if this latter trigger turns around and inserts into table A, the A trigger won't run again. Igor Tandetnik Ran [EMAIL PROTECTED] wrote: See the email of Igor Tandetnik from 18-Dec-2005: Vishal Kashyap wrote Is their any way we can write simple stored procedures or functions in sqlite. If yes please do guide me I need this functionality in one of my open source project. Not in the usual sense, meaning some language that gets stored in the database itself together with the data. The only thing that comes somewhat close is a trigger. It is possible to create a poor man's stored procedure like this: create table sp_dosomething (param1 int, param2 char); create trigger sp_dosomething_impl instead of insert on sp_dosomething begin -- one or more sql statements possibly referring to -- new.param1 and new.param2 end; -- To invoke: insert into sp_dosomething values(1, 'hello'); Note that triggers are rather limited in what they can do. They are just a bunch of SQL statements, there is no control flow (loops, if then else, goto) beyond what little you can implement in pure SQL. They cannot return values, except indirectly by inserting or updating some table. SQLite does not support cascading triggers, so if your stored procedure manipulates some table to which regular triggers are attached (perhaps ensuring data integrity), those triggers won't run. SQLite supports custom functions - see sqlite3_create_function[16]. You write them in C (or any other language that has bindings to SQLite API) and you have to install them every time you open a DB handle with sqlite3_open, before you can refer to them in your SQL statements. They are not stored in the database file itself. Finally, SQLite prepared statements (sqlite_prepare) can be thought of as simple stored procedures defined in your program. Similar to custom functions, you can prepare a statement right after opening the database, then keep it around. Igor Tandetnik Ran On 3/24/06, Chethana, Rao (IE10) [EMAIL PROTECTED] wrote: Hi, Can you tell me how to create a stored procedure in an sqlite3 database and use the same in a trigger? Please provide an example (as complete as possible). In the stored procedure I need to execute few queries on some tables. Can you tell me how to do that also? Any help is deeply appreciated. Best Regards, Chethana
Re: [sqlite] Stored procedures in triggers
Igor Tandetnik [EMAIL PROTECTED] wrote: Cascading triggers are supported [in SQLite], recursive triggers are not. That is, if you have an insert trigger on table A which, say, inserts into table B, and there's an insert trigger on table B, it will run. But if this latter trigger turns around and inserts into table A, the A trigger won't run again. I've been looking into this. Right now, if you have a recursive trigger, it just doesn't run. There is no error. I'm thinking of perhaps changing that so that you do at least get an error message. Thoughts? Would making recursive triggers an error rather than just silently ignoring them break anybody's code? I'm also looking at making DELETE triggers recursive. I can do that because recursive DELETE triggers are guaranteed to terminate (you will eventually run out of rows to delete.) But INSERT or UPDATE triggers might go on forever. There are also technical issues that make recursive INSERT and UPDATE triggers more difficult so that I would prefer to delay implementing them. Comments? Would it be useful to have recursive DELETE triggers even without recursive INSERT or UPDATE triggers? -- D. Richard Hipp [EMAIL PROTECTED]
RE: [sqlite] Stored procedures in triggers
Thoughts? Would making recursive triggers an error rather than just silently ignoring them break anybody's code? even if it does, it should. otherwise, people may assume that the functionality exists,and rely on it. I'm also looking at making DELETE triggers recursive. I can do that because recursive DELETE triggers are guaranteed to terminate (you will eventually run out of rows to delete.) But INSERT or UPDATE triggers might go on forever. There are also technical issues that make recursive INSERT and UPDATE triggers more difficult so that I would prefer to delay implementing them. Comments? Would it be useful to have recursive DELETE triggers even without recursive INSERT or UPDATE triggers? not much IMHO
[sqlite] no errors,but not executed: Stored procedures in triggers
My problem is not with cascading or using recursive triggers.
Actually,
I am using sqlite3_create_function to execute some queries(like
insert or update etc.) but the control does not pass to the custom
function at all, ie., say a user-defined function
sp_dosomethingfunc(sqlite3_context *context, int argc,sqlite3_value
**argv)
{
Do something or execute some queries...
..etc
}
/* from main() I'm calling the above function using
sqlite3_create_function*/
int main()
{
/* after using sqlite_open */
sqlite3_create_function()
/* exec function is executed, but control is
not passed to sp_dosomethingfunc, how do I make stmts inside this custom
function get executed? */
sqlite3_exec(.)
}
/* and this sp_dosomethingfunc, I'm calling from triggers. I'm not
getting any error. But this function is not executed */
Pls do reply ASAP.
-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: Friday, March 24, 2006 6:38 PM
To: SQLite
Subject: [sqlite] Re: Stored procedures in triggers
A small correction: I was wrong about SQLite not supporting cascading
triggers. Cascading triggers are supported, recursive triggers are not.
That is, if you have an insert trigger on table A which, say, inserts
into table B, and there's an insert trigger on table B, it will run. But
if this latter trigger turns around and inserts into table A, the A
trigger won't run again.
Igor Tandetnik
Ran [EMAIL PROTECTED] wrote:
See the email of Igor Tandetnik from 18-Dec-2005:
Vishal Kashyap wrote
Is their any way we can write simple stored procedures or functions
in sqlite. If yes please do guide me I need this functionality in one
of my open source project.
Not in the usual sense, meaning some language that gets stored in the
database itself together with the data. The only thing that comes
somewhat close is a trigger. It is possible to create a poor man's
stored procedure like this:
create table sp_dosomething (param1 int, param2 char);
create trigger sp_dosomething_impl
instead of insert on sp_dosomething
begin
-- one or more sql statements possibly referring to
-- new.param1 and new.param2
end;
-- To invoke:
insert into sp_dosomething values(1, 'hello');
Note that triggers are rather limited in what they can do. They are
just
a bunch of SQL statements, there is no control flow (loops, if then
else, goto) beyond what little you can implement in pure SQL. They
cannot return values, except indirectly by inserting or updating some
table. SQLite does not support cascading triggers, so if your stored
procedure manipulates some table to which regular triggers are
attached
(perhaps ensuring data integrity), those triggers won't run.
SQLite supports custom functions - see sqlite3_create_function[16].
You
write them in C (or any other language that has bindings to SQLite
API)
and you have to install them every time you open a DB handle with
sqlite3_open, before you can refer to them in your SQL statements.
They
are not stored in the database file itself.
Finally, SQLite prepared statements (sqlite_prepare) can be thought of
as simple stored procedures defined in your program. Similar to custom
functions, you can prepare a statement right after opening the
database,
then keep it around.
Igor Tandetnik
Ran
On 3/24/06, Chethana, Rao (IE10)
[EMAIL PROTECTED] wrote:
Hi,
Can you tell me how to create a stored procedure in an sqlite3
database and use the same in a trigger? Please provide an example
(as complete as possible). In the stored procedure I need to execute
few queries on some tables. Can you tell me how to do that also?
Any help is deeply appreciated.
Best Regards,
Chethana
Re: [sqlite] no errors,but not executed: Stored procedures in triggers
On 3/24/06, Chethana, Rao (IE10) [EMAIL PROTECTED] wrote:
My problem is not with cascading or using recursive triggers.
Actually,
I am using sqlite3_create_function to execute some queries(like
insert or update etc.) but the control does not pass to the custom
function at all, ie., say a user-defined function
sp_dosomethingfunc(sqlite3_context *context, int argc,sqlite3_value
**argv)
{
Do something or execute some queries...
..etc
}
/* from main() I'm calling the above function using
sqlite3_create_function*/
int main()
{
/* after using sqlite_open */
sqlite3_create_function()
/* exec function is executed, but control is
not passed to sp_dosomethingfunc, how do I make stmts inside this custom
function get executed? */
sqlite3_exec(.)
}
/* and this sp_dosomethingfunc, I'm calling from triggers. I'm not
getting any error. But this function is not executed */
your function would be executed by something like
select sp_dosomethingfunc
Since it's a function you have to do something to evaluate it.
Since your trigger is 'calling' the function, are you sure your
trigger is being run?
Re: [sqlite] Re: concers about database size
On Wed, Mar 22, 2006 at 07:35:32PM +0100, Daniel Franke wrote: I can tell you that even 750M rows wouldn't be a huge deal for PostgreSQL, and 20G of data is nothing. Though your table would take somewhere around 30G due to the higher per-row overhead in PostgreSQL; I'm not really sure how large the indexes would be. AFAIK, PostgreSQL is implemented in a client-server architecture. For maintainability, I try to avoid such a thing. It is, but I wouldn't let that scare you off. 8.1 with a few config tweaks (mostly just to enable automatic vacuums) is very, very hands-off. Of course if SQLite suffices it'll probably be even more hands off. :) The data could easily be grouped by chromosome, but I would like to avoid this, too. I expect, it'd be sort of an hassle to do multi-chromosome queries. Possibly. I honestly have no idea how partitioning works in SQLite, only in PostgreSQL. Everything would appear as a single table in PostgreSQL, and if you added some rules you'd even be able to insert/update/delete from that single table. But partitioning is not fast=true, so you'd need to do some testing to see how much it helped you. (And indeed, how much different schemes helped you). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Stored procedures in triggers
Would it be useful to have recursive DELETE triggers even without recursive INSERT or UPDATE triggers? Recursive DELETE triggers would certainly be usefull and have in fact already been asked for on this list occasionally. They would allow to move referential integrity of hierarchical data out of the application and into the database. My vote is a strong Yes! in favour of recursive DELETE triggers! Ralf Btw: Does the SQL standard say anything about recursive triggers? Is there a reserved word to make a trigger recursive or not? Could there be an (application defined) limit on INSERT and UPDATE recursions for to solve the endless loop / stack/memory overflow problem?
RE: [sqlite] no errors,but not executed: Stored procedures in triggers
When I create trigger by giving select sp_dosomethingfunc(); THE
TRIGGER IS GETTING CREATED without any errors stmts within main r
executed, but stmts within this custom function-
sp_dosomethingfunc(sqlite3_context *context, int argc,sqlite3_value
**argv)-- r not excuted. Also what is that
sqlite3_context *context doing here? Y is it used?
I want to make use of exec within this custom function. How shall I use
it?
Awaiting reply.
-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED]
Sent: Friday, March 24, 2006 8:18 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] no errors,but not executed: Stored procedures in
triggers
On 3/24/06, Chethana, Rao (IE10) [EMAIL PROTECTED] wrote:
My problem is not with cascading or using recursive triggers.
Actually,
I am using sqlite3_create_function to execute some queries(like
insert or update etc.) but the control does not pass to the custom
function at all, ie., say a user-defined function
sp_dosomethingfunc(sqlite3_context *context, int argc,sqlite3_value
**argv)
{
Do something or execute some queries...
..etc
}
/* from main() I'm calling the above function using
sqlite3_create_function*/
int main()
{
/* after using sqlite_open */
sqlite3_create_function()
/* exec function is executed, but control is
not passed to sp_dosomethingfunc, how do I make stmts inside this
custom
function get executed? */
sqlite3_exec(.)
}
/* and this sp_dosomethingfunc, I'm calling from triggers. I'm not
getting any error. But this function is not executed */
your function would be executed by something like
select sp_dosomethingfunc
Since it's a function you have to do something to evaluate it.
Since your trigger is 'calling' the function, are you sure your
trigger is being run?
Re: [sqlite] Scrolling thru an index
JP wrote: Jay Sprenkle wrote: My application is geared towards users who want to find a specific name in a list of names, and then want to have the possibility to scroll backwards or forwards. For example, if I search for Sprenkle I want to show the user a window with Sprenkle in the middle, preceded by the 50 names before it, and followed by the 50 names after it, and also to be able to smoothly scroll in either direction. I know the index contains sufficient data to do this, but there seems to be no way to use it from SQLite. Get it in two chunks, the first 100 names after the name in question: select x from mytable where Name 'sprenkle' limit 100 and the 100 names before the name in question: select x from mytable where Name 'sprenkle' limit 100 order by x desc Right, that is the way I ended up doing it. I used = instead of and added an order by to the first one (order is never guaranteed unless specifically declared). Using a UNION of those two SELECTs does not work in 3.3.4 (bug?). Executing them separately does work. Thanks, jp. Technically the SQL standard doesn't allow an order by clause on a subselect, however this is an extension that SQLite, and possibly other database engines, allow. To do what you want in standard SQL you would need two queries, the first two locate the beginning of your set of set rows, and a second to get the rows you want. select Name from mytable where Name 'Sprenkle' order by Name desc limit 1 offset 50; You would save the result of this query and pass it in as the parameter to the next query. select * from mytable where Name = ? order by Name limit 101; In SQLite these can be combined into one query that gets the desired rows. select * from mytable where Name = ( select Name from mytable where Name 'Sprenkle' order by Name desc limit 1 offset 50) order by Name limit 101; This query works as expected in SQLite so it should be a work around for your union bug. HTH Dennis Cote P.S. And now 'Sprenkle' has been raised to the same heights as 'foo' and 'bar'.
Re: [sqlite] Scrolling thru an index
In SQLite these can be combined into one query that gets the desired rows. select * from mytable where Name = ( select Name from mytable where Name 'Sprenkle' order by Name desc limit 1 offset 50) order by Name limit 101; This query works as expected in SQLite so it should be a work around for your union bug. That's pretty elegant. Nicely done! P.S. And now 'Sprenkle' has been raised to the same heights as 'foo' and 'bar'. LOL! I've been told I'm 'fubar' but hey, I've gotta work with what I have.
[sqlite] Join on same table and performance.
Hello, I guess this subject is a bit worn out. But I am having scalabillity problems with SQLite in XMMS2. We have dimensioned XMMS2 to handle insanely huge media libraries, playlists and clients. Our goal is to be able to run medialibs with 50.000 files without problem. Our backend is SQLite3. When we get somewhere around 16 rows (10k entries) we're starting to see problems with scalabillity of SQLite3. I would like some views on how we could speed up the storage backend and also comments on our structure. Today we save all data in one table called Media. Each entry has a id number and each id number can have a indefinitive number of properties. To keep this flexible and clean we choose to add one row per property. A property can be artist, album, url and more. The schema is this: create table Media (id integer, key, value, source integer); and the indexes: create unique index key_idx on Media (id,key,source); create index prop_idx on Media (key,value); create index source_idx on Media (key,source); create index key_source_val_idx on Media (key,source,value); The most common query is something like this: select value from Media where id=5 and key='url' and source=1; This query remains very fast no matter how many entries I have in my database also things like: select key, value from Media where id=5 and source=1; is still very fast. But more advanced queries like show me all albums and artists that are not compilations are very slow: select distinct m1.value as artist, ifnull(m2.value,'[unknown]') as album from Media m1 left join Media m2 on m1.id = m2.id and m2.key='album' left join Media m3 on m1.id = m3.id and m3.key='compilation' where m1.key='artist' and m3.value is null; In fact, whenever I join with myself and try to extract a big number of values it can take forever to get the result. I have tried to increase the cache_size to somewhere around 32000 to see if it made any difference, it didn't. idxchk tells me that the good indexes are in use. Any comments, help or blame is welcome to try to solve this issue of scalabillity. You can download a medialib here: http://debian.as/~skid/medialib.db.gz this contains almost 20 rows and 14000 songs and is a real user library. Looking forward to getting your input. Thanks Tobias
Re: [sqlite] Join on same table and performance.
Hi. You're joining the table with itself using 'id'. However, there's no index just on that field. I'm not sure how exactly SQLite utilises indices, but it might be unable to use index on (id,key,source) triple to optimise calculation of m1.id = m2.id. Does this sound sensible? Cheers. --- =?ISO-8859-1?Q?Tobias_Rundstr=F6m?= [EMAIL PROTECTED] wrote: Hello, I guess this subject is a bit worn out. But I am having scalabillity problems with SQLite in XMMS2. We have dimensioned XMMS2 to handle insanely huge media libraries, playlists and clients. Our goal is to be able to run medialibs with 50.000 files without problem. Our backend is SQLite3. When we get somewhere around 16 rows (10k entries) we're starting to see problems with scalabillity of SQLite3. I would like some views on how we could speed up the storage backend and also comments on our structure. Today we save all data in one table called Media. Each entry has a id number and each id number can have a indefinitive number of properties. To keep this flexible and clean we choose to add one row per property. A property can be artist, album, url and more. The schema is this: create table Media (id integer, key, value, source integer); and the indexes: create unique index key_idx on Media (id,key,source); create index prop_idx on Media (key,value); create index source_idx on Media (key,source); create index key_source_val_idx on Media (key,source,value); The most common query is something like this: select value from Media where id=5 and key='url' and source=1; This query remains very fast no matter how many entries I have in my database also things like: select key, value from Media where id=5 and source=1; is still very fast. But more advanced queries like show me all albums and artists that are not compilations are very slow: select distinct m1.value as artist, ifnull(m2.value,'[unknown]') as album from Media m1 left join Media m2 on m1.id = m2.id and m2.key='album' left join Media m3 on m1.id = m3.id and m3.key='compilation' where m1.key='artist' and m3.value is null; In fact, whenever I join with myself and try to extract a big number of values it can take forever to get the result. I have tried to increase the cache_size to somewhere around 32000 to see if it made any difference, it didn't. idxchk tells me that the good indexes are in use. Any comments, help or blame is welcome to try to solve this issue of scalabillity. You can download a medialib here: http://debian.as/~skid/medialib.db.gz this contains almost 20 rows and 14000 songs and is a real user library. Looking forward to getting your input. Thanks Tobias -- * Zoner PhotoStudio 8 - Your Photos perfect, shared, organised! www.zoner.com/zps __ Stops spam 100% for your email accounts or you get paid. http://www.cashette.com
Re: [sqlite] Join on same table and performance.
Elcin Recebli [EMAIL PROTECTED] wrote: Hi. You're joining the table with itself using 'id'. However, there's no index just on that field. I'm not sure how exactly SQLite utilises indices, but it might be unable to use index on (id,key,source) triple to optimise calculation of m1.id = m2.id. SQLite is able to use the prefix of an index. So in this case, the index on (id,key,source) would be used to optimize m1.id=m2.id. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Join on same table and performance.
El 24-03-2006, a las 16:08, [EMAIL PROTECTED] escribió: Elcin Recebli [EMAIL PROTECTED] wrote: Hi. You're joining the table with itself using 'id'. However, there's no index just on that field. I'm not sure how exactly SQLite utilises indices, but it might be unable to use index on (id,key,source) triple to optimise calculation of m1.id = m2.id. SQLite is able to use the prefix of an index. So in this case, the index on (id,key,source) would be used to optimize m1.id=m2.id. Hello, Well it definitly did something. The query execution time was down by a factor of at least 100. Thanks Elcin. -- Tobias
Re: [sqlite] Join on same table and performance.
=?ISO-8859-1?Q?Tobias_Rundstr=F6m?= [EMAIL PROTECTED] wrote: create table Media (id integer, key, value, source integer); and the indexes: create unique index key_idx on Media (id,key,source); create index prop_idx on Media (key,value); create index source_idx on Media (key,source); create index key_source_val_idx on Media (key,source,value); source_idx and key_source_val_idx are redundant. Your queries will run just as fast if you DROP source_idx and keep just key_source_val_idx. And your INSERTs, UPDATEs, and DELETEs will be a little faster, since there is one fewer index to maintain. This is just an aside - it is not the cause of your problems. But more advanced queries like show me all albums and artists that are not compilations are very slow: select distinct m1.value as artist, ifnull(m2.value,'[unknown]') as album from Media m1 left join Media m2 on m1.id = m2.id and m2.key='album' left join Media m3 on m1.id = m3.id and m3.key='compilation' where m1.key='artist' and m3.value is null; I downloaded your database and the query above was indeed slow. But then I ran ANALYZE so that SQLite can gather statistics on the various indices, then reran the query. This time, SQLite was able to use the ANALYZE results to make better index choices and the query is quite speedy. The results of ANALYZE are stored in a special table named sqlite_stat1. So you only have to run it once and the result will be used for all subsequent queries. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Scrolling thru an index
In SQLite these can be combined into one query that gets the desired rows. select * from mytable where Name = ( select Name from mytable where Name 'Sprenkle' order by Name desc limit 1 offset 50)order by Name limit 101; This query works as expected in SQLite so it should be a work around for your union bug. Very, very nice. The idea is right on! I made a tweak: The query works well, except when searching names within the first 50, i.e. if I search 'AAA', it doesn't bring anything. To fix it, I added a coalesce(x,''), where x is the inner select: select * from mytable where name = Coalesce( (select name from mytable where name 'A' order by name desc limit 1 offset 50) ,'') order by name limit 101; This works well on the full range. Thanks all! jp
Re: [sqlite] Join on same table and performance.
Friday, March 24, 2006, 2:33:36 PM, Tobias Rundström wrote: [...] The schema is this: create table Media (id integer, key, value, source integer); and the indexes: create unique index key_idx on Media (id,key,source); create index prop_idx on Media (key,value); create index source_idx on Media (key,source); create index key_source_val_idx on Media (key,source,value); I wonder what effect create table Media (id INTEGER PRIMARY KEY, key, value, source integer); would have on your query time. This would use the already built-in BTree index. e -- Doug Currie Londonderry, NH
Re: [sqlite] Join on same table and performance.
I downloaded your database and the query above was indeed slow. But then I ran ANALYZE so that SQLite can gather statistics on the various indices, then reran the query. This time, SQLite was able to use the ANALYZE results to make better index choices and the query is quite speedy. The results of ANALYZE are stored in a special table named sqlite_stat1. So you only have to run it once and the result will be used for all subsequent queries. Thank, you. This helps of course. How often should I run analyze? Trying to figure out how to make this programaticly for new installations. I guess there has to be a bit of data in the table before I can run analyze with the outcome that I want? -- Tobias
Re: [sqlite] Join on same table and performance.
=?ISO-8859-1?Q?Tobias_Rundstr=F6m?= [EMAIL PROTECTED] wrote: create table Media (id integer, key, value, source integer); and the indexes: create unique index key_idx on Media (id,key,source); create index prop_idx on Media (key,value); create index source_idx on Media (key,source); create index key_source_val_idx on Media (key,source,value); Investigating further, I see that Media.source=1 for every row in your database. What is Media.source? Does it ever have a value other than 1? Since source is always 1, there is really no point in indexing it. For the sample data set you supplied, you could just as well get by with the following two indices: CREATE UNIQUE INDEX key_idx ON media (id,key); CREATE INDEX prop_idx ON media (key,value); If in other data sets media.source takes on more a more diverse set of values, then perhaps some of the other indices above would be useful - but not in the sample data you supplied. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Join on same table and performance.
=?ISO-8859-1?Q?Tobias_Rundstr=F6m?= [EMAIL PROTECTED] wrote: I downloaded your database and the query above was indeed slow. But then I ran ANALYZE so that SQLite can gather statistics on the various indices, then reran the query. This time, SQLite was able to use the ANALYZE results to make better index choices and the query is quite speedy. The results of ANALYZE are stored in a special table named sqlite_stat1. So you only have to run it once and the result will be used for all subsequent queries. Thank, you. This helps of course. How often should I run analyze? Trying to figure out how to make this programaticly for new installations. I guess there has to be a bit of data in the table before I can run analyze with the outcome that I want? The statistics on all of your databases are likely to be very much alike. So run ANALYZE once on a sample database such as the one you posted earlier. Save off the data that the ANALYZE command writes into the sqlite_stat1 table. When creating a new database, run ANALYZE before doing any CREATE TABLEs. The ANALYZE command will create an empty sqlite_stat1 table. Then use INSERTs to populate the sqlite_stat1 table with information saved from when you ran ANALYZE on the large sample database. -- D. Richard Hipp [EMAIL PROTECTED]
[sqlite] Error when matching column names in a view
Any comments would be appreciated, SQLite version 3.3.4 Enter .help for instructions sqlite create table one (id integer primary key); sqlite create table two (id integer primary key); sqlite select one.id, two.id from one join two where one.id=two.id; sqlite create view three as select one.id, two.id from one join two where one.id=two.id; sqlite select * from three; sqlite select one.id from three; SQL error: no such column: one.id sqlite select id from three; SQL error: no such column: id sqlite create view four as select one.id as oneid, two.id as twoid from one join two where one.id=two.id; sqlite select oneid from four; sqlite The last one is a workaround to show that you can reference columns by their alias, but not their name in a view. Could this be related to http://www.sqlite.org/cvstrac/chngview?cn=3128 by any chance? Thanks! -Boris -- +1.604.689.0322 DeepCove Labs Ltd. 4th floor 595 Howe Street Vancouver, Canada V6C 2T5 [EMAIL PROTECTED] CONFIDENTIALITY NOTICE This email is intended only for the persons named in the message header. Unless otherwise indicated, it contains information that is private and confidential. If you have received it in error, please notify the sender and delete the entire message including any attachments. Thank you. smime.p7s Description: S/MIME cryptographic signature
