sqlite java需要按照,SQLite:java/jdbsqlite和python/sqlite3的区别
我目前正在開發用java(7)實現的桌面應用程序，它管理sql數據庫中的大量數據記錄。只有幾個表，但它們包含大量的記錄。我需要對單個表執行復雜的查詢，但不需要執行復雜的聯接操作。在
到目前為止，我一直在與博士后合作。但由于它是一個桌面單用戶應用程序，我也考慮過使用sqlite(不用說，這也會降低設置的復雜性)。所以我寫了一個簡單的python腳本來做一些性能測試。令我驚訝的是，首先，sqlite的實際執行情況如何；其次，python中的查詢響應時間比java中的要小得多。在
一個常見的場景是根據id列表選擇一批記錄。在python中，我使用以下代碼來測試響應時間：rand_selection = ','.join([str(int(random.random()* MAX_INDEX )) for i in xrange(PAGE_SIZE)])
start = time.time();
c = db.cursor();
res = c.execute("SELECT * FROM bigtable WHERE id in ("+rand_selection+")");
reslist = [str(t) for t in res]; c.close();
print( time.time() - start );
對于MAX_INDEX=111000和PAGE_SIZE=100，這給了我大約5毫秒的增量。在
好吧，太好了。現在，讓我們轉到java：我使用jdbc-sqlite驅動程序。我在完全相同的表上執行了完全相同的查詢，查詢時間總是在200毫秒左右，這對于我的用例來說是不可接受的。在
我錯過了什么嗎？在
我知道這是一個非常普遍的問題。但是也許有人對JDBCSQLite有一定的經驗，并且從經驗中知道發生了什么
[編輯]：使用timit.default_計時器()正如建議的(謝謝，馬蒂恩·皮特斯)給了我相似的結果。在
[Edit2]：根據CL的建議，我編寫了java代碼的簡化版本。使用這段代碼，我可以而不是驗證結果和響應時間與python代碼相同。但是，我在另一臺機器上測試了這一點，使用了不同的jdk(openjdk7與oraclejdk7)。誠然，我的其他測試代碼很可能有一些問題。在
[編輯2013-08-16]：我現在使用原始設置執行相同的測試。我還把它和博士后做了比較。在
^{pr2}$
測試代碼(請原諒草率的編碼…)：package ch.dsd;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class Main {
private static int COL_COUNT = 20;
private static int TESTRUNS = 20;
private static int INDEX_COUNT = 64;
/*
CREATE TABLE bigtable ( id INTEGER PRIMARY KEY ASC, prop0 real, prop1 real, ... , prop19 real );
*/
static class Entity {
private long id;
private ArrayList properties = new ArrayList(COL_COUNT);
public Entity() {
for( int i = 0; i < COL_COUNT; i++) {
properties.add(0.0);
}
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public void setProperty(int idx, double prop) {
properties.set(idx, prop);
}
public double getProperty(int idx) {
return properties.get(idx);
}
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
for( double prop: properties ) {
sb.append(prop);
sb.append(",");
}
sb.delete(sb.length()-1, sb.length());
return sb.toString();
}
}
private static String placeholders( int n ) {
StringBuilder sb = new StringBuilder();
if( n > 0 ) {
sb.append("?");
for( int i = 1; i < n; i++ )
sb.append(",?");
return sb.toString();
}
return "";
}
private static void setRandomIdcs( PreparedStatement ps, int start, int stop, int max ) throws SQLException {
for( int i = start; i <= stop; i++ ) {
ps.setLong(i, (long) ((double) max * Math.random()));
}
}
private static void setRandomValues( PreparedStatement ps, int start, int stop ) throws SQLException {
for( int i = start; i <= stop; i++ ) {
ps.setDouble(i, Math.random());
}
}
private static void readFromResultSet( ResultSet rs, List lst ) throws SQLException {
while(rs.next()) {
final Entity e = new Entity();
e.setId(rs.getLong(1));
for( int i = 0; i < COL_COUNT; i++ )
e.setProperty(i, rs.getDouble(i+2));
lst.add(e);
}
}
public static void performTest(Connection c) throws SQLException {
final PreparedStatement ps = c.prepareStatement("SELECT * FROM bigtable WHERE id in ("+placeholders(INDEX_COUNT)+")");
ArrayList entities = new ArrayList();
for( int i = 0; i < TESTRUNS; i++ ) {
setRandomIdcs( ps, 1, INDEX_COUNT, 1000000 ); // there are one million entries stored in the test table
long start = System.currentTimeMillis();
final ResultSet rs = ps.executeQuery();
readFromResultSet(rs, entities);
// System.out.println(entities.get(INDEX_COUNT-1));
System.out.println("Time used:" + (System.currentTimeMillis() - start));
System.out.println("Items read:" + entities.size());
rs.close();
entities.clear();
}
ps.close();
}
public static void createPSQLTable(Connection c) throws SQLException {
final String create_stmt = "CREATE TABLE IF NOT EXISTS bigtable (id SERIAL PRIMARY KEY, " +
"prop0 double precision,prop1 double precision,prop2 double precision,prop3 double precision,prop4 double precision,prop5 double precision,prop6 double precision,prop7 double precision,prop8 double precision,prop9 double precision,prop10 double precision,prop11 double precision,prop12 double precision,prop13 double precision,prop14 double precision,prop15 double precision,prop16 double precision,prop17 double precision,prop18 double precision,prop19 double precision)";
final PreparedStatement ps = c.prepareStatement(create_stmt);
ps.executeUpdate();
ps.close();
}
public static void loadPSQLTable( Connection c ) throws SQLException {
final String insert_stmt = "INSERT INTO bigtable VALUES (default, " + placeholders(20) + ")";
final PreparedStatement ps = c.prepareStatement(insert_stmt);
for( int i = 0; i < 1000000; i++ ) {
setRandomValues(ps, 1, 20);
ps.executeUpdate();
}
c.commit();
}
public static void main(String[] args) {
Connection c = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:/Users/dsd/tmp/sqlitetest/testdb.db");
c.setAutoCommit(false);
performTest(c);
c.close();
System.out.println("POSTGRES");
System.out.println("========");
final Properties props = new Properties();
props.setProperty("user", "dsd");
c = DriverManager.getConnection("jdbc:postgresql:testdb", props);
c.setAutoCommit(false);
createPSQLTable(c);
// loadPSQLTable(c);
performTest(c);
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
}
}
結果：Time used:348
Items read:64
Time used:407
Items read:64
Time used:259
Items read:64
Time used:341
Items read:64
Time used:325
Items read:64
Time used:145
Items read:64
Time used:70
Items read:64
Time used:98
Items read:64
Time used:91
Items read:64
Time used:134
Items read:64
Time used:68
Items read:64
Time used:51
Items read:64
Time used:51
Items read:64
Time used:51
Items read:64
Time used:55
Items read:64
Time used:67
Items read:64
Time used:56
Items read:64
Time used:90
Items read:64
Time used:56
Items read:64
Time used:51
Items read:64
POSTGRES
========
Time used:75
Items read:64
Time used:58
Items read:64
Time used:31
Items read:64
Time used:26
Items read:64
Time used:34
Items read:64
Time used:6
Items read:64
Time used:5
Items read:64
Time used:4
Items read:64
Time used:5
Items read:64
Time used:6
Items read:64
Time used:5
Items read:64
Time used:6
Items read:64
Time used:4
Items read:64
Time used:28
Items read:64
Time used:3
Items read:64
Time used:4
Items read:64
Time used:4
Items read:64
Time used:4
Items read:64
Time used:3
Items read:64
Time used:5
Items read:64
總結
以上是生活随笔為你收集整理的sqlite java需要按照,SQLite:java/jdbsqlite和python/sqlite3的区别的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： php 怎么打印条形码,php – 如
- 下一篇： zip 密码算法 java,java z