[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Transaction?
From:       "Derrell Lipman" <derrell.lipman () unwireduniverse ! com>
Date:       2008-12-31 16:46:43
Message-ID: cdc91d420812310846y1a12bb70ya095b40d8b4b49a5 () mail ! gmail ! com
[Download RAW message or body]

On Wed, Dec 31, 2008 at 11:36 AM, John Hawley
<johnhawley@blueyonder.co.uk>wrote:

> the following block of code executes 2 or 3 orders of magnitude slower
> than 1 think it should ( the db was opened in earlier code)
>

Yup, you're committing each insert to disk (a VERY slow process) rather than
waiting until you've inserted all 10000 entries before committing to disk.
Insert code at the following marked places for dramatically increased
performance:

>
>        RecOffset=sizeof(rec);
>        fseek(infile,126,SEEK_SET);            // data starts at byte 126
>

      /* Begin a transaction */
      rc = sqlite3_exec(db, "BEGIN;", NULL, 0, &zErrMsg);
      if (rc != SQLITE_OK) { /* do error handling */ }

       while (fread(&rec,sizeof(rec),1,infile))
>        {
>            iSeq++;
>            GridY= (long) rec.lat*LATGRIDFACTOR + 0.5;
>            GridX= (long) rec.longt*LongGridFactor(rec.lat) + 0.5;
>            iGS =GridX*10000000 + GridY;
>            sprintf_s(SqlStr,200,"INSERT into soundings
> VALUES(%lf,%lf,%f,%s,%d,%ld);",rec.lat,
>                rec.longt,rec.depth,DateStr,iSeq,iGS);
>            rc= sqlite3_exec(db,SqlStr,NULL,0,&zErrMsg);
>            if(rc!=SQLITE_OK)
>            {
>                MessageBox(hwnd,zErrMsg,"SQL Error adding soundings
> data",MB_OK);
>

      ./* Rollback the transaction.  No need for error checking as journal
will recover next time anyway. */
      (void) sqlite3_exec(db, "ROLLBACK;", NULL, 0, NULL);

>                exit(1);
>            }
>
>        }
>

      /* Commit the transaction.  This will be your single slow operation
rather than 10000 of them */
      rc = sqlite3_exec(db, "COMMIT;", NULL, 0, &zErrMsg);
      if (rc != SQLITE_OK) { /* do error handling */ }

Derrell
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[prev in list] [next in list] [prev in thread] [next in thread]