Richard,
Ok, im pretty clear on the file locking being the cause of the
problems with the sqlite3 * structures, but thanks for confirming it.
I understand that on platforms that dont have this issue its not a
problem.
But why then can i not have a single transaction wrapping a single
connection
"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> Even on the
> platforms where a single sqlite3 * structure can be used on multiple
> threads (provided it is not at the same time), it is not possible to
> have a transaction which works across these threads.
I beg to differ. What makes you think th
Roger,
I think sqlite suffers somewhat from a bit of an identity crisis.
Whilst it is both a library and a piece of code which you embed in a
project it is often talked about as though it is some external
component.
Technically sqlite is not thread safe. Just because the library has
explicitly
"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> Richard,
>
> My complaint, if you want to call it that. Was simply that there are
> seemingly artificial constraints on what you can and cant do accross
> threads.
>
> If i have a linked list, i can use it across threads if i want to,
> provided that
Emerson Clarke wrote:
If i have a linked list, i can use it across threads if i want to,
provided that i synchronise operations in such a way that the list
does not get corrupted.
And of course you also have to know about memory barriers and compiler
re-ordering. That is highly dependent on t
Richard,
My complaint, if you want to call it that. Was simply that there are
seemingly artificial constraints on what you can and cant do accross
threads.
If i have a linked list, i can use it across threads if i want to,
provided that i synchronise operations in such a way that the list
does
"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
>>
> It seemed to me that making a library which only functioned on a per
> thread basis was something that you would have to do deliberately and
> by design.
I'm still trying to understand what your complaint is.
--
D. Richard Hipp <[EMAIL PROTECTED]
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Emerson Clarke wrote:
| I am left to assume that all other locking mechanisms like ipc and
| files have already been tried and been found wanting. I also assume
| that priority has been given to making sqlite operate across network
| boundaries rathe
Roger,
My original question was in fact not a statement. I did not want
sqlite to work differently. Rather the opposite, sqlite already works
differently to the way i, and probably a lot of users assume that it
would. So all i wanted to know was why that is the case.
It seemed to me that maki
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Emerson Clarke wrote:
| I have deliberately tried to avoid giving too much detail on the
| architecture of the index since that was not the point and i didnt
| want to end up debating it.
I don't want to debate your index architecture either :-). Qu
Roger,
I have deliberately tried to avoid giving too much detail on the
architecture of the index since that was not the point and i didnt
want to end up debating it.
The design of the index is not the issue, suffice to say that i think
you are over complicating things. It is a desceptively simp
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
You never answered the bit about whether indexing A can be done at the
same time as B. (I get the feeling you have a certain design in mind
and insist that SQLite changes to meet that design, rather than change
your own design around the constraints
Emereson,
And you can't do this with oracle either, That is create a connection and pass
it around between multiple threads.. Oracle would be very very unhappy if you
did that. Oracle utilizes a context variable to distinguish between threads and
utilizes precompiler flags to enable thread su
Unless you have a multi processor machine or some form of parallel
processing I cannot see how you can do anything other than slow your
procesing by adding threading overhead.
You can simplify the conception of the whole process by understanding
that Sqlite writes to a single file on a single
Ken,
Yes you cannot have multiple threads within the same transaction, and
you cannot pass a connection between threads.
I think we have an undestanding about the performance situation, and
we are getting to the real heart of the issue, which why it is not
possible to have a single transaction,
The test server.c code is an example that has a single thread that performs the
DB interactions each client thread, communicates via a queue interface. Each
client will get serialized into the DBserver thread and get its work done.
Thus eliminating any lower level locking and mutexing inside th
OK, thanks Joe.
I will look up those peculiarities as you suggested, im interested in
understanding what they are.
I agree with you, DRH has done a great job. Sqlite is a fantastic
piece of software and and outstanding example of the way open source
should be. It is small, efficient and incred
Ken,
Thanks.
Ok, i think i did miss the point with your test case. I assumed that
it was always possible to perform multiple select, insert, delete, and
update statements within a single transaction anyway. Which i guess
relates to you last point. I dont see why if i have a single global
tran
This old mailing list thread better describes this proposed algorithm
to contain all open/close/lock/unlock activity in a single work thread:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg15852.html
--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> As you already know, it's not just Linux -
Richard,
First let me say thank you for your earilier suggestion regarding the
compile time options and overlaoding of the os routines. It was
exactly the kind of information i was searching to for and i am
looking into using that strategy at the moment.
Sorry if ive managed to offend you, plea
My last message cites some of the peculiarities of POSIX fcntl() locking.
Search the SQLite mailing list archives for more detailed info as
it pertains to SQLite.
As for having a single unified (locking) model - SQLite already
employs such a strategy as best as is possible given the portable
natu
--- [EMAIL PROTECTED] wrote:
> On some older versions of Linux, SQLite is unable to pass
> database connections from one thread to another. But this
> is a problem with the threading libraries used in those older
> linux versions and is outside the control of SQLite. I do not
> think this issue c
Joe,
Im interested to know what those constraints are and why ?
The only reason i mentioned shared memory is because it provides a
platform and filesystem agnostic way of handling ipc. Obvioulsy i
dont know the ins and outs of the locking process, but i just thought
it would make sense to have
Emerson,
You just need to lock the entire transaction using a mutex before hand.
That means each thread will have its own sqlite cache. Reread the sqlite
locking and concurrency guide, you'll see that SQLITE is NOT a transaction
based system such as Postgress/mysql/ oracle. It locks the
"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> Im not sure that the current situation of forced thread
> incompatibility is better than leaving it up to users to manage the
> threading. Usually it is assumed that a library is thread unsafe
> unless otherwise specified.
>
> Developing multithreaded
--- Emerson Clarke <[EMAIL PROTECTED]> wrote:
> Developing multithreaded applications is difficult, i wouldnt dispute
> that. But i do dispute the wisdom of actively making a library
> incompatible with threads.
"Actively"? That's a bit much.
There are constraints on the ability to pass SQLite
In general worker threads is not an efficient solution to the problem
even if parallelism is available. There is nothing to be gained by
having one thread handing off A to another set of worker threads
because you have to synchronise on whatever queue/list/pipe you use to
transfer the information
Im not sure that the current situation of forced thread
incompatibility is better than leaving it up to users to manage the
threading. Usually it is assumed that a library is thread unsafe
unless otherwise specified.
Developing multithreaded applications is difficult, i wouldnt dispute
that. Bu
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Emerson Clarke wrote:
| For architectural reasons the above steps must be performed in that
| order. This means that operations cant be separated or queued up in
| the way that you suggested. Each step is dependent on the previous
| step.
I was look
On 12/27/06, Emerson Clarke <[EMAIL PROTECTED]> wrote:
The first question is why database locking has been enabled on a per thread
basis instead of per process so that the normal methods of thread
synchronisation (mutexes, ciritcal sections) could not be used for
maintaining consistency within th
Ken,
Thanks i understand your example well.
What im looking for is the ability to have multiple threads perform a
write operation based on my mutex, not some internal sqlite write
mutex. If i am managing the concurrency and performing correct
synchronisation, why can i not have multiple threads
The indexing process works like this.
1.) Open a document and parse its contents.
2.) Look up records in the first database based on the contents of the
document, updating records where appropriate and inserting new ones.
3.) Transforming the document based on what was obtained from the
first dat
Emerson,
Is the Database on the same disk as the rest of the file operations? If so is
it possible that you are I/O bound and causing seek issues due to i/o access
patterns?
Take a look at the test_server.c code in the sqlite/src directory. I used that
as a basis to build a custom library
Emerson Clarke wrote:
The idea is that because i am accessing two databases, and doing
several file system operations per document, there should be a large
gain by using many threads. There is no actual indexing process, the
whole structure is the index, but if anything the database operations
t
I am curious as to how multiple threads would perform faster inserts
into an Sqlite database, which is a single file plus the journal.
Are you using a multiple processor machine?
Emerson Clarke wrote:
Roger,
Thanks for the suggestions. I think using a worker thread and a queue
would be equiv
Roger,
Thanks for the suggestions. I think using a worker thread and a queue
would be equivalent to just running a single thread since it
effectively makes the database operations synchronous. Although i can
see what your driving at regarding the transactions every n records.
The idea is that
"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
>
> The first question is why database locking has been enabled on a per thread
> basis instead of per process so that the normal methods of thread
> synchronisation (mutexes, ciritcal sections) could not be used for
> maintaining consistency within the
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Emerson Clarke wrote:
| modified the api to ensure that each thread was given its own sqlite3 *
| structure.
I would assume that the actual indexing is the expensive part since it
involves a lot of I/O (SQLite page size is 1KB). Why don't you do thi
I built and use an application server which embeds Sqlite and processes
web traffic. It is multi-threaded and can handle very many connections.
It is very fast because it uses no IPC channels or process creation.
It caches threads and reuses them rather than creating and killing them.
It can
James Mills uttered:
Hi Richard,
When I mean high-traffic I would imagine more than 1 hit/s.
I do want to clear something up though (if you could):
If a site using sqlite takes 700ms to load and there are
two simultaneous (as simultaneous as one can get) hits
to the site, say user A and user
Hrmm also a couple of other things...
In order to make a decision, I would need some way of
running tests and simulations so I can come up with some
numbers. Then scale that up and use it as an indicator
for our decision. Do you have any tools that'll help with
this ?
cheers
James
--
--
-"Probl
Hi Richard,
I appreciate your feedback on the matter. I myself have
used SQLite in many of my applications in the past for quite
some years now. Most of them do indeed only write to
or read from teh database for only fractions of a second.
When I mean high-traffic I would imagine more than 1 hit/
James Mills <[EMAIL PROTECTED]> wrote:
> Hi Folks,
>
> I'm wanting to use SQLite in an embedded web application
> that will serve as a proxy and possible serve up many
> connections at once. I'm talking here of high-traffic
> through this web app.
>
> Question is, how will SQLite perform under th
> -Original Message-
> From: Mohd Radzi Ibrahim [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 18, 2006 3:23 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite performance questions.
>
> Hi,
> Could you tell me what breaks the transaction?
Hi,
Could you tell me what breaks the transaction? Is create table/index within
transaction breaks it?
best regards,
Radzi.
- Original Message -
From: "Isaac Raway" <[EMAIL PROTECTED]>
To:
Sent: Thursday, October 19, 2006 2:07 AM
Subject: Re: [sqlite] sqlite perfo
I'm going to agree with Robert here, I have an application that makes heavy
use of large blob of text in a sqlite database. Performance was unbearable,
wrapping even small sets of operations in transactions greatly improved the
performance. I don't have numbers, but suffice it to say that it went
> -Original Message-
> From: Andrew Cheyne [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 18, 2006 8:08 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] sqlite performance questions.
[snip]
> I have then been writing some sample C programs making use
> of the C API,
> but hav
Joe Wilson wrote:
--- Martin Jenkins <[EMAIL PROTECTED]> wrote:
For :memory: databases, long periods
were observed where the VM size crept up but I/O write bytes did not,
followed by periods where I/O bytes increased.
If you use "PRAGMA temp_store=MEMORY" with your :memory: database
you will
> I think some other factor is at play here.
> SQLite 2.x's memory databases are still twice as fast at batch inserts
> than either 3.x's disk-based databases or 2.x's disk-based databases
> when the DB size is less than physical machine memory.
I did some experimentation with an SQLite 2.8.17 :m
--- Martin Jenkins <[EMAIL PROTECTED]> wrote:
> For :memory: databases, long periods
> were observed where the VM size crept up but I/O write bytes did not,
> followed by periods where I/O bytes increased.
If you use "PRAGMA temp_store=MEMORY" with your :memory: database
you will have no I/O wh
Hi,
I am really really thankful to all the members of this group. The Discussion
here was really very helpful for me and also for the others.
I was not as much experienced as the other members who took part in this
dicussion, but i worked hard ad spent a lot of time to find out why i am
getti
Dennis Cote wrote:
Joe Wilson wrote:
I think some other factor is at play here.
Yes there is another factor at work here. [...] I suspect there are
optimizations that could be made to the memory I/O routines to speed
them up, they should at least be able to run slightly faster than file
ba
Joe Wilson wrote:
I think some other factor is at play here.
SQLite 2.x's memory databases are still twice as fast at batch inserts
than either 3.x's disk-based databases or 2.x's disk-based databases
when the DB size is less than physical machine memory.
Joe,
Yes there is another factor
> On 23 Jun 2006, at 14:16, Dennis Cote wrote:
> > Most of SQLite's disk I/O is actually going to the memory used for
> > the operating system's disk cache, not directly to the disk. Hence
> > its speed is not much different when using a disk based database
> > than a memory based database. I
--- Manzoor Ilahi Tamimy <[EMAIL PROTECTED]> wrote:
> DISK MODE
> 3000 000 INSERTS 31 Seconds 96774 INSERTS / Sec
> "SELECT * from t" 5 Seconds.
>
> MEMORY MODE
> 3000 000 INSERTS 53 Seconds 56604 INSERTS / Sec
> "SELECT * from t" 5 Seconds.
>
> Can I reduce the TIME of DISK mode or t
On 23 Jun 2006, at 14:16, Dennis Cote wrote:
John Stanton wrote:
Cacheing will drive you crazy.
Very well put.
Most of SQLite's disk I/O is actually going to the memory used for
the operating system's disk cache, not directly to the disk. Hence
its speed is not much different when using
John Stanton wrote:
Cacheing will drive you crazy.
Very well put.
Most of SQLite's disk I/O is actually going to the memory used for the
operating system's disk cache, not directly to the disk. Hence its speed
is not much different when using a disk based database than a memory
based databa
Cacheing will drive you crazy.
CARTER-HITCHIN, David, GBM wrote:
This is a modified version of the test code I posted to show
that there
was a small but definite SLOWDOWN when using :memory:
databases compared
to a database in a file on disk. It seems strange, but it is
true. Use a
disk
At 03:09 23/06/2006, you wrote:
#include "stdafx.h"
<>
samples, t, samples / t);
getch();
//*
Here you should create index for table t. In your previous example, for hvh itm
// Select Time check
//*
> This is a modified version of the test code I posted to show
> that there
> was a small but definite SLOWDOWN when using :memory:
> databases compared
> to a database in a file on disk. It seems strange, but it is
> true. Use a
> disk file for best speed.
If true, this is crazy. Memory
Manzoor Ilahi Tamimy wrote:
I have tested my Code with the following PRAGMA and still not getting any
change in time.
//--
sqlite3_exec(db, "PRAGMA temp_store=2", NULL, NULL, NULL);
sqlite3_exec(db, "PRAGMA synchronous=0", NULL, NULL,
Manzoor Ilahi Tamimy wrote:
>sqlite3_exec(db, "create table t (a integer, b float, c text,d integer,
> e float, f text, g float, h text)", NULL, NULL, NULL);
>
>sqlite3_exec(db, "begin transaction", NULL, NULL, NULL);
>
>sqlite3_prepare(db, "insert into t values (?, ?, ?, ?, ?
I have tested my Code with the following PRAGMA and still not getting any
change in time.
//--
sqlite3_exec(db, "PRAGMA temp_store=2", NULL, NULL, NULL);
sqlite3_exec(db, "PRAGMA synchronous=0", NULL, NULL, NULL);
sqlite3_exec(db, "PRAG
Manzoor Ilahi Tamimy wrote:
Here Is The Schema For these Tables.
CREATE TABLE HVH (
Field1 VARCHAR(8), IDC VARCHAR(4),
Field3 VARCHAR(2), Field4 VARCHAR(4),
Field5 VARCHAR(7), Field6 VARCHAR(8),
Field7 VARCHAR(1), Field8 FLOAT);
CREATE TABLE ITM(
IDC VARCHAR(4),ITEMNAME VARCHAR(2
At 03:30 20/06/2006, you wrote:
Here Is The Schema For these Tables.
CREATE TABLE HVH (
Field1 VARCHAR(8), IDC VARCHAR(4),
Field3 VARCHAR(2), Field4 VARCHAR(4),
Field5 VARCHAR(7), Field6 VARCHAR(8),
Field7 VARCHAR(1), Field8 FLOAT);
CREATE TABLE ITM(
IDC VARCHAR(4),
"Manzoor Ilahi Tamimy" <[EMAIL PROTECTED]> writes:
> Here Is The Schema For these Tables.
>
> CREATE TABLE HVH (
> Field1 VARCHAR(8),IDC VARCHAR(4),
> Field3 VARCHAR(2),Field4 VARCHAR(4),
> Field5 VARCHAR(7),Field6 VARCHAR(8),
> Field7 VARCHAR(1),Field8 FLOAT);
>
> CREATE TABL
Here Is The Schema For these Tables.
CREATE TABLE HVH (
Field1 VARCHAR(8), IDC VARCHAR(4),
Field3 VARCHAR(2), Field4 VARCHAR(4),
Field5 VARCHAR(7), Field6 VARCHAR(8),
Field7 VARCHAR(1), Field8 FLOAT);
CREATE TABLE ITM(
IDC VARCHAR(4),ITEMNAME VARCHAR(20),
COLUMN3
count(*) is pretty slow in sqlite because it basically does select *
and then counts the results. This means it's looking through your
whole big file.
You can come up with some tricks like keeping a separate count
up-to-date with triggers. There have been some old threads on
optimizing count that
Dear ALL,
I am really thankful to Bill King, Micha Bieber , Derrell for your valuable
suggestions.
I was really confused that which way should I follow now, because I was sure
that SQLite will work much better. when I got the suggestion about Firebird
then again I went to the comparison page
Bill King <[EMAIL PROTECTED]> writes:
> Manzoor Ilahi Tamimy wrote:
>
>> We are Using SQLite for one of our project.
>>
>>The Database Size is more than 500 MB.
>>It contain one table and about 10 million Records.
> Err, for that size, I'd recommend going something heavier, like
> firebird. This
Monday, June 19, 2006, 07:37:22, Manzoor Ilahi Tamimy wrote:
> The Database Size is more than 500 MB.
> It contain one table and about 10 million Records.
I had problems with even more records (roughly 25 million, > 1GB of
data) and I've stopped efforts to do it in pure sqlite in the end, also
b
Manzoor Ilahi Tamimy wrote:
Hello All,
We are Using SQLite for one of our project.
The Database Size is more than 500 MB.
It contain one table and about 10 million Records.
We are facing Problem in the select with single Join. The join is between a
big table and a small table. The small t
On Thu, 16 Mar 2006 [EMAIL PROTECTED] wrote:
>Sorry it took me some time to get back to this thread.
No problem. I missed your reply anyway:)
>
>- Original Message
>From: Christian Smith <[EMAIL PROTECTED]>
>
>> When your database does not fit in memory, yes, you're right, the OS may
Sorry it took me some time to get back to this thread.
- Original Message
From: Christian Smith <[EMAIL PROTECTED]>
> When your database does not fit in memory, yes, you're right, the OS may
> well get caching wrong, and in the worst way possible. Two things though:
> - SQLite does have
On Mon, 13 Mar 2006 [EMAIL PROTECTED] wrote:
> [snip aio API stuff]
>After finding out about this api, I found out that at least mysql and
>postgresql use it, so I am guessing that changing the sql engine to
>generate batches of read/writes is possible.
>
>My guess is that using this api will incr
- Original Message
> From: Joe Wilson <[EMAIL PROTECTED]>
> The disk read/write reordering optimization only works if the
> database file is contiguous on the disk and not fragmented.
> --- [EMAIL PROTECTED] wrote:
> > Basically, the db file is accessed with seek + read/write operations
On Fri, 10 Mar 2006 [EMAIL PROTECTED] wrote:
>- Original Message
>> From: Christian Smith <[EMAIL PROTECTED]>
>
>> But SQLite depends on the OS caching abilities for much of it's
>> performance. Removing it is like saying SQLite is rubbish on Intel
>> processors after testing on a i486.
>
The disk read/write reordering optimization only works if the
database file is contiguous on the disk and not fragmented.
--- [EMAIL PROTECTED] wrote:
> Basically, the db file is accessed with seek + read/write operations.
> Given a set of such operations, it can be very beneficial to reorder th
It also helps to put the database file(s) on a different
physical hard drive than the hard drive of the operating
system and swap.
--- Carl Jacobs <[EMAIL PROTECTED]> wrote:
> Nicolas,
>
> From: <[EMAIL PROTECTED]>
> > On the other hand, I tried to make better use of the cache: if I run my 1M
Nicolas,
From: <[EMAIL PROTECTED]>
> On the other hand, I tried to make better use of the cache: if I run my 1M
inserts in 10 transactions of 100,000, things get a bit slower than 100
transactions of 10,000 inserts.
> I tried one transaction of 1,000,000 inserts and the test app hangs at
100% cpu
- Original Message
> From: Christian Smith <[EMAIL PROTECTED]>
> But SQLite depends on the OS caching abilities for much of it's
> performance. Removing it is like saying SQLite is rubbish on Intel
> processors after testing on a i486.
yes and no: while it's nice to be able to rely someh
- Original Message
> From: [EMAIL PROTECTED]
> <[EMAIL PROTECTED]> wrote:
> >
> > One question though: are the file access "sorted", so that seeks are
> > minimised when performing a transaction (making the assumption that
> > the file is not fragmented on disk)?
> >
> I'm not sure wha
<[EMAIL PROTECTED]> wrote:
>
> One question though: are the file access "sorted", so that seeks are
> minimised when performing a transaction (making the assumption that
> the file is not fragmented on disk)?
>
I'm not sure what you are asking. Can you restate your question?
--
D. Richard Hipp
On Thu, 9 Mar 2006 [EMAIL PROTECTED] wrote:
>- Original Message
>> From: Christian Smith <[EMAIL PROTECTED]>
>
>> Is this a likely usage scenario? Will your application regularly
>> umount/mount the filesystem between transactions? While sounding
>> facetious, I'm not trying to. Your othe
I've had similar speed increases using the same technique
you've described for the last 10 revs of SQLite. I never
had any noticable improvement when fiddling with the
SQLite cache parameters. I just assumed it was a Linux thing.
But maybe not.
> So what I tried next was to simply run my app af
- Original Message
> From: Christian Smith <[EMAIL PROTECTED]>
> Is this a likely usage scenario? Will your application regularly
> umount/mount the filesystem between transactions? While sounding
> facetious, I'm not trying to. Your otherwise excellent example is let down
> by a probably
On Thu, 9 Mar 2006, Christian Smith wrote:
>On Wed, 8 Mar 2006 [EMAIL PROTECTED] wrote:
>
>>
>>One question though: are the file access "sorted", so that seeks are
>>minimised when performing a transaction (making the assumption that the
>>file is not fragmented on disk)?
>
>
>The OS will sort IO
On Wed, Mar 08, 2006 at 03:09:15PM -0800, [EMAIL PROTECTED] wrote:
[...]
> One question though: are the file access "sorted", so that seeks are
> minimised when performing a transaction (making the assumption that the file
> is not fragmented on disk)?
I assume you tried your tests with "PRAGMA
On Wed, 8 Mar 2006 [EMAIL PROTECTED] wrote:
>- Original Message
>From: [EMAIL PROTECTED]
>To: sqlite-users@sqlite.org
>
>> SQLite inserts in records in primary key order. (That is not
>> strictly true - but it is close enough to being true for the
>> purposes of what follows.) So when y
On Tue, 7 Mar 2006 [EMAIL PROTECTED] wrote:
>Hi all
>
>it seems that I am running in a problem with the way sqlite accesses the
>disk when inserting rows of data in databases that are large in number of
>records but not necessary big on disk (I am talking millions of records
>in files that are in
- Original Message
From: [EMAIL PROTECTED]
To: sqlite-users@sqlite.org
> SQLite inserts in records in primary key order. (That is not
> strictly true - but it is close enough to being true for the
> purposes of what follows.) So when you insert records that
> are already in fname order,
<[EMAIL PROTECTED]> wrote:
> Hi all
>
> it seems that I am running in a problem with the way sqlite accesses the disk
> when inserting rows of data in databases that are large in number of records
> but not necessary big on disk (I am talking millions of records in files that
> are in the order
> well, it could be true, but not in the queries i have posted. i "group
> by" column "a" and there is an index on column "a", so sqlite does not
> have to do anything to compute key. it does not even have to back to
Do not confuse the index key with the aggregator key. The two may be
the s
On Fri, 2005-04-08 at 11:53 -0700, Maksim Yevmenkin wrote:
> CREATE INDEX data_by_a ON data (a);
>
> > time sqlite3 db 'select n2 from data where a <= 18234721' > /dev/null
> 25.95u 0.71s 0:27.02 98.6%
>
If you make the index look like this:
CREATE INDEX data_by_a ON data(a, n2);
Then SQLite
On Tue, 12 Apr 2005, Maksim Yevmenkin wrote:
>Dear SQLite users,
>
>consider this
>
> [snip]
>
>it only took 4+ seconds to read, parse, perform hash table lookup and
>sum the data. note that for unique 1417 keys it had to do hash lookup
>and hash insert.
>
>so, just with plain ascii file i get fou
Maksim Yevmenkin wrote:
>
>>>so, just with plain ascii file i get four times the speed i get with
>>>sqlite. note that my c program will scale linearly with the size of
>>>dataset (just like i see with sqlite).
>>>
>>>
>> With anything related to computers, there are always tradeoffs - mos
John.
> >i think, i know what is going on here. the problem is that every time
> >i do an indexed scan sqlite has to
> >
> >1) fetch index pages
> >
> >2) fetch data pages that match "where" condition
> >
> >because both index and data are in the same file sqlite has to perform
> >insane amount of
> > SELECT count(*) FROM (SELECT a,n1 FROM data WHERE a <= 18234721 GROUP BY a);
> >
> > > time sqlite3 db < test.sql
> > 30
> > 1024
> > 1417
> > 13.14u 1.06s 0:14.40 98.6%
>
> Have you tried doing the query like this:
>
> SELECT count(*) FROM (SELECT a,nl FROM data WHERE a-18234721<=0 GRO
Maksim Yevmenkin wrote:
i think, i know what is going on here. the problem is that every time
i do an indexed scan sqlite has to
1) fetch index pages
2) fetch data pages that match "where" condition
because both index and data are in the same file sqlite has to perform
insane amount of seek() calls
On Tue, 2005-04-12 at 16:17 -0700, Maksim Yevmenkin wrote:
> SELECT count(*) FROM (SELECT a,n1 FROM data WHERE a <= 18234721 GROUP BY a);
>
> > time sqlite3 db < test.sql
> 30
> 1024
> 1417
> 13.14u 1.06s 0:14.40 98.6%
>
Have you tried doing the query like this:
SELECT count(*) FROM (SEL
101 - 200 of 239 matches
Mail list logo
