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
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: email@example.com
! Maybe I constructed my query wrong
It looks something like this:
select fields from table order by id desc limit 50;
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
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
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,
and I use insert into MyTest(null,
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
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
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?
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
Also the column is a tinyint..Guess that shouldnt matter..But just FYI...
On 8/4/05, R S <[EMAIL PROTECTED]> wrote:
> 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
I presume it is a PENDING lock? Does SQLite acquire a PENDING lock as
soon it begins an Update operation?
I have a DB containing a table called MyTable which is about 3Gb in size
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
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.
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
> 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
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
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;)
> - Original Message
> From: R S <[EMAIL PROTECTED]>
> To: sql
by default? I am using 3.2.2
I ran across this doc
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
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;
> group by columnVal
> having totalCount > 10
> If you also create an index on insertionTime, your query should be fast.
> R S schrieb:
> >I am trying to use this query and notice that the execution time
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
In my case Delete happens reasonably OK but Vaccuuming takes incredibly
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
It would be the other way around, no?
The larger the no of inserts within a Transaction, the better the
On 10/27/05, Hannes Ricklefs <[EMAIL PROTECTED]> wrote:
> I was wondering if anyone has any experience with the number of INSERT
> statements in one
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"
Can an application access data from SQLite via ODBC? Didn't see
documentation on the same.
I was trying to find info for the same, but couldn't. Do let me know.
> 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
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
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.
On Wed, 02 Mar 2005 18:20:39 -0600, Kurt Welgehausen <[EMAIL PROTECTED]> wrote:
> You can drop an index and later create it again.
Mail list logo