Dan Kennedy, I discovered yesterday that one can use sqlite prepared SQLITE statements to reduce the CPU and memory utilization of parsing SELECT statements used to substitute for Sqlite3BTreeMovetoUnpacked and sqlite3_blob_reopen. Thank you
From: frank_chan...@hotmail.com To: sqlite-users@sqlite.org Subject: RE: Sqlite3BTreeMovetoUnpacked SQLite SELECT Statement Workaround is I/O Bound and uses all the Physical Memory Date: Wed, 29 Feb 2012 11:59:56 -0500 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