Re: [sqlite] Rule of thumb for estimating index size

2006-04-21 Thread Christian Smith
On Fri, 21 Apr 2006, Rusty Conover wrote:

Hi,

Is there an easy way to get an estimate on the space used by an
existing index?  If not what is a good guess on how to estimate the
size?

My guess would be (assuming text fields are being indexed):

[total length of all index keys] + [number of rows]*8 (for the offset
location)

Is that close? I realize disregards all space used by page allocation
overhead.


The page overhead will be pretty constant per index entry.

One underestimate from above is the effect of overflow pages and internal
fragmentation. If the key doesn't fit in the btree node, overflow pages
are used to store the rest of the key, and the pages are used in their
entirety, and not shared with other entries. Thus, if your keys are quite
long, the internal fragmentation must be taken into account in the total
key length. If 1/4 of your rows cause overflow, the the extra overhead can
be approximated as:

 pagesize/2 * [num of rows]/4

This assumes that the last overflow page is on average half full.



Thanks,

Rusty
--
Rusty Conover
InfoGears Inc.





-- 
/\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \


Re: [sqlite] Getting free pages count

2006-04-21 Thread Dennis Jenkins
Alexey Belyaev wrote:
 Hi!

 In current versions sqlite (3.2.7 and later) not enought functions, 
 for receive information abut free pages count (or used pages count).
 I have to use not official methods :(
 May I expect that this functions will be introduced in next versions sqlite?

   

I too would like an officially supported method for getting the free
page count.  I hacked up the following based on James P. Lyon's prior
sqlite 2.xx work:  http://unwg.no-ip.com/freepages.c


Re: [sqlite] Rule of thumb for estimating index size

2006-04-21 Thread John Newby
Hi Christian, thanks for the explanation, when you explain it like that it's
so simple, the index in the book concept didnt even come to me. lol.

John.

On 21/04/06, Christian Smith [EMAIL PROTECTED] wrote:

 On Fri, 21 Apr 2006, Rusty Conover wrote:

 Hi,
 
 Is there an easy way to get an estimate on the space used by an
 existing index?  If not what is a good guess on how to estimate the
 size?
 
 My guess would be (assuming text fields are being indexed):
 
 [total length of all index keys] + [number of rows]*8 (for the offset
 location)
 
 Is that close? I realize disregards all space used by page allocation
 overhead.


 The page overhead will be pretty constant per index entry.

 One underestimate from above is the effect of overflow pages and internal
 fragmentation. If the key doesn't fit in the btree node, overflow pages
 are used to store the rest of the key, and the pages are used in their
 entirety, and not shared with other entries. Thus, if your keys are quite
 long, the internal fragmentation must be taken into account in the total
 key length. If 1/4 of your rows cause overflow, the the extra overhead can
 be approximated as:

 pagesize/2 * [num of rows]/4

 This assumes that the last overflow page is on average half full.


 
 Thanks,
 
 Rusty
 --
 Rusty Conover
 InfoGears Inc.
 
 
 
 

 --
 /\
 \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
  X   - AGAINST MS ATTACHMENTS
 / \



Re: [sqlite] Getting free pages count

2006-04-21 Thread Alexey Belyaev

I mean freepages.c under not official methods :)

--
View this message in context: 
http://www.nabble.com/Getting-free-pages-count-t1485769.html#a4025820
Sent from the SQLite forum at Nabble.com.



[sqlite] Use sqlite on solaris with java in multithreaded web application

2006-04-21 Thread Nitin Mahajan
Hi,

I want to use sqlite as database in Solaris Environment in Java based web
application. There can be multiple read only requests coming to database at
same time. I tried using javasqlite driver for using sqlite with Java but as
number of users or request increase response time increase drastically and
sometimes jsp page doesn't even render in browser.

Is it advisable to use sqlite in solaris with javasqlite driver in
multithreaded environment? OR there is any other better driver. I even tried
using 3.3.5 version of sqlite.

Any help will be appreciated.

Regards,
Nitin Mahajan


Re: [sqlite] Use sqlite on solaris with java in multithreaded web application

