headers
RaghavendraK 70574 | 1 Jul 2007 05:23
Favicon

Re: Re: In Mem Query Performance

Hi All,

Pls find the sample test below,
A) Using Sqlite 3.3.17,SuSE9 version,8GB Ram,4 CPU machine,
1) Compile the generate the test data program and generate the data
2) Import the data to DB
3) Compile the perf Data program and then generate perf result.
4) Result for 100,000 records it takes 17 sec. Find one record at a time.

regards
ragha
 <at>  <at> 1) Program to generate the test data:

//Compile: g++ -g gen.cpp -o gen

#include <unistd.h>
#include <iostream>
#include <stdio.h>
#include <fstream>

using namespace std;

int main()
{
 fstream fout("data.sql",ios::out);
 fstream fout1("query.sql",ios::out);
  //Schema
  fout<<"create table test (serName text,doid text,primary key (serName,doid));"<<endl;

 for(int j=0; j<100000;j++)
(Continue reading)

Permalink | Reply | 12 comments |
headers
Joe Wilson | 1 Jul 2007 06:16
Picon
Favicon

Re: Re: In Mem Query Performance

Your keys are too large and have the same leading characters. 
Since the first hundred characters are the same you waste a lot 
of CPU time in comparisons.

Try to get your total key size down to a fraction of that size.

At least change your program to generate keys of this form 
instead and it will be much faster:

  key = ptr + key;

--- RaghavendraK 70574 <raghavendrak@...> wrote:
>  for(int j=0; j<100000;j++)
>  {
>     char* ptr = tmpnam(0); 
>      string key =
>
"111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111.";
>      key += ptr;
>      fout1<<key<<endl;
>     fout<<"insert into test values
>
('"<<key<<"',"<<"'22222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222');"<<endl;
> 
>  }

       
____________________________________________________________________________________
Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.
http://farechase.yahoo.com/
(Continue reading)

Permalink | Reply | 0 comments |
headers
Joe Wilson | 1 Jul 2007 06:47
Picon
Favicon

Re: Re: In Mem Query Performance

In addition,

- make a new index only on column1
- move the prepare before the loop to avoid reparsing the SELECT each time
- use sqlite3_bind_* and sqlite3_reset in the loop.
- move finalize after the loop.
- query the test table directly - not the temporary ttest table.
- don't SELECT * if you only need a column or two

--- RaghavendraK 70574 <raghavendrak@...> wrote:
> int main()
> {
>   sqlite3* db;
>   int ret = sqlite3_open("abc",&db);
> 
>   char* errmsg;
>   char buf[1024];
>   sprintf(buf,"create temporary table ttest(column1 text,column2 text,primary key
> (column1,column2));"); 
>   ret = sqlite3_exec(db , buf, NULL ,NULL,&errmsg);
> 
> 
>   sprintf(buf,"insert into ttest select * from test;");
>   ret = sqlite3_exec(db , buf, NULL ,NULL,&errmsg);
> 
> 
>   //read all the srvName from file and store it in arr and query.
>   fstream fin("query.sql",ios::in);
>   string data[100000];
>
(Continue reading)

Permalink | Reply | 9 comments |
headers
Damian Slee | 1 Jul 2007 13:09

sqlite testing with Boundschecker

Hi,

I have done some testing with 3.2 and now 3.4, visual studio2003 and
boundschecker (and XP).  I get a few reported errors when I simply do the
.dump command with no db.  Happens on other times with a db, this is easier.
Line numbers are from the downloaded 3.4.0 source.  I don’t really know how
the vdbe works, so not sure how to start on solving the write overrun one…

Thanks,

damian

Vdbeaux.c sqlite3VdbeChangeP3 line 534

Memory block at address void* _Src = 0x0012C92C in argument 2 in memcpy is
too small, should be 17, was 16.

if( pKeyInfo ){

      unsigned char *aSortOrder;

      memcpy(pKeyInfo, zP3, nByte);

      aSortOrder = pKeyInfo->aSortOrder;

Write Overrun: Memory write to 0x010D8A88 (2) overruns destination block
0x010D89F8 (60) allocated by HeapAlloc.

Vdbe.c, sqlite3VdbeExec, Line 701.  This occurs when running sqlite3.exe
with no db, then executing .dump, then second breakpoint on the pTos->flags
(Continue reading)

Permalink | Reply | 0 comments |
headers
Damian Slee | 1 Jul 2007 13:16

RE: sqlite testing with Boundschecker

Re-submitted cause line color in red didn’t  come thru.  [] around the
reported line

Subject: sqlite testing with Boundschecker

Hi,

I have done some testing with 3.2 and now 3.4, visual studio2003 and
boundschecker (and XP).  I get a few reported errors when I simply do the
.dump command with no db.  Happens on other times with a db, this is easier.
Line numbers are from the downloaded 3.4.0 source.  I don’t really know how
the vdbe works, so not sure how to start on solving the write overrun one…

Thanks,

damian

Vdbeaux.c sqlite3VdbeChangeP3 line 534

Memory block at address void* _Src = 0x0012C92C in argument 2 in memcpy is
too small, should be 17, was 16.

if( pKeyInfo ){

      unsigned char *aSortOrder;

      [memcpy(pKeyInfo, zP3, nByte);]

      aSortOrder = pKeyInfo->aSortOrder;
(Continue reading)

Permalink | Reply | 501 comments |
headers
Mov GP 0 | 1 Jul 2007 13:49
Picon

Semantic Database Design

Hi,
I'm searching for a database backend for a .NET project that involves a
semantic database backend. The problem is that I want to store any generic
kind of custom .NET datatype and not only SQL-primitives.

First, the Database should have a triple store for the semantic relations
that is built of three 32-bit integer columns. This is easy to implement.

   CREATE TABLE Triples
   (
      Subject INT,
      Predicate INT,
      Object INT
   )

But next to this I want to have some additional tables for storing literals,
data, and files. The Tables should consist of a row storing an integer that
gives the ID and an additional column that is storing the generic data. I
might use BLOBs for this issue, but its more efficient to use separate
tables. There should be a separate table for each datatype.

    <at> TableName = '_' + typeof(  <at> Datatype ).ToString()

   CREATE TABLE  <at> TableName
   (
      Object INT,
      Data  <at> Datatype
   )

The  <at> Datatype Parameter is a special object that implements something like a
(Continue reading)

Permalink | Reply | 4 comments |
headers
John Stanton | 1 Jul 2007 15:45
Favicon

Re: Re: In Mem Query Performance

It looks as if you would do better to run your job as one or more 
transactions and to use the Sqlite API, with the sequence:
           sqlite3_prepare
           begin transaction
             loop
               sqlite3_bind
               sqlite3_step
               sqlite3_reset
             end
           commit
           sqlite3_finalize
You may need to split your job into multiple transactions to manage 
their size.

RaghavendraK 70574 wrote:
> Hi All,
> 
> Pls find the sample test below,
> A) Using Sqlite 3.3.17,SuSE9 version,8GB Ram,4 CPU machine,
> 1) Compile the generate the test data program and generate the data
> 2) Import the data to DB
> 3) Compile the perf Data program and then generate perf result.
> 4) Result for 100,000 records it takes 17 sec. Find one record at a time.
> 
> regards
> ragha
>  <at>  <at> 1) Program to generate the test data:
> 
> //Compile: g++ -g gen.cpp -o gen
>
(Continue reading)

