Re: [sqlite] Performance of bulk insertion

2008-03-24 Thread Griggs, Donald
Hi, Mahalakshmi,
 
Regarding: "..Am I doing right or it's a lengthy process.If so suggest
some other way for inserting the records?..."

I must confess I have not spent much time looking at the details of your
email, but you will want to be sure to use a single transaction to
insert many rows in your table if performance is an issue:

http://www.sqlite.org/lang_transaction.html

You'll likely want to use a transaction for at least 100 rows at a time
-- or perhaps even for all 4.

After completing the insert, you may find you wish to use the CREATE
INDEX command to speed up queries.







This email and any attachments have been scanned for known viruses using 
multiple scanners. We believe that this email and any attachments are virus 
free, however the recipient must take full responsibility for virus checking. 
This email message is intended for the named recipient only. It may be 
privileged and/or confidential. If you are not the named recipient of this 
email please notify us immediately and do not copy it or use it for any 
purpose, nor disclose its contents to any other person.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Performance of bulk insertion

2008-03-24 Thread Mahalakshmi.m
Hi,

I am working in 3.3.6

The table schema of my code is as follows.



"CREATE TABLE ALBUMARTIST(

AlbumArtistId INTEGER PRIMARY KEY NOT NULL,

AlbumArtistName TEXT NOT NULL COLLATE
NOCASE,UNIQUE(AlbumArtistName));"

 

"CREATE TABLE ARTIST(

ArtistId INTEGER PRIMARY KEY NOT NULL,

ArtistName TEXT NOT NULL COLLATE NOCASE ,

   UNIQUE(ArtistName));"


"CREATE TABLE ALBUM(

AlbumId INTEGER PRIMARY KEY NOT NULL,

AlbumName TEXT NOT NULL COLLATE NOCASE ,

   UNIQUE(AlbumName));"




 "CREATE TABLE MUSIC(

Id INTEGER PRIMARY KEY NOT NULL,

Track TEXT NOT NULL,

URL TEXT NOT NULL,

Album_Id INTEGER,

Artist_Id INTEGER,

AlbumArtist_Id INTEGER);"


 "CREATE TABLE PLAYLIST (

PlayListId INTEGER PRIMARY KEY NOT NULL,

PlayListTrack TEXT,

PlayListUrl TEXT);"

 

"CREATE TRIGGER fkdc_MUSIC AFTER DELETE ON MUSIC FOR EACH ROW  

BEGIN

DELETE FROM ALBUM WHERE AlbumId = old.Album_Id and not exists
(SELECT Id FROM MUSIC WHERE Album_Id = old.Album_Id); 

DELETE FROM ARTIST WHERE ArtistId = old.Artist_Id and not exists
(SELECT Id FROM MUSIC WHERE Artist_Id = old.Artist_Id);

 DELETE FROM ALBUMARTIST WHERE AlbumArtistId = old.AlbumArtist_Id
and not exists (SELECT Id FROM MUSIC WHERE AlbumArtist_Id =
old.AlbumArtist_Id);

 DELETE FROM TRACKLIST WHERE PlayListUrl = old.URL and not exists
(SELECT Id FROM MUSIC WHERE URL = old.URL);

END;"

 

 My database has 4 records.

My need is to add all the Track  in the Table MUSIC to the Table
TRACKLIST.For that i have used the following code.

 

 while(u32_PlayListRecordCount < TotalRecordCount)
 {

   if ( sqlite3_prepare(pst_SqliteCallback->db," SELECT Track, URL FROM
MUSIC LIMIT 100 OFFSET ? ; ",-1,&pst_PlayListPrepareStmt,0)!= SQLITE_OK) 
   {
return SQLITE_DB_ERROR;
   }
   else
   {
sqlite3_bind_int(pst_PlayListPrepareStmt,1, u32_OffsetValue);

 

Now I will step and store all the 100 results in one Buffer.

   }
  
  if(e_ReturnStatus == SQLITE_DB_SUCCESS)
  {
   if ( sqlite3_prepare(pst_SqliteCallback->db,"INSERT INTO TRACKLIST
(PlayListTypeId,PlayListTrack,PlayListUrl) VALUES
(?,?,?);",-1,&pst_PlayListPrepareStmt,0)!= SQLITE_OK) 
   {
return SQLITE_DB_ERROR;
   }

 

   else
   {
for(u32_Offset = 0;u32_Offset < gu32_PlayListindex;u32_Offset++)

Here gu32_PlayListindex  is the count of  "SELECT count(*) FROM MUSIC LIMIT
100 OFFSET ? ;"


{
 sqlite3_bind_int(pst_PlayListPrepareStmt,1,PlayListIndex);
 
sqlite3_bind_text(pst_PlayListPrepareStmt,2,st_PlayList[u32_Offset].PlayList
TrackBuffer,-1,SQLITE_STATIC);
 
sqlite3_bind_text(pst_PlayListPrepareStmt,3,st_PlayList[u32_Offset].PlayList
UrlBuffer,-1,SQLITE_STATIC);
u32_Return = sqlite3_step(pst_PlayListPrepareStmt);
sqlite3_reset(pst_PlayListPrepareStmt);
 }
u32_Return = sqlite3_finalize(pst_PlayListPrepareStmt);
u32_ PlayListRecordCount = u32_ PlayListRecordCount +
gu32_PlayListindex;
   }
  }
  else
  {
   return SQLITE_DB_ERROR;
  }
 }

Am I doing right or it's a lengthy process.If so suggest some other way for
inserting the records.
 

But  this takes very long time to insert all the Tracks into
playlist.Totally I need to insert 4 records.

 

Is there anyother way to insert huge number of data in to another table..

I want to insert  each record after getting the Track from MUSIC select
statement.Can we use Triggers for solving this.

Can anyone help to solve this .

 

Thanks & Regards,

Mahalakshmi.M


 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users