[sqlite] Efficient record insertion techniques?

2005-07-19 Thread R S
Hi, I wrote a Real Time logging app that insert logs from various Unix machines in our lab into a sqlite Database. The insertions are not batched as of now. Maybe that itself is an optimization. 1) Should I use a transaction even for a single record? 2) I open the DB, insert the record and close

Re: [sqlite] Efficient record insertion techniques?

2005-07-19 Thread R S
ot locked the partition usage was just 6%! > > > Donald Griggs > > Opinions are not necessarily those of Misys Healthcare Systems nor its board > of directors. > > > > -Original Message- > From: R S [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 19, 2005 2:23 PM > To: sqlite-users@sqlite.org >

Re: [sqlite] Efficient record insertion techniques?

2005-07-20 Thread R S
! Maybe I constructed my query wrong It looks something like this: select fields from table order by id desc limit 50; Any ideas??? On 7/20/05, R S <[EMAIL PROTECTED]> wrote: > Actually it is an ext3...and I guess the File Size limitation on ext3 is 4TB. > > > On 7

Re: [sqlite] Efficient record insertion techniques?

2005-07-20 Thread R S
Ok that improved my response time. Now, what if have queries which dont query the id field at all? Will the response then be sluggish? On 7/20/05, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > On Wed, 2005-07-20 at 11:15 -0700, R S wrote: > > Actually I deduced the file siz

[sqlite] Basic qns about TEMP table

2005-07-22 Thread R S
1) When I want to move/insert records from a TEMP table into my regular table, can I do this within a transaction? 2) Suppose my Table/(and Temp Table) have a schema like CREATE [TEMP] TABLE My[Temp]Test ( id primary key not null, value int); and I use insert into MyTest(null,

[sqlite] No Timeout during commit?

2005-07-25 Thread R S
Hi, I have 2 process accessing the DB, one reading and the other writing. Often the process reading the DB could take long and could block the other process from committing a bunch of records to the DB. I noticed that when the reader process has the lock and the writer process tries to commit a

Re: [sqlite] Long retrieval times

2005-08-02 Thread R S
Thanks everyone for your response. Dennis, that works great! Jay, I think I presume you meant (x >= 'a'). This works great too! Thank you again! On 8/2/05, Dennis Cote <[EMAIL PROTECTED]> wrote: > Thomas Briggs wrote: > > > > > > > > > > >>However if I use something like: > >>select * from

[sqlite] Qn on Transaction across 2 DBs

2005-08-03 Thread R S
Hi, I have a requirement where I need to update 2 tables in 2 separate DBs within a transaction. Do I need to attach the second table to the first at the start of the transaction? As I understand SQLite commits a transaction across a single DB only? Thanks!

[sqlite] Limitation on no. of Indices in a table?

2005-08-04 Thread R S
Hi, I have a table with a large # of indices (almost as the # of columns) in the table. SQLite works beautifully with most constraints and magically takes a long time for a query with constraints on the column with its index last created. Any limitations on the max # of indices allowed for a

[sqlite] Re: Limitation on no. of Indices in a table?

2005-08-04 Thread R S
Also the column is a tinyint..Guess that shouldnt matter..But just FYI... On 8/4/05, R S <[EMAIL PROTECTED]> wrote: > Hi, > I have a table with a large # of indices (almost as the # of > columns) in the table. SQLite works beautifully with most constraints > and magically

[sqlite] How do I view the execution plan of a SQL query?

2005-08-13 Thread R S
Thanks!

[sqlite] Lock type during an update?

2005-08-24 Thread R S
I presume it is a PENDING lock? Does SQLite acquire a PENDING lock as soon it begins an Update operation?

[sqlite] What is this extraneous table?

2005-09-05 Thread R S
Hi, I have a DB containing a table called MyTable which is about 3Gb in size currently. When my process is writing to it, it seems to create a table something likeMyTable-vmhyimo41pq82sw63ip0 with an associated journal table.I seem to have quite a few of these tables.I cannot use the Command

[sqlite] SQLite where clause parsing?

2005-09-06 Thread R S
Hi, I have a million records in my DB. I tried using .explain on and ran the query below which took a long time although I just want the last 100,000 records...(It ran much faster when my table had a 100,000 records) so I assume it is related to how I constructed the statement. select columns

[sqlite] rowId continuity?

2005-09-14 Thread R S
Hi, I have a screen which displays rows of my table in a paged form (i.e. one can do Back/Previous on them and navigate across pages). I use the rowId of the top and bottom rows to query for the next/previous set of rows in the page. However I just read somewhere that the rowId need not be

Re: [sqlite] Re: rowId continuity?

2005-09-14 Thread R S
> I don't quite see why you need rowId to be contiguous to implement your > paging. Can't you do something like this: > > select * from mytable > where rowId > :lastRowId > order by rowId > limit 100 > > OK I do it this way, but my concern was are these rows refilled later? Also what happens if

[sqlite] Qn on moving data from Temp Table to Main Table

2005-10-08 Thread R S
Hi, The Temp Table (obviously) mimics the Main Table. The Main Table has a column, recordNo which is a integer primary key (which autoincrements). Now I periodically move Data from the Temp into the Main Table (and then delete records from the Temp Table). It succeeds the first time, but fails

Re: [sqlite] Qn on moving data from Temp Table to Main Table

2005-10-09 Thread R S
means you'll have to change > "select * from temp" in your insert stmt to a select that explicitly names > each column (insert into main select null, col1, col2... from temp;) > > -Clark > > - Original Message > From: R S <[EMAIL PROTECTED]> > To: sql

[sqlite] "or" in SQL Query converted to "IN"

2005-10-20 Thread R S
by default? I am using 3.2.2 I ran across this doc http://www.sqlite.org/optoverview.html

[sqlite] Receive error: database is full

2005-10-23 Thread R S
with Return Value 13. I checked my partition space and its usage is just 2% (Platform is Linux using SQLite 3.2.2). Also I am using Temp tables and periodically move data into my Main Table. I wondered if my Temp Table is full because strace on my process gave me messages like:

[sqlite] Is this query optimized?

2005-10-25 Thread R S
Hi, I am trying to use this query and notice that the execution time increasing linearly as the Table size increases. select totalCount from (select count(*) as totalCount from myTable where (insertionTime BETWEEN and ) and columnVal > '0' group by columnVal) where totalCount > 10; Diff between

Re: [sqlite] Is this query optimized?

2005-10-25 Thread R S
0' > group by columnVal > having totalCount > 10 > > If you also create an index on insertionTime, your query should be fast. > > Martin > > > R S schrieb: > > >Hi, > >I am trying to use this query and notice that the execution time > increasing > >l

[sqlite] Temp table in which dir?

2005-10-25 Thread R S
Hi, I programatically create a Temp table for my DB. Does the Temp table reside in the same directory as my Database or in another directory (/var/tmp I suppose). Thanks?

Re: [sqlite] Very Slow delete times on larger databases, please help!

2005-10-26 Thread R S
In my case Delete happens reasonably OK but Vaccuuming takes incredibly long? On 10/21/05, Allan, Mark <[EMAIL PROTECTED]> wrote: > > > Thanks to both Christian Smith and John Stanton for your posts. > > > On Wed, 19 Oct 2005, Christian Smith wrote: > > From the VDBE output you originally

Re: [sqlite] Size of INSERT and UPDATE in TRANSACTION

2005-10-27 Thread R S
It would be the other way around, no? The larger the no of inserts within a Transaction, the better the performance. On 10/27/05, Hannes Ricklefs <[EMAIL PROTECTED]> wrote: > > Hello, > > I was wondering if anyone has any experience with the number of INSERT > UPDATE > statements in one

Re: [sqlite] sqlite 2.0 database

2005-11-07 Thread R S
Are you trying to open a file by the nomenclature "-journal" file? On 11/7/05, Manuel Enache <[EMAIL PROTECTED]> wrote: > > I have a sqlite 2.0 DB and I need the data within. > > I tried open it with almost all the tools I finded. > > All say that: "file is encrypted or is not a database" > >

[sqlite] Support for ODBC?

2004-10-14 Thread R S
Hi, Can an application access data from SQLite via ODBC? Didn't see documentation on the same. Thanks!

[sqlite] Is this an in-memory database too

2004-10-07 Thread R S
I was trying to find info for the same, but couldn't. Do let me know.

Re: [sqlite] Is this an in-memory database too

2004-10-07 Thread R S
TED]> wrote: > > > R S wrote: > > I was trying to find info for the same, but couldn't. Do let me know. > > > > SQLite normally operates off of disk. It reads a little from the > disk as it can get by with, but once it reads from the disk it > caches inform

[sqlite] Reindexing a table in SQLite

2005-03-02 Thread R S
Hi, How do I programatically reindex a table in SQLite? Is it possible? I did a search but didn't find much. I am using version 3.0.8 Thanks!

Re: [sqlite] Reindexing a table in SQLite

2005-03-02 Thread R S
I periodically delete older records from my table (which contains hundreds of thousands of entries). I want to reindex the table for more efficient access. Thanks! On Wed, 02 Mar 2005 18:20:39 -0600, Kurt Welgehausen <[EMAIL PROTECTED]> wrote: > You can drop an index and later create it again.