Re: [sqlite] Aggregate and query limit
Hi, Yes, You are right. How SQLite will know the position of the row 990 unless it ordered by indexed field. That takes me to another point, How SQLite exactly interpret the query, I read in the documentation "After the parser assembles tokens into complete SQL statements, it calls the code generator to produce virtual machine code that will do the work that the SQL statements request", And in another part "The program generated by the code generator is executed by the virtual machine" So, I have two questions about that mechanism of executing the quires: - What is the language type that's the code generator is generating? - Can i skip the SQL parsing and provide ready compiled statement to be executed by the VM and tells SQLite what exactly to do, Not what the generator understands from my SQL.? I know if it possible writing pre-compiled quires may cause compatiblity problems with the SQLite future releases but please let me know if it possible. Thanks, Mina.
Re: [sqlite] To increase search speed
I think method 3 shows the best promise: Obviously M4 is out since it is in memory only. Besides caching should help improve the performance. The qustion that begs to be asked is: What are your performance goals? And what are your performance numbers ie timings for each of the mehtods you indicated. Re-run and capture timing for the various selects... Select distinct AlbumName from MUSIC; -> Select distinct albumName from albums; Select distinct ArtistName from MUSIC; -> select distinc artistName from artists; Select distinct GenreName from MUSIC; -> select distinct genreName from genres; "Sreedhar.a" <[EMAIL PROTECTED]> wrote: Hi I am working in 3.3.6. I have created a table with 4 records with 12 fields as follows. "create table MUSIC (Id integer primary key, AlbumName text not null collate nocase,Track text not null collate nocase,ArtistName text not null collate nocase,URL text not null collate nocase , Duration integer, TrackFormat text not null collate nocase, BitRate integer, sampleRate integer, Channels integer, Filesize integer GenreName text not null collate nocase);" I will often search for the following fields only. Select distinct AlbumName from MUSIC; Select distinct ArtistName from MUSIC; Select distinct GenreName from MUSIC; Select distinct AlbumName for particular ArtistName Select Track for particular AlbumName Select distinct ArtistName for particular GenreName To obtain nice search speed which method will work fine. I have tried wilth the following methods. Method 1: It's the one described above Method 2: By doing indexing.I tried with the following. "create index Musicidx1 on MUSIC(ArtistName collate nocase,AlbumName collate nocase);" "create index Musicidx2 on MUSIC(AlbumName collate nocase,ArtistName collate nocase,URL collate nocase);" This gives better performance than method 1 for the following searches; Select distinct AlbumName for particular ArtistName Select Track for particular AlbumName Select distinct ArtistName for particular GenreName Method 3: Joining Multiple tables. I have created 4 tables as follows; "create table ALBUMS (id integer primary key,Album text,unique(Album));" "create table ARTISTS (id integer primary key,Artist text,unique(Artist));" "create table GENRES (id integer primary key,Genre text,unique(Genre));" "create table MUSIC (Id integer primary key, AlbumName text not null collate nocase,Track text not null collate nocase,ArtistName text not null collate nocase,URL text not null collate nocase , Duration integer, TrackFormat text not null collate nocase, BitRate integer, sampleRate integer, Channels integer, Filesize integer,album_id integer,artist_id integer, GenreName text not null collate nocase ,Genre_Id integer);" Here album_id , artist_id and Genre_Id are the id values of ALBUMS,ARTISTS,GENRES Tables. This shows better performance than indexing for the following searches. Select distinct AlbumName from MUSIC; Select distinct ArtistName from MUSIC; Select distinct GenreName from MUSIC; Method 4: Inmemory method.I will copy all the content from the temporary database to inmemory and then performing search. If I am using this method means then while inserting records , that record will be inserted into the temporary memory only. But I want to be inserted in to the original database also. Is there any other way to do this. Can any one help to increase my search speed. Thanks in Advance. Regards, Sreedhar
Re: [sqlite] Columns from nested joins aren't properly propagated
=/ Well... that was just to confirm that there's no way to make those queries work... As we say, hope is the last to die... Thanks a lot for your attention. Bruno On 8/17/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > Given that this issue has existed for years, and many simple workarounds > are known, I doubt it will change any time soon. If you want to use > SQLite, you have to rewrite the queries. The benefit of changing the > generated SQL is that it will work on all the databases you mentioned. > > The sqlite source code is available. You can modify it as see you fit. > > --- "Bruno S. Oliveira" <[EMAIL PROTECTED]> wrote: > > As I told you, I know how to avoid this. But I can't rewrite these > > queries. That's my problem... > > > > Thanks for your attention. > > > > Regards, > > Bruno > > > > On 8/17/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > > > SELECT t_dados.id > > > FROM Points1 LEFT JOIN t_dados ON Points1.object_id = t_dados.id > > > ORDER BY t_dados.id, Points1.geom_id; > > > > > > --- "Bruno S. Oliveira" <[EMAIL PROTECTED]> wrote: > > > > I'm having problems with the following query (and, in general, in > > > > queries using left joins): > > > > > > > > [EMAIL PROTECTED]:~$ sqlite3 parana > > > > SQLite version 3.4.1 > > > > Enter ".help" for instructions > > > > sqlite> SELECT t_dados.id FROM (Points1 LEFT JOIN t_dados ON > > > > Points1.object_id = t_dados.id) WHERE 1 = 1 AND 1 = 1 ORDER BY > > > > t_dados.id, Points1.geom_id; > > > > SQL error: no such column: t_dados.id > > > > > > Choose the right car based on your needs. Check out Yahoo! Autos new Car > Finder tool. > http://autos.yahoo.com/carfinder/ > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- /** * Bruno S. Oliveira * Bacharel em Ciência da Computação * Mestrando em Inteligência Computacional * http://www.inf.ufpr.br/brunoso/ * * http://www.last.fm/user/bsoliveira/ */
Re: [sqlite] Increasing performance of joins with a group by clause?
On 19/08/2007 4:01 AM, Jef Driesen wrote: Suppose I have two related tables: CREATE TABLE events ( id INTEGER PRIMARY KEY NOT NULL, place_id INTEGER ); CREATE TABLE places ( id INTEGER PRIMARY KEY NOT NULL, name TEXT ); INSERT INTO places (id, name) VALUES (1, 'Place A'); INSERT INTO places (id, name) VALUES (2, 'Place B'); INSERT INTO places (id, name) VALUES (3, 'Place C'); INSERT INTO events (id, place_id) VALUES (1, 1); INSERT INTO events (id, place_id) VALUES (2, 2); INSERT INTO events (id, place_id) VALUES (3, 1); Now, I want to count the number of 'events' at every 'place'. I started with a simple join and a group by clause to perform the count: SELECT name, count (*) AS count FROM events LEFT JOIN places ON places.id = events.place_id GROUP BY events.place_id; name|count Place A|2 Place B|1 It executes very fast (because the join can take advantage of the primary key) but does not produce the desired result. As you can see, not all places are included in the result: So I changed swapped the tables in the join: SELECT name, count (events.place_id) AS count FROM places LEFT JOIN events ON events.place_id = places.id GROUP BY places.id; name|count Place A|2 Place B|1 Place C|1 Folks are dumb where I come from; can someone please explain how this could be correct? The INSERT statements create 2 events at place_id 1 (A), 1 event at place_id 2 (B) and *ZERO* events at place_id 3 (C). Please pardon me if this is strange behaviour (I'm new to this mailing list) but I actually *ran* the OP's code, with this result: C:\junk>sqlite3 junk.sq3 SQLite version 3.4.2 Enter ".help" for instructions sqlite> .read outer_join_query.sql --- query 1 --- Place A|2 Place B|1 --- query 2 --- Place A|2 Place B|1 Place C|0 --- query 3 --- Place A|2 Place B|1 Place C|0 sqlite> Cheers, John - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Increasing performance of joins with a group by clause?
Jef Driesen <[EMAIL PROTECTED]> wrote: Is there a difference between using a primary key or another index for a join? There is a slight difference. When SQLite looks up by ROWID (which INTEGER PRIMARY KEY column maps to) it jumps directly to the record, and has all the fields in it easily available. When looking up by any other index, the engine finds an entry in the index which contains all the fields comprising the index, as well as the ROWID of the record in the table. If the query nees some columns in the record that are not contained in the index, SQLite has to perform another lookup, by ROWID in the table. In your particular query, all the necessary data is in the index and the second lookup is not necessary. So you won't see any difference. And what is the advantage (if there is one) over not using a join at all: SELECT name, (SELECT count (*) FROM events WHERE events.place_id = places.id) AS count FROM places; None. I expect the execution plan to be almost identical for these two statements. You still need an index on events(place_id) if you want this query to run fast. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] To increase search speed
use sqlite statements. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: "Sreedhar.a" <[EMAIL PROTECTED]> Date: Saturday, August 18, 2007 10:52 am Subject: [sqlite] To increase search speed > Hi > I am working in 3.3.6. > I have created a table with 4 records with 12 fields as follows. > > "create table MUSIC (Id integer primary key, AlbumName text not > null collate > nocase,Track text not null collate nocase,ArtistName text not null > collatenocase,URL text not null collate nocase , Duration integer, > TrackFormat text > not null collate nocase, BitRate integer, sampleRate integer, Channels > integer, Filesize integer GenreName text not null collate nocase);" > > I will often search for the following fields only. > >Select distinct > AlbumName from MUSIC; >Select distinct > ArtistName from MUSIC; >Select distinct > GenreName from MUSIC; >Select distinct > AlbumName for particular ArtistName >Select Track for > particular AlbumName >Select distinct > ArtistName for particular GenreName > > To obtain nice search speed which method will work fine. > I have tried wilth the following methods. > > Method 1: > > It's the one described above > > Method 2: > > By doing indexing.I tried with the following. > >"create index Musicidx1 on MUSIC(ArtistName collate > nocase,AlbumNamecollate nocase);" >"create index Musicidx2 on MUSIC(AlbumName collate > nocase,ArtistNamecollate nocase,URL collate nocase);" > > This gives better performance than method 1 for the following > searches; > > Selectdistinct AlbumName for particular ArtistName > > SelectTrack for particular AlbumName > > Selectdistinct ArtistName for particular GenreName > > Method 3: > > Joining Multiple tables. > I have created 4 tables as follows; > >"create table ALBUMS (id integer primary key,Album > text,unique(Album));" >"create table ARTISTS (id integer primary key,Artist > text,unique(Artist));" >"create table GENRES (id integer primary key,Genre > text,unique(Genre));" > >"create table MUSIC (Id integer primary key, AlbumName > text not > null collate nocase,Track text not null collate nocase,ArtistName > text not > null collate nocase,URL text not null collate nocase , Duration > integer,TrackFormat text not null collate nocase, BitRate integer, > sampleRateinteger, Channels integer, Filesize integer,album_id > integer,artist_idinteger, GenreName text not null collate nocase > ,Genre_Id integer);" > > Here album_id , artist_id and Genre_Id are the id values of > ALBUMS,ARTISTS,GENRES Tables. > This shows better performance than indexing for the following > searches. > > Select distinct AlbumName from MUSIC; > > Select distinct ArtistName from MUSIC; > > Select distinct GenreName from MUSIC; > Method 4: > > Inmemory method.I will copy all the content from the temporary > database to > inmemory and then performing search. > If I am using this method means then while inserting records , that > recordwill be inserted into the temporary memory only. > But I want to be inserted in to the original database also. Is > there any > other way to do this. > > Can any one help to increase my search speed. > Thanks in Advance. > > Regards, > Sreedhar > > > > > > > > > > > > > > > > > > > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Aggregate and query limit
--- Mina R Waheeb <[EMAIL PROTECTED]> wrote: > Do you mean the LIMIT in SQLite is not optimized? and the performance > of selecting from table contains 1000 rows match the query condation > equal selecting the same condation with limit 990,10? Pretty much, yes, as it should be. LIMIT is optimized correctly. It is not as simplistic as you think. SQLite uses btrees internally to store row data and index data. There are no table row counts stored anywhere in the database. If a user selects all rows in a 5 row table, say: select * from t; 30 2 17 90 5 and then they go "select * from t limit 3 offset 2;" most would scream bloody murder if the following rows were not returned: 17 90 5 but in fact, because it is an unordered SELECT, any 3 rows could technically be returned. But there is an implicit ordering that the SQLite and MySQL users have grown accustomed to seeing. (LIMIT is not in the SQL standard). So even when no ORDER BY and no GROUP BY is used, you need to scan at least LIMIT+OFFSET rows anyway in order to get the result. Look at select.c and use "EXPLAIN SELECT ..." and examine the VDBE instructions yourself for your queries and schema. Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase. http://farechase.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Unusual use of the LIKE operator (long)
Conventional usage is as follows: ... WHERE column-name LIKE "string" Which, of course, selects rows where the values in the named column match the string, which can, and usually does, have wildcards. I have been using an inverted arrangement: ... WHERE "string" LIKE column-name This allows the wildcards to be in the named column of the database. What possible use could this be? It's hard to even get your head around it. _*Example*_ I have an application that wants to apply changes to images, using the fantastic ImageMagick program. Generally, these are simple size reductions, and the same IM options can be used in broad categories -- with a few exceptions. I have two groups of images, named M.##.tif and HG.##.tif. The IM options are to be different for the two categories, M and HG, but the same within those categories -- with those few exceptions. This could easily get out of hand and become a programming nightmare! Here are the few SQL records (table:OPTIONS) that do this: ___key__priority_ _options_ M.23.% 0options for image M.23 M.70.% 0options for image M.70 HG.01.%0options for image HG.01 M.%9default options for category M HG.% 9default options for category HG The search goes like this: ... WHERE "file-name" LIKE key ORDER BY priority LIMIT 1 I use the LIMIT 1 because each options field has the __complete__ set of options for that case, but a different LIMIT (e.g. 2) could be used to accommodate //additional/ /options for specific images. One can easily see how this can also be extended to provide different options for other image types, perhaps .jpg or .gif (M.%.GIF 8 options). _*Other Applications*_ These are some other applications where I have used table column names in the right argument of the LIKE operator. _*Decision Tables*_ The condition stub is put into the database, and the "don't care" entries are easily represented with LIKE wildcards. This doesn't save having to make those tests, but the operation using SQL is simple to implement. _*Finite State Machine*_ The state transition tuples are represented in SQL, again with don't care positions coded as LIKE wildcards. -- Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA Genealogy, et Cetera: http://freepages.rootsweb.com/~dav4is/ 480 ancestral & collateral families, mostly 17°-19° century New England & European roots. Total population: 117,600+ Annex: http://www.gencircles.com/users/dav4is/ email: [EMAIL PROTECTED] Two roads diverged in a wood, and I... I took the one less traveled by, and that has made all the difference. -Robert Frost - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Aggregate and query limit
On 8/18/07, Mina R Waheeb <[EMAIL PROTECTED]> wrote: > Hi, > Thanks for reply. I'm still confused about that. I don't know much > about SQLite internals, But what i understanded from your reply that > SQLite fetch any row match the query condation and then apply to it > the GROUP BY then apply to it the ORDER BY is there is any, And when > the LIMIT appears SQLite keep fetching the result from the "start" > until the result set size equals the requested limit. > > Do you mean the LIMIT in SQLite is not optimized? and the performance > of selecting from table contains 1000 rows match the query condation > equal selecting the same condation with limit 990,10? Yes. The limit clause just skips the number of records you want from the result set. I haven't checked the code, but I believe you still gain something because you can also avoid reading full rows if you don't need to (like having an index - or an autogenerated temporary index - you can use) when sqlite does the initial "skip" part. Regards, ~Nuno Lucas > > Thanks, > Mina. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Odd error on "BEGIN IMMEDIATE"
OK, I understand. Must have miss read teh docs.. thanks, Scott Trevor Talbot wrote: On 8/17/07, Scott Derrick <[EMAIL PROTECTED]> wrote: exec a "BEGIN IMMEDIATE", with a sleep loop if I can't acquire the reserved lock. Then prepare, step, finalize, exit the function When I come back into the function and exec a "BEGIN IMMEDIATE" I get an error "Cannot start a transaction within a transaction". Whats wrong? Doesn't sqlite3_finalize(stmt), release the locks, deletes the prepared statement and causes the database to be updated? It deletes the prepared statement. The statement's action would have been performed by a previous sqlite3_step(). Locks and database updates are another matter, because... Why does the engine think I'm still in a transaction? ...you started a transaction with BEGIN. Unless the statement you stepped is a COMMIT, your transaction isn't done yet :) Exec a COMMIT at the end of the loop. Incidentally, you can keep the prepared statement around if it's appropriate. You can open the database, prepare the statement, and only step() and reset() within the loop. This way you don't have to keep preparing it over and over again. You must finalize() it before closing the database, though. - To unsubscribe, send email to [EMAIL PROTECTED] - -- - The most effectual engines for [pacifying a nation] are the public papers... [A despotic] government always [keeps] a kind of standing army of newswriters who, without any regard to truth or to what should be like truth, [invent] and put into the papers whatever might serve the ministers. Thomas Jefferson to G. K. van Hogendorp Oct. 13, 1785. (*) ME 5:181, Papers 8:632 - To unsubscribe, send email to [EMAIL PROTECTED] -
