[sqlite] Performance test on windows

[Gaurav Arora]

Hi All,
I am a newbie to SQlite, just saw that the performance numbers on
www.sqlite.org are not reliable (as per the notce on website,
http://www.sqlite.org/speed.html 
)
So, I thought of profiling SQlite operations, on linux platform
the performance time is quite good.
I build sqlite on Windows too, and saw discouraging numbers coming out of
it, following are some of them.
e.g.
RH9:
inserting 1000 records ~.07 seconds.

WindowsXP:
inserting 100 records ~11 seconds.
inserting 1000 records ~113 seconds.

May be I did something wrong for building, or the parameters passed for
building sqlite werent correct.
I am willing to update the test results on the site, could anyone here guide
me for the things which I might be doing wrong.

Thanks in advance.
//Gaurav

RE: [sqlite] Problem using pragmas

[Saifuddin Rangwala]

Thanks a lot for the reply. You test program did work for me.

And apology, cause it was my mistake. There was a corruption in some
other unrelated place,
That was showing up in sqlite.

Thanks,
-Saif.



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 17, 2007 11:58 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problem using pragmas

"Saifuddin Rangwala" <[EMAIL PROTECTED]> wrote:
> Hi,
>  
> I am trying to configure sqlite-3.3.10 using pragma sql queries, 
> but it dumps core in sqlite spi.
>  
> What I do is
>  
> 1. sqlite3_open
> 2. sqlite3_exec("PRAGMA cach_size = 1000;"
>  
> 2 dumps core. While single stepping I found that sqlite3_prepare is 
> called twice inside sqlite3_exec and and the 2nd call results in SEGV.
>  

My test program shown below works just fine.  Perhaps you can provide
some additional details on how you are making this fail:

   #include 
   int main(int argc, char **argv){
 sqlite3 *db;
 unlink("test.db");
 unlink("test.db-journal");
 sqlite3_open("test.db", );
 sqlite3_exec(db, "PRAGMA cache_size=1000;", 0, 0, 0);
 sqlite3_exec(db, "CREATE TABLE t1(x);", 0, 0, 0);
 sqlite3_close(db);
   }

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: RE: [sqlite] DROP INDEX not freeing up memory

[Dave Gierok]

It does in fact look like the memory isn't being freed up entirely.  I am 
properly tracking xMalloc, xRealloc, and xFree.  I have a memory database and 
wrote some test code to loop a few times creating/dropping the same index.  The 
results of that are (numbers are total bytes allocated):

7632746 Before 1st Create Index
7637587 After 1st Create Index
7637434 After 1st Drop Index (and Vacuum -- the vacuum doesn't change memory 
allocated though)
7637587 After 2nd Create Index
7637434 After 2nd Drop Index (and Vacuum)
7637587 After 3rd Create Index
7637434 After 3rd Drop Index (and Vacuum)

Notice that the memory slightly decreases after the 1st Drop Index, but doesn't 
nearly drop to what it should (it should drop to 7632746 -- the same level as 
before the 1st Create Index).

Also notice that after the 1st create/drop, the memory allocated is the same 
after each create and after each drop.  So it implies there is not a leak -- 
but we can't get down to the original level before we created the first index.

So what's the big deal you might ask -- this example shows 5K that can't be 
reclaimed.  But in our game we create hundreds of indices that take up about 
2MB -- 2MB is quite a bit of memory in our world.

Any suggestions or explanations?

Thanks,
Dave

-Original Message-
From: Dave Gierok [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 17, 2007 9:06 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] DROP INDEX not freeing up memory

OK, thanks, I'll do some more digging and let you know.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 17, 2007 8:30 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DROP INDEX not freeing up memory

Dave Gierok <[EMAIL PROTECTED]> wrote:
> xTruncate is not being called because this is an in-memory database.  Memor=
> y databases should have memory freed instead I assume?
>

In-memory databases call sqliteFree() to release their memory.
I checked, and this does appear to work.  Perhaps the sqliteFree()
call is somehow not making it down into your measurement layer.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Problem using pragmas

[drh]

"Saifuddin Rangwala" <[EMAIL PROTECTED]> wrote:
> Hi,
>  
> I am trying to configure sqlite-3.3.10 using pragma sql queries, but
> it dumps core in sqlite spi.
>  
> What I do is 
>  
> 1. sqlite3_open
> 2. sqlite3_exec("PRAGMA cach_size = 1000;"
>  
> 2 dumps core. While single stepping I found that sqlite3_prepare is
> called twice inside sqlite3_exec and and the 2nd call results in SEGV.
>  

My test program shown below works just fine.  Perhaps you can
provide some additional details on how you are making this fail:

   #include 
   int main(int argc, char **argv){
 sqlite3 *db;
 unlink("test.db");
 unlink("test.db-journal");
 sqlite3_open("test.db", );
 sqlite3_exec(db, "PRAGMA cache_size=1000;", 0, 0, 0);
 sqlite3_exec(db, "CREATE TABLE t1(x);", 0, 0, 0);
 sqlite3_close(db);
   }

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Problem using pragmas

[Saifuddin Rangwala]

Hi,
 
I am trying to configure sqlite-3.3.10 using pragma sql queries, but
it dumps core in sqlite spi.
 
What I do is 
 
1. sqlite3_open
2. sqlite3_exec("PRAGMA cach_size = 1000;"
 
2 dumps core. While single stepping I found that sqlite3_prepare is
called twice inside sqlite3_exec and and the 2nd call results in SEGV.
 
Thanks a lot for your help.
 
Thanks,
-Saif.

Re: [sqlite] DROP INDEX not freeing up memory

[John Stanton]

I haven't looked at the Sqlite code in detail, but in general it is hard 
to return memory so memory usage hits a high water mark.  You can decide 
if that is the case just by looking at the Sqlite source and 
establishing how the memory allocation in your OS and compiler support 
package works.


What you don't want is to have memory leaks where areas of memory are 
allocated and then lost so that memory usage keeps increasing until a 
catastrophic failure occurs.


Vacuum will certainly free deleted space in the database and make its 
disk footprint smaller.  You would have to look at the source to see if 
if it returns cache space used by yuor in memory DB.


Dave Gierok wrote:

But the docs say VACUUM is supposed to free that memory which it doesn't look 
like it is doing.

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 16, 2007 3:41 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DROP INDEX not freeing up memory

Sqlite is allocating cache memory and reusing it.  That is not a bug, it
is a beneficial feature.

Dave Gierok wrote:


Yes, I did try that -- I should have mentioned it.  When I create and drop the 
index again, the amount of memory that Sqlite takes is effectively the same.  
That is, to create the index once costs a lot of memory, and then future 
deletions/creations does not change the amount of memory allocated.

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 16, 2007 1:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DROP INDEX not freeing up memory

Dave Gierok wrote:



(I'm resending this, as I have not seen it posted the first time I sent - 
apologies if it gets posted twice)

I am running an in-memory db and am using the functionality where we override 
all the OS calls such that we implement our own memory management and file IO.  
Therefore, I am able to easily track the amount of memory Sqlite is taking up.  
I have noticed that when I call CREATE INDEX and then the corresponding DROP 
INDEX, I do not see the memory drop to the same level as it was before the 
CREATE INDEX.  I also call VACUUM after DROP INDEX, which makes no difference.

Is this expected, or does it sound like a bug in Sqlite?

Thanks,
Dave




What happens if you create and drop the index again?  Do that to see if
you are just seeing an effect of assigning cache on the first operation.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-





-
To unsubscribe, send email to [EMAIL PROTECTED]
-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] DROP INDEX not freeing up memory

[Dave Gierok]

OK, thanks, I'll do some more digging and let you know.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 17, 2007 8:30 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DROP INDEX not freeing up memory

Dave Gierok <[EMAIL PROTECTED]> wrote:
> xTruncate is not being called because this is an in-memory database.  Memor=
> y databases should have memory freed instead I assume?
>

In-memory databases call sqliteFree() to release their memory.
I checked, and this does appear to work.  Perhaps the sqliteFree()
call is somehow not making it down into your measurement layer.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Correct use of sqlite3 API (release locks)

[drh]

Brodie Thiesfield <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> I have multiple processes using a single database for both read and
> write. I want to ensure that my interpretation of the v3 API spec is
> correct. In particular, I want to ensure that all processes lock the
> database for the minimum time possible and release the lock as soon as
> they have finished processing the SQL statement. Are locks released
> automatically on error/done, or manually by the sqlite3_reset() call, or
> some other function?
> 

You should call sqlite3_reset() (or sqlite3_finalize()) to make sure
locks are released.  They might have been released prior to that point,
but not always.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] DROP INDEX not freeing up memory

[drh]

Dave Gierok <[EMAIL PROTECTED]> wrote:
> xTruncate is not being called because this is an in-memory database.  Memor=
> y databases should have memory freed instead I assume?
> 

In-memory databases call sqliteFree() to release their memory.
I checked, and this does appear to work.  Perhaps the sqliteFree()
call is somehow not making it down into your measurement layer.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Correct use of sqlite3 API (release locks)

[Brodie Thiesfield]

Hi,

I have multiple processes using a single database for both read and
write. I want to ensure that my interpretation of the v3 API spec is
correct. In particular, I want to ensure that all processes lock the
database for the minimum time possible and release the lock as soon as
they have finished processing the SQL statement. Are locks released
automatically on error/done, or manually by the sqlite3_reset() call, or
some other function?

I currently use the following logic in my internal DB layer.

  if sql not prepared
 sqlite3_prepare_v2()

  sqlite3_bind_* ...

  rc = sqlite3_step()
  while rc == SQLITE_ROW
 process row (sqlite3_column_*) ...
 rc = sqlite3_step()

  if rc != SQLITE_DONE
 process error

  sqlite3_reset()

Will this release the locks on the database (assuming no transactions?)
by the end of the function?

Regards,
Brodie


-
To unsubscribe, send email to [EMAIL PROTECTED]
-