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/P00000001/P00000001-ALL.zip unzip to P00000001-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 "P00000001-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