2006-04-21 Thread Jay Sprenkle
 Hi,

 I want to use sqlite as database in Solaris Environment in Java based web
 application. There can be multiple read only requests coming to database at
 same time. I tried using javasqlite driver for using sqlite with Java but as
 number of users or request increase response time increase drastically and
 sometimes jsp page doesn't even render in browser.

 Is it advisable to use sqlite in solaris with javasqlite driver in
 multithreaded environment? OR there is any other better driver. I even tried
 using 3.3.5 version of sqlite.

I would look at the indexes on your database.
Which queries are slowing response time?
You might share the query and schema with us so we can help you optimize them.


RE: [sqlite] Use sqlite on solaris with java in multithreaded web application

2006-04-21 Thread Nitin Mahajan
Hi Jay,

Thanks for your immediate reply.

I will send you schema and query for which I feel there is problem. 
On the other hand the driver I used for connection between java and sqlite
(javasqlite- http://www.ch-werner.de/javasqlite/) states that this driver is
tested only in Linux. I was wondering even if I should use SQLite with java
on solaris in live applications.

Regards,
Nitin

-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
Sent: Friday, April 21, 2006 6:59 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Use sqlite on solaris with java in multithreaded web
application

 Hi,

 I want to use sqlite as database in Solaris Environment in Java based web
 application. There can be multiple read only requests coming to database
at
 same time. I tried using javasqlite driver for using sqlite with Java but
as
 number of users or request increase response time increase drastically and
 sometimes jsp page doesn't even render in browser.

 Is it advisable to use sqlite in solaris with javasqlite driver in
 multithreaded environment? OR there is any other better driver. I even
tried
 using 3.3.5 version of sqlite.

I would look at the indexes on your database.
Which queries are slowing response time?
You might share the query and schema with us so we can help you optimize
them.


Re: [sqlite] Use sqlite on solaris with java in multithreaded web application

2006-04-21 Thread Jay Sprenkle
 I will send you schema and query for which I feel there is problem.
 On the other hand the driver I used for connection between java and sqlite
 (javasqlite- http://www.ch-werner.de/javasqlite/) states that this driver is
 tested only in Linux. I was wondering even if I should use SQLite with java
 on solaris in live applications.

I don't know about the java/sun interface to sqlite. I have used it on linux and
windows and it's worked very well for me. Others on this list might know more.


Re: [sqlite] Re: SQL statements in progress error message

2006-04-21 Thread Nicholas Bastin
On 4/20/06, Igor Tandetnik [EMAIL PROTECTED] wrote:
 Nicholas Bastin [EMAIL PROTECTED]
 wrote:
  When trying to commit a transaction, I'm getting the following error
  message from sqlite3_exec():
 
  cannot commit transaction - SQL statements in progress
 
  Is there any way to find out what statements are in progress?  Also,
  what could cause this?  I can guarantee that sqlite3_step has returned
  SQLITE_DONE on all my statements before I get to this point.  Is that
  not sufficient?

 No, it is not sufficient. You have to sqlite3_reset or sqlite3_finalize
 all statements in progress before you can commit.

Ok, thanks.  I'll make sure to reset all statements before trying to
commit the transaction.  (Is this mentioned somewhere in the
documentation?)

--
Nick


[sqlite] sqlite3_prepare with non-scalar variables?

2006-04-21 Thread Nicholas Bastin
Is there any way to effectively prepare the following statement:

SELECT * FROM some_table WHERE id IN (?);

where the contents of the variable are a vector? (1,3,5,9,34,57), etc.

I've been building up these just using text and using exec, but it
seems you'd want to have a sqlite3_bind_int64_vector or something
similaram I missing that this is implemented somewhere?

--
Nick


[sqlite] Re: sqlite3_prepare with non-scalar variables?

2006-04-21 Thread Igor Tandetnik

Nicholas Bastin [EMAIL PROTECTED]
wrote:

Is there any way to effectively prepare the following statement:

SELECT * FROM some_table WHERE id IN (?);

where the contents of the variable are a vector? (1,3,5,9,34,57), etc.

I've been building up these just using text and using exec, but it
seems you'd want to have a sqlite3_bind_int64_vector or something
similaram I missing that this is implemented somewhere?


As far as I can tell, you can't directly do what you want. The closest 
you can get to it is as follows: create a temporary table


   create temp table list (id integer);

Insert your values into it:

   insert into temp.list(id) values(?);

Finally, reorganize your select statement to use this table:

   select * from some_table join temp.list on (some_table.id = 
temp.list.id);


All statements used here can be prepared.

Igor Tandetnik 



[sqlite] Reuse of TCL array causes memory leak

2006-04-21 Thread Aldobino Braga
I've run into an issue where the reuse of a TCL array (to hold query
results) causes a catastrophic memory leak. The eval command is called
with the same array name again and again.  Each successive query / array
population exponentially consumes more and more memory (until the system
crashes).

Since eval is responsible for the array creation, it seems to me that it
should be responsible for calling array unset before it populates the
data.  When I call array unset myself, everything works as expected. 

Perhaps this is the expected behavior, however I was unable to find anything
in the documentation regarding it.
Would anyone care to comment?

Regards,
Al Braga




Re: [sqlite] Getting free pages count

2006-04-21 Thread Dennis Jenkins
Alexey Belyaev wrote:
 I mean freepages.c under not official methods :)

   

