RE: [sqlite]:Using sqlite3_progress_handler for GUI application
Hi, Thankyou very much for the suggestions. Best Regards, A.Sreedhar. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 19, 2007 12:41 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite]:Using sqlite3_progress_handler for GUI application Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > On Tue, Dec 18, 2007 at 12:24:25PM +, Simon Davies wrote: > > > Prob shd be http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor > > One question - using the example mentioned there: > > If we've created an index: CREATE INDEX example1 ON tracks(singer, > title); > > So, it'll make the query, like below, much faster: > > SELECT title FROM tracks > WHERE singer='Madonna' >AND title<:firsttitle > ORDER BY title DESC > LIMIT 5; > > but I understand, that when I'll try to add in the query a field > not covered by index "example1", like this: > > SELECT title FROM tracks > WHERE singer='Madonna' >AND title<:firsttitle >AND year_ed > 1985; > > so, then I'm losing every profit from having "example1" index, > right? Or perhaps "not quite every", and some speedup still remains - > just because _some_ columns are covered by example1 anyway? The index is still used to speed the search. But the extra "AND year_ed>1985" term requires SQLite to check each row coming out of the index an discard those for which the condition is not true. This might be a small or a large loss in performance, depending on how many rows match the condition. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] :Using sqlite3_progress_handler for GUI application
Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > On Tue, Dec 18, 2007 at 02:10:02PM -0600, John Stanton wrote: > > > It won't be ignored. If you have an index ordered in the sequence > > specified by the ORDER BY Sqlite will use that order instead of having > > to assemble the result set and sort it. > > To say it precisely: although it won't be ignored - but (in the case > mentioned above) it won't be used anyway, and won't spoil the speed gain, > given by index, right? > > I'm asking, because tries with new database I prefer to make without > indexing, and add indexes later. So I was wondering, should I then check the > code again, to remove some "ORDER BY"-s, or can I skip it. Always include the ORDER BY. SQLite will automatically omit the sorting step if it decides to run the query using an index that outputs the result in sorted order. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite]:Using sqlite3_progress_handler for GUI application
Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > On Tue, Dec 18, 2007 at 12:24:25PM +, Simon Davies wrote: > > > Prob shd be http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor > > One question - using the example mentioned there: > > If we've created an index: CREATE INDEX example1 ON tracks(singer, title); > > So, it'll make the query, like below, much faster: > > SELECT title FROM tracks > WHERE singer='Madonna' >AND title<:firsttitle > ORDER BY title DESC > LIMIT 5; > > but I understand, that when I'll try to add in the query a field not > covered by index "example1", like this: > > SELECT title FROM tracks > WHERE singer='Madonna' >AND title<:firsttitle >AND year_ed > 1985; > > so, then I'm losing every profit from having "example1" index, right? Or > perhaps "not quite every", and some speedup still remains - just because > _some_ columns are covered by example1 anyway? The index is still used to speed the search. But the extra "AND year_ed>1985" term requires SQLite to check each row coming out of the index an discard those for which the condition is not true. This might be a small or a large loss in performance, depending on how many rows match the condition. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite]:Using sqlite3_progress_handler for GUI application
On Tue, Dec 18, 2007 at 12:24:25PM +, Simon Davies wrote: > Prob shd be http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor One question - using the example mentioned there: If we've created an index: CREATE INDEX example1 ON tracks(singer, title); So, it'll make the query, like below, much faster: SELECT title FROM tracks WHERE singer='Madonna' AND title<:firsttitle ORDER BY title DESC LIMIT 5; ...but I understand, that when I'll try to add in the query a field not covered by index "example1", like this: SELECT title FROM tracks WHERE singer='Madonna' AND title<:firsttitle AND year_ed > 1985; ...so, then I'm losing every profit from having "example1" index, right? Or perhaps "not quite every", and some speedup still remains - just because _some_ columns are covered by example1 anyway? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite]:Using sqlite3_progress_handler for GUI application
If you store the last key and have an index on that key you will get each 100 at the same speed by getting the first 100 greater than your highest last key. Sreedhar.a wrote: Hi, Say,I am having 4 entries in the table ALBUM. "CREATE TABLE ALBUM (AlbumtId INTEGER PRIMARY KEY NOT NULL,Album TEXT NOT NULL COLLATE NOCASE DEFAULT 'Unknown',UNIQUE(Album));" Now I want to list the first 100 Album from ALBUM table.The result has to be in sorting order.So my querry is like. "SELECT AlbumId ,Album FROM ALBUM ORDER BY Album LIMIT 100 OFFSET 0;" So for the next time I will change the offset to 200 then 300 ... But the search Speed is fast initially and slows down later since it has 4 records.Since I want to display the results in the GUI Is there any other way to can increase my speed. I came across sqlite3_progress_handler() .Will this be better for my case.If so kindly suggest where I can read to know more about this. if ( sqlite3_prepare(pst_SqliteCallback->db"SELECT AlbumId ,Album FROM ALBUM ORDER BY Album;",-1,_SearchPrepareStmt,0)!= SQLITE_OK) { return SQLITE_EXEC_ERROR; } u32_ReturnStatus = sqlite3_step(pst_SearchPrepareStmt); sqlite3_progress_handler(db,26,xCallback,pArg);>Is this the right place. u32_NumCols = sqlite3_column_count(pst_SearchPrepareStmt); ps8_ColumnName = sqliteMalloc(2*u32_NumCols*sizeof(const char *) + 1); while( u32_ReturnStatus == SQLITE_ROW ) { for(u32_Count = 0; u32_Count < u32_NumCols; u32_Count++) { ps8_ColumnName[u32_Count] = sqlite3_column_name(pst_SearchPrepareStmt, u32_Count); u8_SearchResult=sqlite3_column_text(pst_SearchPrepareStmt, u32_Count); } u32_ReturnStatus = sqlite3_step(pst_SearchPrepareStmt); } u32_ReturnStatus = sqlite3_finalize(pst_SearchPrepareStmt); Kindly help to solve this. Regards, Sreedhar -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 04, 2007 9:56 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite:Deletion in Joins method Sreedhar.a wrote: CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text); CREATE TABLE ARTIST (ArtistId INTEGER NOT NULL PRIMARY KEY,Artist Text); CREATE TABLE BGM (BgmId INTEGER NOT NULL PRIMARY KEY,Bgm Text); CREATE TABLE MUSIC (Id INTEGER NOT NULL PRIMARY KEY,Album_Id INTEGER CONSTRAINT fk_Album_id REFERENCES ALBUM(AlbumId) ON DELETE CASCADE,Artist_Id INTEGER NOT NULL CONSTRAINT fk_Artist_id REFERENCES ARTIST(ArtistId) ON DELETE CASCADE,Bgm_Id INTEGER NOT NULL CONSTRAINT fk_Bgm_id REFERENCES BGM(BgmId)ON DELETE CASCADE ); I created a trigger as follows . Here I am checking after deleting that record in Music Table I will check wheather that Artist_id is present now in that table MUSIC i.e, (SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = OLD.Artist_Id).And if that Artist_Id is Null then I will delete it in the ARTIST table. But this is not happening with the below trigger. Do I need to add more constraints in the below trigger. Please help to solve this. "CREATE TRIGGER fkdc_MUSIC AFTER DELETE ON MUSIC FOR EACH ROW BEGIN SELECT CASE WHEN (SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = OLD.Artist_Id) IS NOT NULL THEN 'DELETE FROM ARTIST WHERE ArtistId=OLD.Artist_Id' END; END;" You need to use a conditional delete in your trigger. You can't do that using a select with a case statement. You could try something like this (untested): CREATE TRIGGER fkdc_MUSIC AFTER DELETE ON MUSIC FOR EACH ROW BEGIN delete from Artist where ArtistId = old.ArtistId and not exist (select id from music where ArtistId = old.ArtistId); delete from Album where AlbumId = old.AlbumId and not exist (select id from music where AlbumId = old.AlbumId); delete from BGM where BgmId = old.BgmId and not exist (select id from music where BgmId = old.BgmId); END;" A couple of other things to note: You should probably change your table definitions to key the phrase "INTEGER PRIMARY KEY" together so that sqlite can use its btree key optimization. Instead of this: CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text); use this: CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL, Album Text); Also, if you want to speed up the searches in the deletes trigger above, at the expense of slowing down all the insert and delete operations into the tables, you could add indexes on the individual Id columns in the music table. create index MusicArtistId on Muisc(ArtistId); create index MusicAlbumId on Muisc(AlbumId); create index MusicBgmId on Muisc(BgmId); HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED]
Re: [sqlite]:Using sqlite3_progress_handler for GUI application
FWIW, I notice the window title on the CVSTrac-generated pages at sqlite.org is "Sqlite CVSTrace". I'm guessing it should be "SQLite CVSTrac". - Original Message From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Tuesday, December 18, 2007 4:18:52 AM Subject: Re: [sqlite]:Using sqlite3_progress_handler for GUI application "Sreedhar.a" <[EMAIL PROTECTED]> wrote: > Hi, > > Say,I am having 4 entries in the table ALBUM. > > "CREATE TABLE ALBUM (AlbumtId INTEGER PRIMARY KEY NOT NULL,Album TEXT NOT > NULL COLLATE NOCASE DEFAULT 'Unknown',UNIQUE(Album));" > > Now I want to list the first 100 Album from ALBUM table.The result has to be > in sorting order.So my querry is like. > > "SELECT AlbumId ,Album FROM ALBUM ORDER BY Album LIMIT 100 OFFSET 0;" > > So for the next time I will change the offset to 200 then 300 ... > But the search Speed is fast initially and slows down later since it has > 4 records. http://www.sqlite.org/cvstrac/wiki?ScrollingCursor -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite]:Using sqlite3_progress_handler for GUI application
Prob shd be http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor On 18/12/2007, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > "Sreedhar.a" <[EMAIL PROTECTED]> wrote: > > Hi, > > > > Say,I am having 4 entries in the table ALBUM. > > > > "CREATE TABLE ALBUM (AlbumtId INTEGER PRIMARY KEY NOT NULL,Album TEXT NOT > > NULL COLLATE NOCASE DEFAULT 'Unknown',UNIQUE(Album));" > > > > Now I want to list the first 100 Album from ALBUM table.The result has to be > > in sorting order.So my querry is like. > > > > "SELECT AlbumId ,Album FROM ALBUM ORDER BY Album LIMIT 100 OFFSET 0;" > > > > So for the next time I will change the offset to 200 then 300 ... > > But the search Speed is fast initially and slows down later since it has > > 4 records. > > http://www.sqlite.org/cvstrac/wiki?ScrollingCursor > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite]:Using sqlite3_progress_handler for GUI application
"Sreedhar.a" <[EMAIL PROTECTED]> wrote: > Hi, > > Say,I am having 4 entries in the table ALBUM. > > "CREATE TABLE ALBUM (AlbumtId INTEGER PRIMARY KEY NOT NULL,Album TEXT NOT > NULL COLLATE NOCASE DEFAULT 'Unknown',UNIQUE(Album));" > > Now I want to list the first 100 Album from ALBUM table.The result has to be > in sorting order.So my querry is like. > > "SELECT AlbumId ,Album FROM ALBUM ORDER BY Album LIMIT 100 OFFSET 0;" > > So for the next time I will change the offset to 200 then 300 ... > But the search Speed is fast initially and slows down later since it has > 4 records. http://www.sqlite.org/cvstrac/wiki?ScrollingCursor -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite]:Using sqlite3_progress_handler for GUI application
Hi, Say,I am having 4 entries in the table ALBUM. "CREATE TABLE ALBUM (AlbumtId INTEGER PRIMARY KEY NOT NULL,Album TEXT NOT NULL COLLATE NOCASE DEFAULT 'Unknown',UNIQUE(Album));" Now I want to list the first 100 Album from ALBUM table.The result has to be in sorting order.So my querry is like. "SELECT AlbumId ,Album FROM ALBUM ORDER BY Album LIMIT 100 OFFSET 0;" So for the next time I will change the offset to 200 then 300 ... But the search Speed is fast initially and slows down later since it has 4 records.Since I want to display the results in the GUI Is there any other way to can increase my speed. I came across sqlite3_progress_handler() .Will this be better for my case.If so kindly suggest where I can read to know more about this. if ( sqlite3_prepare(pst_SqliteCallback->db"SELECT AlbumId ,Album FROM ALBUM ORDER BY Album;",-1,_SearchPrepareStmt,0)!= SQLITE_OK) { return SQLITE_EXEC_ERROR; } u32_ReturnStatus = sqlite3_step(pst_SearchPrepareStmt); sqlite3_progress_handler(db,26,xCallback,pArg);>Is this the right place. u32_NumCols = sqlite3_column_count(pst_SearchPrepareStmt); ps8_ColumnName = sqliteMalloc(2*u32_NumCols*sizeof(const char *) + 1); while( u32_ReturnStatus == SQLITE_ROW ) { for(u32_Count = 0; u32_Count < u32_NumCols; u32_Count++) { ps8_ColumnName[u32_Count] = sqlite3_column_name(pst_SearchPrepareStmt, u32_Count); u8_SearchResult=sqlite3_column_text(pst_SearchPrepareStmt, u32_Count); } u32_ReturnStatus = sqlite3_step(pst_SearchPrepareStmt); } u32_ReturnStatus = sqlite3_finalize(pst_SearchPrepareStmt); Kindly help to solve this. Regards, Sreedhar -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 04, 2007 9:56 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite:Deletion in Joins method Sreedhar.a wrote: > CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text); > CREATE TABLE ARTIST (ArtistId INTEGER NOT NULL PRIMARY KEY,Artist > Text); CREATE TABLE BGM (BgmId INTEGER NOT NULL PRIMARY KEY,Bgm Text); > > CREATE TABLE MUSIC (Id INTEGER NOT NULL PRIMARY KEY,Album_Id INTEGER > CONSTRAINT fk_Album_id REFERENCES ALBUM(AlbumId) ON DELETE > CASCADE,Artist_Id INTEGER NOT NULL CONSTRAINT fk_Artist_id REFERENCES > ARTIST(ArtistId) ON DELETE CASCADE,Bgm_Id INTEGER NOT NULL CONSTRAINT > fk_Bgm_id REFERENCES BGM(BgmId)ON DELETE CASCADE ); > > I created a trigger as follows . > > Here I am checking after deleting that record in Music Table I will > check wheather that Artist_id is present now in that table MUSIC i.e, > (SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = > OLD.Artist_Id).And if that Artist_Id is Null then I will delete it in > the ARTIST table. > > But this is not happening with the below trigger. > Do I need to add more constraints in the below trigger. > Please help to solve this. > > "CREATE TRIGGER fkdc_MUSIC > AFTER DELETE ON MUSIC > FOR EACH ROW > BEGIN > SELECT CASE > WHEN > (SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = OLD.Artist_Id) > IS NOT NULL THEN 'DELETE FROM ARTIST WHERE ArtistId=OLD.Artist_Id' > END; > END;" > > > You need to use a conditional delete in your trigger. You can't do that using a select with a case statement. You could try something like this (untested): CREATE TRIGGER fkdc_MUSIC AFTER DELETE ON MUSIC FOR EACH ROW BEGIN delete from Artist where ArtistId = old.ArtistId and not exist (select id from music where ArtistId = old.ArtistId); delete from Album where AlbumId = old.AlbumId and not exist (select id from music where AlbumId = old.AlbumId); delete from BGM where BgmId = old.BgmId and not exist (select id from music where BgmId = old.BgmId); END;" A couple of other things to note: You should probably change your table definitions to key the phrase "INTEGER PRIMARY KEY" together so that sqlite can use its btree key optimization. Instead of this: CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text); use this: CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL, Album Text); Also, if you want to speed up the searches in the deletes trigger above, at the expense of slowing down all the insert and delete operations into the tables, you could add indexes on the individual Id columns in the music table. create index MusicArtistId on Muisc(ArtistId); create index MusicAlbumId on Muisc(AlbumId); create index MusicBgmId on Muisc(BgmId); HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite]:Using sqlite3_progress_handler for GUI application
Hi, Say,I am having 4 entries in the table ALBUM. "CREATE TABLE ALBUM (AlbumtId INTEGER PRIMARY KEY NOT NULL,Album TEXT NOT NULL COLLATE NOCASE DEFAULT 'Unknown',UNIQUE(Album));" Now I want to list the first 100 Album from ALBUM table.The result has to be in sorting order.So my querry is like. "SELECT AlbumId ,Album FROM ALBUM ORDER BY Album LIMIT 100 OFFSET 0;" So for the next time I will change the offset to 200 then 300 ... But the search Speed is fast initially and slows down later since it has 4 records.Since I want to display the results in the GUI Is there any other way to can increase my speed. I came across sqlite3_progress_handler() .Will this be better for my case.If so kindly suggest where I can read to know more about this. if ( sqlite3_prepare(pst_SqliteCallback->db"SELECT AlbumId ,Album FROM ALBUM ORDER BY Album;",-1,_SearchPrepareStmt,0)!= SQLITE_OK) { return SQLITE_EXEC_ERROR; } u32_ReturnStatus = sqlite3_step(pst_SearchPrepareStmt); sqlite3_progress_handler(db,26,xCallback,pArg);>Is this the right place. u32_NumCols = sqlite3_column_count(pst_SearchPrepareStmt); ps8_ColumnName = sqliteMalloc(2*u32_NumCols*sizeof(const char *) + 1); while( u32_ReturnStatus == SQLITE_ROW ) { for(u32_Count = 0; u32_Count < u32_NumCols; u32_Count++) { ps8_ColumnName[u32_Count] = sqlite3_column_name(pst_SearchPrepareStmt, u32_Count); u8_SearchResult=sqlite3_column_text(pst_SearchPrepareStmt, u32_Count); } u32_ReturnStatus = sqlite3_step(pst_SearchPrepareStmt); } u32_ReturnStatus = sqlite3_finalize(pst_SearchPrepareStmt); Kindly help to solve this. Regards, Sreedhar -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 04, 2007 9:56 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite:Deletion in Joins method Sreedhar.a wrote: > CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text); > CREATE TABLE ARTIST (ArtistId INTEGER NOT NULL PRIMARY KEY,Artist > Text); CREATE TABLE BGM (BgmId INTEGER NOT NULL PRIMARY KEY,Bgm Text); > > CREATE TABLE MUSIC (Id INTEGER NOT NULL PRIMARY KEY,Album_Id INTEGER > CONSTRAINT fk_Album_id REFERENCES ALBUM(AlbumId) ON DELETE > CASCADE,Artist_Id INTEGER NOT NULL CONSTRAINT fk_Artist_id REFERENCES > ARTIST(ArtistId) ON DELETE CASCADE,Bgm_Id INTEGER NOT NULL CONSTRAINT > fk_Bgm_id REFERENCES BGM(BgmId)ON DELETE CASCADE ); > > I created a trigger as follows . > > Here I am checking after deleting that record in Music Table I will > check wheather that Artist_id is present now in that table MUSIC i.e, > (SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = > OLD.Artist_Id).And if that Artist_Id is Null then I will delete it in > the ARTIST table. > > But this is not happening with the below trigger. > Do I need to add more constraints in the below trigger. > Please help to solve this. > > "CREATE TRIGGER fkdc_MUSIC > AFTER DELETE ON MUSIC > FOR EACH ROW > BEGIN > SELECT CASE > WHEN > (SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = OLD.Artist_Id) > IS NOT NULL THEN 'DELETE FROM ARTIST WHERE ArtistId=OLD.Artist_Id' > END; > END;" > > > You need to use a conditional delete in your trigger. You can't do that using a select with a case statement. You could try something like this (untested): CREATE TRIGGER fkdc_MUSIC AFTER DELETE ON MUSIC FOR EACH ROW BEGIN delete from Artist where ArtistId = old.ArtistId and not exist (select id from music where ArtistId = old.ArtistId); delete from Album where AlbumId = old.AlbumId and not exist (select id from music where AlbumId = old.AlbumId); delete from BGM where BgmId = old.BgmId and not exist (select id from music where BgmId = old.BgmId); END;" A couple of other things to note: You should probably change your table definitions to key the phrase "INTEGER PRIMARY KEY" together so that sqlite can use its btree key optimization. Instead of this: CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text); use this: CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL, Album Text); Also, if you want to speed up the searches in the deletes trigger above, at the expense of slowing down all the insert and delete operations into the tables, you could add indexes on the individual Id columns in the music table. create index MusicArtistId on Muisc(ArtistId); create index MusicAlbumId on Muisc(AlbumId); create index MusicBgmId on Muisc(BgmId); HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -