Re: [sqlite] Sqlite3BTreeMovetoUnpacked SQLite SELECT Statement Workaround is I/O Bound and uses all the Physical Memory

[Frank Chang]

Daniel Kennedy, I tried your SQLite SELECT Statement workaround(the profiler 
output is attached) but the profiler is full of sqlite functions and the 
application runs slower because it is I/O bound and uses almost all the 
physical memory. 
I was thinking maybe we could write only one SQLITE SELECT statement and cache 
the blobs in memory
Could you please suggest how I could fix this I/O Bound SQLIte SELECT Statement 
Problem.
void cIntersectingDedupe::GetSubGraphBlob(sSUBGRAPHINFO *SubGraph_,/*unsigned 
long*/ int*& SubGraphBlob_,
  int *Size_) {
 int Size;

  //    sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);

 // Grab a BLOB and put it into a unsigned long buffer. As the BLOB contains 
record numbers,
 //   we will never see a value of zero. Thus, we use 0 to mark the end of the 
array.
 //
 // Note that we can have BLOBs of size 0, though. If we used DistillSubGraph to
 //   dedupe the subgraph, some records will be consolidated into others. The
 //   donor record's BLOB gets zapped because all of it's BLOB was rolled into 
the
 //   donee (All your BLOB are belong to us!)
 
 // First time, open the BLOB for real, else we can re-open (faster):
   char SelectStatement[256];
   char WhereClause[256];
         strcpy(SelectStatement, "select [Rows] from AggregatedData");
   sprintf(WhereClause," where [RowId] = %d",SubGraph_->IteratorPos+1);
   strcat(SelectStatement, WhereClause);
   int ReturnValue=sqlite3_prepare(SubGraph_->Database,
    SelectStatement,-1,
    &SubGraph_->Statement);
 
   int status =  sqlite3_step(SubGraph_->Statement);
   if (status == SQLITE_ROW) {
  
    SubGraphBlob_ =  (int*)sqlite3_column_blob(SubGraph_->Statement,0); //2);
    Size = (sqlite3_column_bytes(SubGraph_->Statement,0)); //2);
// if (SubGraph_->hBlob==0)
//  
sqlite3_blob_open(SubGraph_->Database,"main","AggregatedData","Rows",SubGraph_->IteratorPos+1,0,&SubGraph_->hBlob);
// else
//  sqlite3_blob_reopen(SubGraph_->hBlob,SubGraph_->IteratorPos+1);
//
// Size=sqlite3_blob_bytes(SubGraph_->hBlob)/sizeof(unsigned long);
// sqlite3_blob_read(SubGraph_->hBlob,SubGraphBlob_,Size*sizeof(unsigned 
long),0);
// SubGraphBlob_[Size]=0;
 if (Size_!=0)
  *Size_=Size;
 }
}
void cIntersectingDedupe::IntersectBlobs(sSUBGRAPHINFO *SubGraph_,int 
*IntersectionBlob_,
  /*unsigned long*/int *SubGraphBlob_) {
 int Pos1,Pos2,PosOut;
 GetSubGraphBlob(SubGraph_,SubGraphBlob_);
 // Perform the intersection. We walk though the two blobs, if the blobs 
contain the same
 //   value, that value is copied to PosOut in Blob_, else, the blob that is 
'behind' is
 //   incremented so it can 'catch up' to the other:
 Pos1=Pos2=PosOut=0;
 while (IntersectionBlob_[Pos1]!=0 && SubGraphBlob_[Pos2]!=0) {
  if (IntersectionBlob_[Pos1]==SubGraphBlob_[Pos2]) {
   IntersectionBlob_[PosOut++]=IntersectionBlob_[Pos1++];
   Pos2++;
  } else if (IntersectionBlob_[Pos1]<SubGraphBlob_[Pos2]) {
   Pos1++;
  } else {
   Pos2++;
  }
 }
 IntersectionBlob_[PosOut]=0;
}

Thank you for your help.


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