Permalink | Reply | 0 comments |
headers
Trevor Talbot | 1 Jul 2007 16:02
Picon

Re: Semantic Database Design

On 7/1/07, Mov GP 0 <movgp0@...> wrote:

> I'm searching for a database backend for a .NET project that involves a
> semantic database backend. The problem is that I want to store any generic
> kind of custom .NET datatype and not only SQL-primitives.

>    CREATE TABLE  <at> TableName
>    (
>       Object INT,
>       Data  <at> Datatype
>    )
>
> The  <at> Datatype Parameter is a special object that implements something like a
> "ISQLiteDatatype" Interface that provides information about the class that
> is needed for getting information about the used datatype. This includes:
>
>    -   how to serialize (use also ISerializeable)
>    -   how to sort (use also ICompareable)
>    -   is it possible to do byte ordering with the serialized data?
>    -   minimal and maximal Datasize and -lenght?
>    -   etc.
>
> SQL is not capable to handle this issue, but I think it should be possible
> using the API. At least in MS SQL Server it is possible, but you can't
> create new datatypes on the fly, but you have to upload and register the
> datatype's class to the server as DLL.

I'm not sure I understand what you're looking for here.  The SQL
interface (for any DBMS) is not going to act like a .NET object; that
behavior is contained within your application.  What exactly are you
(Continue reading)

Permalink | Reply | 3 comments |
headers
RaghavendraK 70574 | 1 Jul 2007 17:58
Favicon

Re: Re: In Mem Query Performance

Hi Joe/John

Appreciate your reply.
Result: with your suggestions it is to down to 3 secs.
But i need to get it to 1sec. Possible?

Here are the findings,
1) With Mem db default compile options and with suggestions(bind+ index) you have
provided to use bind performance stands at 10-11secs.
2) With File db default compile options it stands at 13-14secs.
3) begin and commit seems to have no affect on the query.
   Seperate index on mem table seems to have no affect.
4) Earlier Joe had suggetsed to make changes to page size,with that
it is down to 3 secs.[Along with your suggestions]
      Compile Option changes:
      ./configure --enable-threadsafe

      BCC = gcc -g -O2

      # C Compile and options for use in building executables that
      # will run on the target platform.  (BCC and TCC are usually the
      # same unless your are cross-compiling.)
      #
      TCC = gcc -g -O2 -I. -I${TOP}/src

      # Define -DNDEBUG to compile without debugging (i.e., for production usage)
      # Omitting the define will cause extra debugging code to be inserted and
      # includes extra comments when "EXPLAIN stmt" is used.
      #
      TCC += -DNDEBUG=1 -DSQLITE_DEFAULT_CACHE_SIZE=32768 -DSQLITE_DEFAULT_PAGE_SIZE=8192
(Continue reading)

Permalink | Reply | 8 comments |
headers
drh | 1 Jul 2007 20:10

Re: sqlite testing with Boundschecker

"Damian Slee" <damian <at> damianslee.com> wrote:
> 
> I have done some testing with 3.2 and now 3.4, visual studio2003 and
> boundschecker (and XP). 

Thank you for the report.

I have analyzed the errors reported by boundschecker and they
all appear to be false postives.  The SQLite 3.4.0 code base
is correct in all cases and boundchecker is complaining about
problems that do not exist.

The following is typical:

> 
> Dangling Pointer: Pointer 0x010D9250, allocated by HeapAlloc, has already
> been freed.
> 
> Vdbe.c sqlite3VdbeExec Line 469
> 
>   if( p->magic!=VDBE_MAGIC_RUN ) return SQLITE_MISUSE;
>   assert( db->magic==SQLITE_MAGIC_BUSY );
>   [pTos = p->pTos;]
>   if( p->rc==SQLITE_NOMEM ){
> 

There are two pointers on the offending line: "p" and "pTos".  The
p pointer appears to be valid.  Otherwise, the "return SQLITE_MISUSE"
two lines above would have be taken.  Or at worst, the dereference
of p two lines above should have triggered a similar error.  So
(Continue reading)

Permalink | Reply | 503 comments |

Gmane