Re: [sqlite] About "speed"

[big stone]

Hello,

I Re-checked today the  Mysql suspect performance, after a reboot, on :
"select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group
by cand_nm, contbr_st;"

This particular operation is now 7.1 seconds.

I may have miss-used "MysqlWorkbench".

==> I updated the figure to the "reproducible" 7.1seconds.

https://raw.github.com/stonebig/ztest_donotuse/master/benchmark_test01_measures.GIF



Regards,
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[big stone]

Hi

*Elefterios, Simon,*


*Wes McKinney gave us :- a fully detailed benchmark case (data +
reproducible test),*


*- where SQLite was :  . abnormally less good than Postgresql (so could be
better),*


*  . SQLdatabase in general were abnormally less good,   . a hint
"vertica"was given.*


*Maybe it is a 'golden' benchmark, as it hints a possible 10x margin of
improvement for SQLite. *
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[big stone]

ok,

Just updated with 3.8.4beta of  2014-03-05.

I also re-did some previous measures as :
- testing method improved a little,
- I measured more carefully that SQLite has also a sort of caching benefit,
when you run a query twice on windows7.


Regards,
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[Stephan Beal]

On Wed, Mar 5, 2014 at 7:25 PM, Richard Hipp  wrote:

> MySQL does very well on query 8 which is a repeat of query 6.  This might
> be because MySQL implements a query cache.  It remembers the result of each
> query and if that query occurs again, without an intervening INSERT,
> DELETE, or UPDATE on one of the tables used by the query, just echos the
> previous answer.
>

Counterpoint: MySQL's wire protocol does not support concurrent SELECTs to
be running on the same connection, meaning client code has to do a lot more
work to collect all row data in cases where sqlite3 can easily run queries
while stepping over another. In my experience, writing code with their C
API takes at least 3-5 times longer because it's just generally a pain to
work with (it requires a great deal of the user). sqlite3 wins hands-down
on sanity/usability of the C API and, consequently, development speed. That
doesn't generally apply to script bindings (where using MySQL is also
easy), but it does to the native C APIs.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[Richard Hipp]

On Wed, Mar 5, 2014 at 9:29 AM, big stone  wrote:

> Timing updates with Mysql 5.6.16
>

MySQL does very well on query 8 which is a repeat of query 6.  This might
be because MySQL implements a query cache.  It remembers the result of each
query and if that query occurs again, without an intervening INSERT,
DELETE, or UPDATE on one of the tables used by the query, just echos the
previous answer.

I think it would be interesting to do a single simple INSERT, DELETE, or
UPDATE on the FEC table after test 8, then repeat the same query again, to
see if MySQL is able to maintain the spectacular performance of test 8.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[Richard Hipp]

On Wed, Mar 5, 2014 at 9:29 AM, big stone  wrote:

> Timing updates with Mysql 5.6.16
>

I wonder if you could update the timings for the current SQLite 3.8.4 beta?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[big stone]

Timing updates with Mysql 5.6.16

test =
https://raw.github.com/stonebig/ztest_donotuse/master/benchmark_test01.txt

results =
https://github.com/stonebig/ztest_donotuse/blob/master/benchmark_test01_measures.GIF?raw=true
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[big stone]

The result in a .csv format for Mikael,

Sorry I'm very bad in html, I hope someone can re-post it in a nice-looking
html table

Nota :
- Postgresql is not tuned at all, and its figures move a lot between two
measures,
- I couldn't do a Pandas measure because "not enough memory".


"sequence of operations \ time sec(at first
try)","postgresql9.2.3","sqlite3.8.3.0 7200rpm_disk.db","sqlite3.8.3.0
:memory:","sqlite3.8.3.0 7200rpm_disk.db compiled -o2","sqlite3.8.3.0
:memory: compiled -o2"
"loading 5 344 498 records * 18 columns from .csv",78,151,131,137,51
"select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm
",54,22,20,11,10
"select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm
-- a second time",5.1,22,20,11,10
"select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group
by cand_nm, contbr_st",5.6,27,23,16,14
"CREATE INDEX xyzzy2 ON fec(cand_nm, contbr_st,
contb_receipt_amt)",176,43,38,30,25
"select cand_nm, sum(contb_receipt_amt) as total from fec group by
cand_nm,",4.9,3,3,2,2
"select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group
by cand_nm, contbr_st,",5.6,4,4,3,3
"total time (not relevant)",329,272,239,210,115
"relative speed-up (not relevant)",-21%,0%,12%,23%,58%
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[Richard Hipp]

