Re: [sqlite] [Bulk] How to optimize this simple select query ?
ok i will try On 12/9/2010 9:33 PM, Richard Hipp wrote: > On Thu, Dec 9, 2010 at 11:27 AM, Vander Clock Stephane< > svandercl...@yahoo.fr> wrote: > >> no one have an idea how to do such query ?? >> > You can try building with SQLITE_ENABLE_STAT2 and then running ANALYZE. Or, > the query you show is ideally suited for the R-Tree module. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Bulk] How to optimize this simple select query ?
> I can't solve your problem but I have observations. I don't see how any SGDB > (or RDBS as we call them) could do this quickly without lots of indexes. > but they do :( Firebird for exemple ... > Your long SELECT command is something I would probably do in my programming > language instead of SQLite. Just read every record and do the 'ifs' in my > language. I think this would actually be as fast or faster than having > SQLite do it. > yes but we speak about millionS of rows :( > But there are ways to make SQLite do it quickly. Either way you have to read > every record, so there's no way to save time there. Another way to do it > would be to add a column to your HASH table. Let's call it 'tally'. You > would start by doing > > UPDATE HASH set tally = 0; > > Then you would do 25 UPDATE commands like > > UPDATE HASH SET tally = tally + 1 WHERE x1_y1 BETWEEN #randomnumber1 AND > (#randomnumber1 + 20); > [...] > UPDATE HASH SET tally = tally + 1 WHERE x3_y2 BETWEEN #randomnumber34 AND > (#randomnumber34 + 20); > [...] > UPDATE HASH SET tally = tally + 1 WHERE x5_y5 BETWEEN #randomnumber73 AND > (#randomnumber73 + 20); > > Then to find the records which obeyed all 25 limits you'd do > > SELECT id FROM HASH WHERE tally = 25; > yes but not really a "multri thread" solutions :( > If you put 26 indexes on the table (one on each x_y and one on tally) this > should execute really quickly. It might be faster still if you make another > table just for the 'tally' column. but the row returning by just the 5 index is already zero :( so how adding more index can help ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Bulk] How to optimize this simple select query ?
> If you have another situation, > wit same amount of data, > which returns immedialty, > than either situation is not the same, > or you are making an error. > same situation (same amount of data) but on other SGBD like Firebird. the result return imediatly. on sqlite3 it's take hours :( > maybe creating indexes on more than one field, >can speed up the query, > but i cannot tell you on which fields, > because i dont know the data > i thing that 5 index is quite enalf because the number of rows returning by just the indexed column is zero ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Bulk] How to optimize this simple select query ?
On Thu, Dec 9, 2010 at 11:27 AM, Vander Clock Stephane < svandercl...@yahoo.fr> wrote: > no one have an idea how to do such query ?? > You can try building with SQLITE_ENABLE_STAT2 and then running ANALYZE. Or, the query you show is ideally suited for the R-Tree module. > > thanks > stéphane > > On 12/8/2010 7:56 PM, Vander Clock Stephane wrote: > > Hello, > > > > on the table : > > > > CREATE TABLE HASH( > > ID INTEGER PRIMARY KEY ASC, > > x1_y1 INTEGER, > > x1_y2 INTEGER, > > x1_y3 INTEGER, > > x1_y4 INTEGER, > > x1_y5 INTEGER, > > x2_y1 INTEGER, > > x2_y2 INTEGER, > > x2_y3 INTEGER, > > x2_y4 INTEGER, > > x2_y5 INTEGER, > > x3_y1 INTEGER, > > x3_y2 INTEGER, > > x3_y3 INTEGER, > > x3_y4 INTEGER, > > x3_y5 INTEGER, > > x4_y1 INTEGER, > > x4_y2 INTEGER, > > x4_y3 INTEGER, > > x4_y4 INTEGER, > > x4_y5 INTEGER, > > x5_y1 INTEGER, > > x5_y2 INTEGER, > > x5_y3 INTEGER, > > x5_y4 INTEGER, > > x5_y5 INTEGER > > ); > > CREATE INDEX HASH_X1_Y1_IDX ON HASH (X1_Y1); > > CREATE INDEX HASH_X2_Y2_IDX ON HASH (X2_Y2); > > CREATE INDEX HASH_X3_Y3_IDX ON HASH (X3_Y3); > > CREATE INDEX HASH_X4_Y4_IDX ON HASH (X4_Y4); > > CREATE INDEX HASH_X5_Y5_IDX ON HASH (X5_Y5); > > > > > > with millions of rows, how to optimize such query : > > > > Select > > ID > > from > > HASH > > where > > x1_y1>=<#randomnumber1> and > > x1_y1<=<#randomnumber1>+ 20 and > > x1_y2>=<#randomnumber4> and > > x1_y2<=<#randomnumber4> + 20 and > > x1_y3>=<#randomnumber7> and > > x1_y3<=<#randomnumber7> + 20 and > > x1_y4>=<#randomnumber10> and > > x1_y4<=<#randomnumber10> + 20 and > > x1_y5>=<#randomnumber13> and > > x1_y5<=<#randomnumber13> + 20 and > > x2_y1>=<#randomnumber16> and > > x2_y1<=<#randomnumber16> + 20 and > > x2_y2>=<#randomnumber19> and > > x2_y2<=<#randomnumber19> + 20 and > > x2_y3>=<#randomnumber22> and > > x2_y3<=<#randomnumber22> + 20 and > > x2_y4>=<#randomnumber25> and > > x2_y4<=<#randomnumber25> + 20 and > > x2_y5>=<#randomnumber28> and > > x2_y5<=<#randomnumber28> + 20 and > > x3_y1>=<#randomnumber31> and > > x3_y1<=<#randomnumber31> + 20 and > > x3_y2>=<#randomnumber34> and > > x3_y2<=<#randomnumber34> + 20 and > > x3_y3>=<#randomnumber37> and > > x3_y3<=<#randomnumber37> + 20 and > > x3_y4>=<#randomnumber40> and > > x3_y4<=<#randomnumber40> + 20 and > > x3_y5>=<#randomnumber43> and > > x3_y5<=<#randomnumber43> + 20 and > > x4_y1>=<#randomnumber46> and > > x4_y1<=<#randomnumber46> + 20 and > > x4_y2>=<#randomnumber49> and > > x4_y2<=<#randomnumber49> + 20 and > > x4_y3>=<#randomnumber52> and > > x4_y3<=<#randomnumber52> + 20 and > > x4_y4>=<#randomnumber55> and > > x4_y4<=<#randomnumber55> + 20 and > > x4_y5>=<#randomnumber58> and > > x4_y5<=<#randomnumber58> + 20 and > > x5_y1>=<#randomnumber61> and > > x5_y1<=<#randomnumber61> + 20 and > > x5_y2>=<#randomnumber64> and > > x5_y2<=<#randomnumber64> + 20 and > > x5_y3>=<#randomnumber67> and > > x5_y3<=<#randomnumber67> + 20 and > > x5_y4>=<#randomnumber70> and > > x5_y4<=<#randomnumber70> + 20 and > > x5_y5>=<#randomnumber73> and > > x5_y5<=<#randomnumber73> + 20; > > > > because they takes very very lot of time (hourS) to return :( > > on other SGBD (like Firebird) with same amount of data > > they return immediatly ... > > > > Thanks by advance > > stéphane > > ___ > > 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 > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Bulk] How to optimize this simple select query ?
On 9 Dec 2010, at 4:27pm, Vander Clock Stephane wrote: > no one have an idea how to do such query ?? I can't solve your problem but I have observations. I don't see how any SGDB (or RDBS as we call them) could do this quickly without lots of indexes. >x3_y2>=<#randomnumber34> and >x3_y2<=<#randomnumber34> + 20 and In SQLite you can write x3_y2 BETWEEN #randomnumber34 AND (#randomnumber34 + 20) Your long SELECT command is something I would probably do in my programming language instead of SQLite. Just read every record and do the 'ifs' in my language. I think this would actually be as fast or faster than having SQLite do it. But there are ways to make SQLite do it quickly. Either way you have to read every record, so there's no way to save time there. Another way to do it would be to add a column to your HASH table. Let's call it 'tally'. You would start by doing UPDATE HASH set tally = 0; Then you would do 25 UPDATE commands like UPDATE HASH SET tally = tally + 1 WHERE x1_y1 BETWEEN #randomnumber1 AND (#randomnumber1 + 20); [...] UPDATE HASH SET tally = tally + 1 WHERE x3_y2 BETWEEN #randomnumber34 AND (#randomnumber34 + 20); [...] UPDATE HASH SET tally = tally + 1 WHERE x5_y5 BETWEEN #randomnumber73 AND (#randomnumber73 + 20); Then to find the records which obeyed all 25 limits you'd do SELECT id FROM HASH WHERE tally = 25; If you put 26 indexes on the table (one on each x_y and one on tally) this should execute really quickly. It might be faster still if you make another table just for the 'tally' column. The above assumes that a small proportion of the points obeys each restriction. If most points obey most restrictions then you should set tally to 1 at start and to 0 if any restriction is /not/ true. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Bulk] How to optimize this simple select query ?
On 09-12-10 17:27, Vander Clock Stephane wrote: > no one have an idea how to do such query ?? > > thanks > stéphane > > On 12/8/2010 7:56 PM, Vander Clock Stephane wrote: >> Hello, >> >> on the table : >> >> CREATE TABLE HASH( >> . >> x5_y5>=<#randomnumber73> and >> x5_y5<=<#randomnumber73> + 20; >> >> because they takes very very lot of time (hourS) to return :( >> on other SGBD (like Firebird) with same amount of data >> they return immediatly ... > If you have another situation, wit same amount of data, which returns immedialty, than either situation is not the same, or you are making an error. maybe creating indexes on more than one field, can speed up the query, but i cannot tell you on which fields, because i dont know the data ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Bulk] How to optimize this simple select query ?
no one have an idea how to do such query ?? thanks stéphane On 12/8/2010 7:56 PM, Vander Clock Stephane wrote: > Hello, > > on the table : > > CREATE TABLE HASH( > ID INTEGER PRIMARY KEY ASC, > x1_y1 INTEGER, > x1_y2 INTEGER, > x1_y3 INTEGER, > x1_y4 INTEGER, > x1_y5 INTEGER, > x2_y1 INTEGER, > x2_y2 INTEGER, > x2_y3 INTEGER, > x2_y4 INTEGER, > x2_y5 INTEGER, > x3_y1 INTEGER, > x3_y2 INTEGER, > x3_y3 INTEGER, > x3_y4 INTEGER, > x3_y5 INTEGER, > x4_y1 INTEGER, > x4_y2 INTEGER, > x4_y3 INTEGER, > x4_y4 INTEGER, > x4_y5 INTEGER, > x5_y1 INTEGER, > x5_y2 INTEGER, > x5_y3 INTEGER, > x5_y4 INTEGER, > x5_y5 INTEGER > ); > CREATE INDEX HASH_X1_Y1_IDX ON HASH (X1_Y1); > CREATE INDEX HASH_X2_Y2_IDX ON HASH (X2_Y2); > CREATE INDEX HASH_X3_Y3_IDX ON HASH (X3_Y3); > CREATE INDEX HASH_X4_Y4_IDX ON HASH (X4_Y4); > CREATE INDEX HASH_X5_Y5_IDX ON HASH (X5_Y5); > > > with millions of rows, how to optimize such query : > > Select > ID > from > HASH > where > x1_y1>=<#randomnumber1> and > x1_y1<=<#randomnumber1>+ 20 and > x1_y2>=<#randomnumber4> and > x1_y2<=<#randomnumber4> + 20 and > x1_y3>=<#randomnumber7> and > x1_y3<=<#randomnumber7> + 20 and > x1_y4>=<#randomnumber10> and > x1_y4<=<#randomnumber10> + 20 and > x1_y5>=<#randomnumber13> and > x1_y5<=<#randomnumber13> + 20 and > x2_y1>=<#randomnumber16> and > x2_y1<=<#randomnumber16> + 20 and > x2_y2>=<#randomnumber19> and > x2_y2<=<#randomnumber19> + 20 and > x2_y3>=<#randomnumber22> and > x2_y3<=<#randomnumber22> + 20 and > x2_y4>=<#randomnumber25> and > x2_y4<=<#randomnumber25> + 20 and > x2_y5>=<#randomnumber28> and > x2_y5<=<#randomnumber28> + 20 and > x3_y1>=<#randomnumber31> and > x3_y1<=<#randomnumber31> + 20 and > x3_y2>=<#randomnumber34> and > x3_y2<=<#randomnumber34> + 20 and > x3_y3>=<#randomnumber37> and > x3_y3<=<#randomnumber37> + 20 and > x3_y4>=<#randomnumber40> and > x3_y4<=<#randomnumber40> + 20 and > x3_y5>=<#randomnumber43> and > x3_y5<=<#randomnumber43> + 20 and > x4_y1>=<#randomnumber46> and > x4_y1<=<#randomnumber46> + 20 and > x4_y2>=<#randomnumber49> and > x4_y2<=<#randomnumber49> + 20 and > x4_y3>=<#randomnumber52> and > x4_y3<=<#randomnumber52> + 20 and > x4_y4>=<#randomnumber55> and > x4_y4<=<#randomnumber55> + 20 and > x4_y5>=<#randomnumber58> and > x4_y5<=<#randomnumber58> + 20 and > x5_y1>=<#randomnumber61> and > x5_y1<=<#randomnumber61> + 20 and > x5_y2>=<#randomnumber64> and > x5_y2<=<#randomnumber64> + 20 and > x5_y3>=<#randomnumber67> and > x5_y3<=<#randomnumber67> + 20 and > x5_y4>=<#randomnumber70> and > x5_y4<=<#randomnumber70> + 20 and > x5_y5>=<#randomnumber73> and > x5_y5<=<#randomnumber73> + 20; > > because they takes very very lot of time (hourS) to return :( > on other SGBD (like Firebird) with same amount of data > they return immediatly ... > > Thanks by advance > stéphane > ___ > 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
