Hi,
Say,I am having 40000 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
40000 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,&pst_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]
-----------------------------------------------------------------------------
