Thomas,
> > with sum(n1) added query runs twice as slow. as i was told its because
> > sqlite has to fetch data row. fine, but why its soo slow?! and it
>
>Because for each row it has to compute the aggregate key, find the
> aggregator for that key and increment the sum for that aggregate key
> with sum(n1) added query runs twice as slow. as i was told its because
> sqlite has to fetch data row. fine, but why its soo slow?! and it
Because for each row it has to compute the aggregate key, find the
aggregator for that key and increment the sum for that aggregate key.
That's a lot mo
Dear SQLite users,
consider this
schema
-
sqlite> .schema data
CREATE TABLE data(
a INTEGER,
b INTEGER,
c CHAR,
d INTEGER,
e INTEGER,
n1 FLOAT,
n2 FLOAT
);
CREATE INDEX data_by_a on data (a);
data set
-
sqlite> select count(*) from data where a <= 18234721;
92
Gé,
thanks for the suggestion. unfortunately it did not make any
difference :( below is the results. as you can see it takes 7+ seconds
to "group by" 333,392 records and i'm grouping by column on which i
have index. again, i'm not a database guy, but i think that is slow.
perhaps someone can comm
Maksim,
Some things you could try:
1) increase cache memory
You may be causing a lot of cache misses if the size of the query result
is very large compared to the size of the cache. Index-based searches
can cause multiple reloads of the same page because of a lack of
locality in the cache. An in
Robert,
> [snip]
>
> > i said i print these rows to /dev/null too in my perl code. plus the
> > perl code does some other things such as joining these rows with other
> > hashes and summing the numbers.
>
> That's fine. I was merely trying to account for the 50% speed difference
> between the t
> -Original Message-
> From: Maksim Yevmenkin [mailto:[EMAIL PROTECTED]
> Sent: Monday, April 11, 2005 12:27 PM
> To: Robert Simpson
> Cc: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite performance problem
>
[snip]
> i said i print these rows to /dev/
Robert,
> > time sqlite3 db 'select n1 from data where a <= 18234721' > /dev/null
> > 26.15u 0.59s 0:27.00 99.0%
> >
> > time sqlite3 db 'select n1 from data where a <= 18234721' > /dev/null
> > 26.04u 0.61s 0:26.91 99.0%
> >
> > time sqlite3 db 'select e from data where a <= 18234721' > /dev/null
Let's recap ...
> time sqlite3 db 'select n1 from data where a <= 18234721' > /dev/null
> 26.15u 0.59s 0:27.00 99.0%
>
> time sqlite3 db 'select n1 from data where a <= 18234721' > /dev/null
> 26.04u 0.61s 0:26.91 99.0%
>
> time sqlite3 db 'select e from data where a <= 18234721' > /dev/null
> 12.
Robert,
> > i guess, i can believe this. however its pretty disappointing to get
> > 50% improvement on 30 times less dataset :(
> >
> > but how do you explain this?
> >
> > sqlite> .schema data
> > CREATE TABLE data
> > (
> >a INTEGER,
> >b INTEGER,
> >c CHAR,
> >d
> -Original Message-
> From: Maksim Yevmenkin [mailto:[EMAIL PROTECTED]
> Sent: Monday, April 11, 2005 9:59 AM
> To: Christian Smith
> Cc: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite performance problem
>
> i guess, i can believe this. however its pre
Christian,
thanks for the reply.
> >i'm having strange performance problem with sqlite-3.2.0. consider the
> >following table
> >
> > [snip]
> >
> >now the problem:
> >
> >1) if i do a select with an idex it takes 27 sec. to get 92 rows
> >
> >> time sqlite3 db 'select n2 from data where a <=
On Fri, 8 Apr 2005, Maksim Yevmenkin wrote:
>Hello,
>
>i'm having strange performance problem with sqlite-3.2.0. consider the
>following table
>
> [snip]
>
>now the problem:
>
>1) if i do a select with an idex it takes 27 sec. to get 92 rows
>
>> time sqlite3 db 'select n2 from data where a <=
On Sun, 2005-02-27 at 15:31 -0500, Clay Dowling wrote:
> >
> >Anyone using other compiler to build sqlite on Windows ? Maybe Borland ?
> >I seems there is a lot o room for faster sqlite.dll by using other
> >compiers than VC++ 6. We can run some tests to see which is faster ...
> >
> I thought I re
Ionut Filip wrote:
Hi,
So the VC++ 7.1 dll is much faster than the VC++ 6, up to 20% in some
operations, and the "MS mixed dll load bug" is not a problem, assuming
sqlite doesn't mix native code with managed .net code :).
Anyone using other compiler to build sqlite on Windows ? Maybe Borland ?
I se
; 'Keith Herold'
Subject: RE: [sqlite] sqlite performance variationin linux and windows
> -Original Message-
> From: Keith Herold [mailto:[EMAIL PROTECTED]
> Sent: Friday, February 25, 2005 3:57 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite performance
build a version up to 10-15% faster
I saw the same kind of speedup (and sometimes up to 25%) depending on
if assertions are enabled. I believe they are enabled by default.
Adding -DNDEBUG turns them off.
Roger
> -Original Message-
> From: Keith Herold [mailto:[EMAIL PROTECTED]
> Sent: Friday, February 25, 2005 3:57 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite performance variationin linux and windows
>
> http://www.adtmag.com/article.
http://www.adtmag.com/article.asp?id=7421
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vstechart/html/vcconMixedDLLLoadingProblem.asp
It's been awhile since Iooked at this, but I distinctly remember
reading from one of the product managers at MS that this wouldn't be
fixed
On Fri, Feb 25, 2005 at 02:25:06PM -0800, Keith Herold wrote:
> Well, MS has said that VC++ .NET has a serious multithreading issue;
> a lot of companies are staying on VC6 for that reason.
Keith, I wasn't aware of that. Could you point us to more info on the
problem, please?
--
Andrew Piskorsk
Well, MS has said that VC++ .NET has a serious multithreading issue; a
lot of companies are staying on VC6 for that reason.
--Keith
On Fri, 25 Feb 2005 23:47:32 +0200, Ionut Filip <[EMAIL PROTECTED]> wrote:
>
> Hi Appadurai,
>
> From my experience the stock sqlite.dll is not very fast. I guess
Hi Appadurai,
>From my experience the stock sqlite.dll is not very fast. I guess it's
build with VC++ 6. Using VC++ 7.1 I was able to build a version up to
10-15% faster, not a bad optimization for 0 lines of code :) . It was a
little bigger, about +25%, but for desktop this is not a problem. I o
On 24 Feb 2005 at 19:08, Neelamegam Appadurai wrote:
> Can anyone please help me how to increase the performance on windows
> enviroment
Have a look at this Wiki
http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows
Some basic things to be aware of in there with regards to Speed and
Win
Hi,
Neelamegam Appadurai wrote:
Can anyone please help me how to increase the performance on windows
enviroment
One thing you can do is to increase the page size from 1024 to 4096.
That seems to match either Windows' swapfile-pagesize or the default
page size of NTFS (there is disagreement ove
=== On 2004-06-18, ben.carlyle wrote ===
..
>
>I'd like to take this soapbox opportunity to again thank drh and his
>associates for the wonderful work they put into sqlite and their genine
>personal commitment to this project. Sqlite is a great product, and a
>well-targeted one.
>
>Benjamin
>
J
TED]>
18/06/2004 12:09 AM
To: [EMAIL PROTECTED]
cc:
Subject:Re: [sqlite] SQLite performance with mid-size databases
Well this does not sound good at all. I would think
that SQLite would at least do a better job at queries.
The issues with performance and scal
Ulrik Petersen wrote:
this is also just a stab in the dark, and I subscribe to the digest
version of the mailinglist, so I may not have the latest.
Perhaps you are doing something like
SELECT A.x, A.y
FROM A
WHERE A.rowid = xxx
OR A.rowid = yyy
OR A.rowid = zzz
OR A.rowid = ...
etc.etc. with
See below:
--- Jacob Engstrand <[EMAIL PROTECTED]> wrote:
> To disable the
> I1A index,
> rewrite the WHERE clause like this:
>
> SELECT * FROM t1 WHERE a+0=5 AND b='xyzzy';
>
> To disable the I1B index you could write
>
> SELECT * FROM t1 WHERE a=5 AND b LIKE 'xyzzy';
>
Why
Well this does not sound good at all. I would think
that SQLite would at least do a better job at queries.
The issues with performance and scalability is of
great concern. I was planning on create some new apps
that use SQLite, but now I'm wondering is this little
database is capable of handle ov
f data is visibly faster...indicating that
>the disk cache is very favorably impacting the speed of the queries.
>
>Richard
>
>-Original Message-
>From: Tim Anderson [mailto:[EMAIL PROTECTED]
>Sent: Wednesday, June 16, 2004 10:33 AM
>To: Richard Kuo
>Subje
Richard Kuo wrote:
I suspect some unnecessary disk access has to be the problem...despite
the small amount of new guide information being queried out, disk bytes
read is several times higher than with MS access and scrolling back over
previously accessed areas of data is visibly faster...indicating
he is very favorably impacting the speed of the queries.
Richard
-Original Message-
From: Tim Anderson [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 16, 2004 10:33 AM
To: Richard Kuo
Subject: RE: [sqlite] SQLite performance with mid-size databases
> -Original Message-
> Fr
On 2003-01-27, at 00.00, D. Richard Hipp wrote:
> Enterprise scale database engines do a more sophisticated job
> of selecting indices (when there is a choice like this) by
> collecting lots of statistics on the indices and using complex
> algorithms to make the choice. SQLite takes the easy way
On 2004-06-16, at 06.04, Richard Kuo wrote:
Empirically speaking, we display our data in a scrolling 2
dimensional grid format. With MS access, this grid responds
instantaneously when moving through the grid. With SQLite, there is
very noticable stalling and lag and the disk i/o is higher tha
> -Original Message-
> From: Richard Kuo [mailto:[EMAIL PROTECTED]
> Sent: 16 June 2004 05:04
> To: [EMAIL PROTECTED]
> Subject: [sqlite] SQLite performance with mid-size databases
>
> Hi. We are using SQLite to store and retrieve data rows where
> each row is roughly 2K total in siz
This could be related with the grid handling code.
In another post I already talked about the "SELECT COUNT(*) ..." performance problem,
that can only be solved by tuning the code in the grid control itself.
If the code is generic, it's a strong possibility it isn't optimized for this, and
assume
On Tue, 15 Jun 2004 23:04:04 -0500, you wrote:
>Hi. We are using SQLite to store and retrieve data rows where each
>row is roughly 2K total in size and in a table of 15 columns. The total
>size of the database ranges from 100-300 MB.
>
>The problem we are seeing is that query and insert
> Empirically speaking, we display our data in a
> scrolling 2
> dimensional grid format. With MS access, this grid
> responds
> instantaneously when moving through the grid. With
> SQLite, there is
> very noticable stalling and lag and the disk i/o is
> higher than MS
> Access by roughly
Hi Richard,
Here is a tip to speed up the insert operations: use prepared statements
instead of plain INSERTs. If you have to insert more than one record with
the same format the performance increase is significant.
Transactions also speeds up db operations, there are more detailed topics
about t
201 - 239 of 239 matches
Mail list logo