I know. :)  I'm hoping that someday it will be officially supported.  If
Dr. Hipp feels that it adds unnecessary bloat to the library, then maybe
it can be supported but IFDEFd out by default, and those of us that
want it can set the define and add it back in.

I just feel that my code was a horrible hack.  I had to copy many
private structural definitions from other parts of the source code to
make it work.



Re: [sqlite] Rule of thumb for estimating index size

2006-04-21 Thread drh
Rusty Conover [EMAIL PROTECTED] wrote:
 Hi,
 
 Is there an easy way to get an estimate on the space used by an  
 existing index?  If not what is a good guess on how to estimate the  
 size?
 
 My guess would be (assuming text fields are being indexed):
 
 [total length of all index keys] + [number of rows]*8 (for the offset  
 location)
 
 Is that close? I realize disregards all space used by page allocation  
 overhead.
 

The sqlite3_analyzer executable available on the website will tell
you exactly how much space an index uses.  This utility is not
suitable for use at run-time, but you can use it to validate the
accuracy of your estimate.
--
D. Richard Hipp   [EMAIL PROTECTED]



Re: [sqlite] Reuse of TCL array causes memory leak

2006-04-21 Thread drh
Aldobino Braga [EMAIL PROTECTED] wrote:
 I've run into an issue where the reuse of a TCL array (to hold query
 results) causes a catastrophic memory leak. The eval command is called
 with the same array name again and again.  Each successive query / array
 population exponentially consumes more and more memory (until the system
 crashes).
 
 Since eval is responsible for the array creation, it seems to me that it
 should be responsible for calling array unset before it populates the
 data.  When I call array unset myself, everything works as expected. 
 
 Perhaps this is the expected behavior, however I was unable to find anything
 in the documentation regarding it.
 Would anyone care to comment?
 

A common idiom is to do this:

db eval {SELECT * FROM table} v break

After this statement runs, the array variable v contains
the content of the first row of the table.  Sometimes you
see two or three SELECT statements in a row populating the
same array:

db eval {SELECT * FROM table1} v break
db eval {SELECT * FROM table2} v break
...
db eval {SELECT * FROM tableN} v break

All of the about (which is commonly used) would break if
SQLite tried to do unsets for you.

I think, therefore, that the current behavior is correct.
--
D. Richard Hipp   [EMAIL PROTECTED]



[sqlite] Sqlite inside MySQL as one of the pluggable storage mechanisms ?

2006-04-21 Thread Russell Leighton


I had a musing while reading:

   http://news.com.com/2100-7344_3-6063599.html?part=rsstag=6063599subj=news


where it reminded me of one of MySQL's features:

   MySQL's database is built so that it can use a range of different
   storage mechanisms, tuned for different purposes, such as
   transactions or indexing large amounts of text.

   Rather than have one perfect engine, it's better to have a
   pluggable architecture, Urlocker said. The idea is you can mix and
   match within a single application because data will be used in
   different ways.


Perhaps adding the appropriate bindings to MySQL to register sqlite as a 
storage mechanism would
allow the use of Sqlite in a network environment with out bloating the 
core Sqlite code?


Maybe the above makes no sense, but I find the notion appealing that 
Sqlite could remain as tight little sql engine but could use the 
network server

of MySQL should ever such a thing be needed.

Would that be possible?