Re: [sqlite] Rule of thumb for estimating index size
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
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
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
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
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
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
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
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
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?
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?
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
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
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
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
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 ?
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?