On Sun, Mar 2, 2014 at 1:55 PM, big stone  wrote:

>==> Why such a 'x6' speed-up, as we need to scan the whole table anyway
> ?
>

SQLite implements GROUP BY by sorting on the terms listed in the GROUP BY
clause.  Then as each row comes out, it compares the GROUP BY columns to
the previous row to see if a new "group" needs to be started.  Sorting is
O(NlogN) if you don't have an index.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[Mikael]

big stone,

Can you please compile a chart (in text format is ok) that puts your
numbers from your last mail in relation with the numbers from your email
prior to that, for everyone to get perfectly clear about how the
optimizations you applied now do improve beyond the numbers published in
the original postgresql vs sqlite published here today?

Like for instance, your 151 second result.. that's more than the 21 seconds
number you published in your first email, and so on -

Thanks!



2014-03-02 19:55 GMT+01:00 big stone :

> Hi again,
>
> I tune a little the SQLite experiment :
> - to get rid of the 19th columns message,
> - to measure the previous tests with more precise figures,
> - the effect of the suggested index :
>CREATE INDEX xyzzy2 ON fec(cand_nm, contbr_st, contb_receipt_amt);
> - the effect of using a filesystem database.
>
> results : (time +/-1 seconds, windows timing doesn't show below the second)
> - feeding data :
>. in disk database : 151 seconds
>. in memory database :  131 seconds (25% = 1 cpu used out of 4)
>
>  - creating index CREATE INDEX xyzzy2 ON fec(cand_nm, contbr_st,
> contb_receipt_amt) :
>. in disk database : 43 seconds
>. in memory database :  38 seconds
>
> - select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm
> ;
>   . in disk database : 22 seconds
>   . in memory database :  19 seconds
>   . in disk database with index: 3 seconds
>   . in memory database with index :  3 seconds
>
>
> - select cand_nm, sum(contb_receipt_amt) as total from fec group by
> cand_nm ;
>  . in disk database : 27 seconds
>  . in memory database :  24 seconds
>  . in disk database with index: 4 seconds
>  . in memory database with index :  4 seconds
>
>
> Remarks :
>
> - with an expert index, SQLite is 6 times quicker.
>==> Why such a 'x6' speed-up, as we need to scan the whole table anyway
> ?
>
> - the ":memory:" database is barely quicker than the disk database.
>==> How can a rotating disk (7200rpm) database compete with a pure
> in-memory database ?
>
>
>
> *** ANNEXE 
> script to launch with ":memory:" or with "turlututu.db"  :
> (I measure the file LastWrite time, on windows via powershell to get
> seconds)
>
>
> .header on
> .mod csv
> .separator ","
>
> create table fec(
> CMTE_ID ,CAND_ID ,CAND_NM ,CONTBR_NM ,
> CONTBR_CITY ,CONTBR_ST , CONTBR_ZIP ,
> CONTBR_EMPLOYER ,CONTBR_OCCUPATION ,CONTB_RECEIPT_AMT double
> precision,
> CONTB_RECEIPT_DT ,RECEIPT_DESC ,MEMO_CD ,
> MEMO_TEXT  ,FORM_TP ,FILE_NUM ,
> TRAN_ID ,ELECTION_TP ,USELESS_COLUMN
> );
> .import "P0001-ALL.csv" fec
>
>
> --5 344 498 record read with warning as 19th empty column
> .output fec_test0.csv
> select *  from fec limit 1;
>
> .output stdout
> .output fec_test1.csv
> select count(*) from fec;
>
> .output stdout
> .output fec_test2.csv
>
> select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm;
>
> .output stdout
> .output fec_test3.csv
>
> select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group
> by cand_nm, contbr_st;
>
> .output stdout
>
> -- in memory, with index   -
> CREATE INDEX xyzzy2 ON fec(cand_nm, contbr_st, contb_receipt_amt);
> .output fec_test0c.csv
> select *  from fec limit 1;
>
> .output stdout
>
> .output fec_test1c.csv
> select count(*) from fec;
>
> .output stdout
> .output fec_test2c.csv
>
> select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm;
>
> .output stdout
> .output fec_test3c.csv
>
> select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group
> by cand_nm, contbr_st;
>
> .output stdout
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[big stone]

Hi Mikael,

I'm not expert in rtree virtual table handling, but you may try and post
the result here .
Adding the test of the -o2 compiled SQLite3.8.3.exe (size 801Ko vs 501Ko
for the standard Sqlite, 'size' optimized)

- feeding data :
   . in disk database : 151 seconds
   . in memory database :  131 seconds (25% = 1 cpu used out of 4)
   . in memory database -o2 compilation :  51 seconds (25% = 1 cpu
used out of 4)

 - creating index CREATE INDEX xyzzy2 ON fec(cand_nm, contbr_st,
contb_receipt_amt) :
   . in disk database : 43 seconds
   . in memory database :  38 seconds
   . in memory database -o2 compilation :  25 seconds

- select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm
;
  . in disk database : 22 seconds
  . in memory database :  19 seconds
  . in memory database -o2 compilation :  10 seconds
  . in disk database with index: 3 seconds
  . in memory database with index :  3 seconds
  . in memory database -o2 compilation with index :  2 seconds


- select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm ;
 . in disk database : 27 seconds
 . in memory database :  24 seconds
 . in memory database -o2 compilation  :  14 seconds
 . in disk database with index: 4 seconds
 . in memory database with index :  4 seconds
 . in memory database -o2 compilation with index :  3 seconds

The effect of -o2 is quite significant on these tests.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[big stone]

Hi again,

I tune a little the SQLite experiment :
- to get rid of the 19th columns message,
- to measure the previous tests with more precise figures,
- the effect of the suggested index :
   CREATE INDEX xyzzy2 ON fec(cand_nm, contbr_st, contb_receipt_amt);
- the effect of using a filesystem database.

results : (time +/-1 seconds, windows timing doesn't show below the second)
- feeding data :
   . in disk database : 151 seconds
   . in memory database :  131 seconds (25% = 1 cpu used out of 4)

 - creating index CREATE INDEX xyzzy2 ON fec(cand_nm, contbr_st,
contb_receipt_amt) :
   . in disk database : 43 seconds
   . in memory database :  38 seconds

- select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm
;
  . in disk database : 22 seconds
  . in memory database :  19 seconds
  . in disk database with index: 3 seconds
  . in memory database with index :  3 seconds


- select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm ;
 . in disk database : 27 seconds
 . in memory database :  24 seconds
 . in disk database with index: 4 seconds
 . in memory database with index :  4 seconds


Remarks :

- with an expert index, SQLite is 6 times quicker.
   ==> Why such a 'x6' speed-up, as we need to scan the whole table anyway ?

- the ":memory:" database is barely quicker than the disk database.
   ==> How can a rotating disk (7200rpm) database compete with a pure
in-memory database ?



*** ANNEXE 
script to launch with ":memory:" or with "turlututu.db"  :
(I measure the file LastWrite time, on windows via powershell to get seconds)


.header on
.mod csv
.separator ","

create table fec(
CMTE_ID ,CAND_ID ,CAND_NM ,CONTBR_NM ,
CONTBR_CITY ,CONTBR_ST , CONTBR_ZIP ,
CONTBR_EMPLOYER ,CONTBR_OCCUPATION ,CONTB_RECEIPT_AMT double
precision,
CONTB_RECEIPT_DT ,RECEIPT_DESC ,MEMO_CD ,
MEMO_TEXT  ,FORM_TP ,FILE_NUM ,
TRAN_ID ,ELECTION_TP ,USELESS_COLUMN
);
.import "P0001-ALL.csv" fec


--5 344 498 record read with warning as 19th empty column
.output fec_test0.csv
select *  from fec limit 1;

.output stdout
.output fec_test1.csv
select count(*) from fec;

.output stdout
.output fec_test2.csv

select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm;

.output stdout
.output fec_test3.csv

select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group
by cand_nm, contbr_st;

.output stdout

-- in memory, with index   -
CREATE INDEX xyzzy2 ON fec(cand_nm, contbr_st, contb_receipt_amt);
.output fec_test0c.csv
select *  from fec limit 1;

.output stdout

.output fec_test1c.csv
select count(*) from fec;

.output stdout
.output fec_test2c.csv

select cand_nm, sum(contb_receipt_amt) as total from fec group by cand_nm;

.output stdout
.output fec_test3c.csv

select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group
by cand_nm, contbr_st;

.output stdout
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[Mikael]

big stone,

What are the same results using RTree? (Also feel free to add -O2.)

?

Thanks



2014-03-02 17:25 GMT+01:00 big stone :

> Hi again,
>
> This is what I mean : we should have an updated "speed" page where we could
> objectively measure.
>
> In the mean time, I painfully partially reproduced two of the figures from
> Wes.
>
> Procedure :
>
> download
> ftp://ftp.fec.gov/FEC/Presidential_Map/2012/P0001/P0001-ALL.zip
> unzip to P0001-ALL.csv
>
> This data file is about 965 Mo, 18 columns *  5 344 498 records big.
>
>
> ** Test Preparation **
> - Hardware : pc windows7, 4go ram, cpu intel i3-350m 2.27 Ghz
> - Software :
>   . sqlite-shell-win32-x86-3080300 (sqlite3.8.3)
>   . postgresql 9.3.2.3 64bit
>
> - preparation scripts of sqlite (As there is an added coma at the end of
> each line, The default Sqlite  importation by reading headers will complain
> a little)
> .header on
> .mod csv
> .separator ","
> .import "P0001-ALL.csv" fec
>
> - preparation scripts of postgresql
> create table fec(
> CMTE_ID varchar,CAND_ID varchar,CAND_NM varchar,CONTBR_NM varchar,
> CONTBR_CITY varchar,CONTBR_ST varchar, CONTBR_ZIP varchar,
> CONTBR_EMPLOYER varchar,CONTBR_OCCUPATION varchar,CONTB_RECEIPT_AMT double
> precision,
> CONTB_RECEIPT_DT varchar,RECEIPT_DESC varchar,MEMO_CD varchar,
> MEMO_TEXT  varchar,FORM_TP varchar,FILE_NUM double precision,
> TRAN_ID varchar,ELECTION_TP varchar,USELESS_COLUMN varchar
> );
>
> copy fec from 'C:\\Users\Public\\Documents\\p1all.csv' CSV HEADER; -- load
> in 82 seconds
>
> ** Speed Tests **
> test1 = select cand_nm, sum(contb_receipt_amt) as total from fec group by
> cand_nm;
> ==> SQlite 21 seconds (wes = 72s)
> ==> Postgresql  4.8 seconds stable  (44 seconds first time ?) (wes =4.7)
>
> test2 = select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec
> group by cand_nm, contbr_st;
> select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group
> by cand_nm, contbr_st;
> ==> SQlite 27 seconds
> ==> Postgresql  5.7 seconds   (wes=5.96)
>
> ** Conclusion **
> WesMcKinney "Sqlite/speed.htm" page about SQLite is 3.4 times more awfull
> than what I measure.
> Sqlite3.8.3 is about 4 times slower than Postgresql on this two 'raw' Data
> analysis Tests.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[Richard Hipp]

On Sun, Mar 2, 2014 at 11:25 AM, big stone  wrote:

>
> ** Speed Tests **
> test1 = select cand_nm, sum(contb_receipt_amt) as total from fec group by
> cand_nm;
> ==> SQlite 21 seconds (wes = 72s)
> ==> Postgresql  4.8 seconds stable  (44 seconds first time ?) (wes =4.7)
>
>
My guess is that PG is creating the appropriate index on the first
invocation, which is why the first run on PG takes so much longer.  SQLite
runs without an index in every case.

What are your performance measurements using SQLite when you create an
index appropriate for the query.  An index that will be appropriate for
both the previous and the following query would be:

CREATE INDEX xyzzy ON fec(cand_nm, contbr_st);

Even better would be a covering index:

CREATE INDEX xyzzy2 ON fec(cand_nm, contbr_st, contb_receipt_amt);

What is SQLite's time after it has one or the other of the indices above?


> test2 = select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec
> group by cand_nm, contbr_st;
> select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group
> by cand_nm, contbr_st;
> ==> SQlite 27 seconds
> ==> Postgresql  5.7 seconds   (wes=5.96)
>
> ** Conclusion **
> WesMcKinney "Sqlite/speed.htm" page about SQLite is 3.4 times more awfull
> than what I measure.
> Sqlite3.8.3 is about 4 times slower than Postgresql on this two 'raw' Data
> analysis Tests.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[big stone]

Hi again,

This is what I mean : we should have an updated "speed" page where we could
objectively measure.

In the mean time, I painfully partially reproduced two of the figures from
Wes.

Procedure :

download
ftp://ftp.fec.gov/FEC/Presidential_Map/2012/P0001/P0001-ALL.zip
unzip to P0001-ALL.csv

This data file is about 965 Mo, 18 columns *  5 344 498 records big.


** Test Preparation **
- Hardware : pc windows7, 4go ram, cpu intel i3-350m 2.27 Ghz
- Software :
  . sqlite-shell-win32-x86-3080300 (sqlite3.8.3)
  . postgresql 9.3.2.3 64bit

- preparation scripts of sqlite (As there is an added coma at the end of
each line, The default Sqlite  importation by reading headers will complain
a little)
.header on
.mod csv
.separator ","
.import "P0001-ALL.csv" fec

- preparation scripts of postgresql
create table fec(
CMTE_ID varchar,CAND_ID varchar,CAND_NM varchar,CONTBR_NM varchar,
CONTBR_CITY varchar,CONTBR_ST varchar, CONTBR_ZIP varchar,
CONTBR_EMPLOYER varchar,CONTBR_OCCUPATION varchar,CONTB_RECEIPT_AMT double
precision,
CONTB_RECEIPT_DT varchar,RECEIPT_DESC varchar,MEMO_CD varchar,
MEMO_TEXT  varchar,FORM_TP varchar,FILE_NUM double precision,
TRAN_ID varchar,ELECTION_TP varchar,USELESS_COLUMN varchar
);

copy fec from 'C:\\Users\Public\\Documents\\p1all.csv' CSV HEADER; -- load
in 82 seconds

** Speed Tests **
test1 = select cand_nm, sum(contb_receipt_amt) as total from fec group by
cand_nm;
==> SQlite 21 seconds (wes = 72s)
==> Postgresql  4.8 seconds stable  (44 seconds first time ?) (wes =4.7)

test2 = select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec
group by cand_nm, contbr_st;
select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group
by cand_nm, contbr_st;
==> SQlite 27 seconds
==> Postgresql  5.7 seconds   (wes=5.96)

** Conclusion **
WesMcKinney "Sqlite/speed.htm" page about SQLite is 3.4 times more awfull
than what I measure.
Sqlite3.8.3 is about 4 times slower than Postgresql on this two 'raw' Data
analysis Tests.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[Simon Slavin]

On 2 Mar 2014, at 1:48pm, Elefterios Stamatogiannakis  wrote:

> IMHO, a benchmark like this is useless without any more information. Some 
> questions that i would like to see answered:
> 
> - Which SQLite and Postgres versions were used?
> - Are the SQLite indexes, covering ones?
> - Have any performance pragmas being used?

Does Postgres have enough memory assigned that it's caching the entire database 
in memory ?
What journal mode is SQLite running in ?
What page sizes are both systems using ?
How many processors does the computer have (i.e. is the Postgres server process 
using the same process as the app) ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About "speed"

[Elefterios Stamatogiannakis]

IMHO, a benchmark like this is useless without any more information. 
Some questions that i would like to see answered:


 - Which SQLite and Postgres versions were used?
 - Are the SQLite indexes, covering ones?
 - Have any performance pragmas being used?

Also interval joins ("between") are hard for SQLite's default indexes, 
but converting them to use a multidimensional index (R-Trees) speeds 
them up to similar speeds as Postgres.


estama

On 2/3/2014 3:02 μμ, big stone wrote:

Hello,

This morning I saw  Pandas/Wes McKinney communicating figures :
  - starting at 37'37" of http://vimeo.com/79562736,
  - leaking a slide where SQLite "is" 15 times slower than Postgresql.

==> the dataset is public :
http://www.fec.gov/disclosurep/PDownload.do?candId=P0001=2012=All%20Candidates=pNational
==> the sql are basic.

Wouldn't it be nice to update the "speed.html" page to have an objective
vision ?

Rationals :
- better show progress (it's hidden in
http://www.sqlite.org/checklists/3080300/index),
- better show non-time metrics : memory, electricity ,i/o...
- better show options effect : ":memory:" , "compile -o2", ...
- better show SQLite position in the SQL landscape.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] About "speed"

[big stone]

Hello,

This morning I saw  Pandas/Wes McKinney communicating figures :
 - starting at 37'37" of http://vimeo.com/79562736,
 - leaking a slide where SQLite "is" 15 times slower than Postgresql.

==> the dataset is public :
http://www.fec.gov/disclosurep/PDownload.do?candId=P0001=2012=All%20Candidates=pNational
==> the sql are basic.

Wouldn't it be nice to update the "speed.html" page to have an objective
vision ?

Rationals :
- better show progress (it's hidden in
http://www.sqlite.org/checklists/3080300/index),
- better show non-time metrics : memory, electricity ,i/o...
- better show options effect : ":memory:" , "compile -o2", ...
- better show SQLite position in the SQL landscape.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users