浏览 46 次
|
该帖已经被评为新手帖
|
|
|---|---|
| 作者 | 正文 |
|
时间:2008-07-25
对数据库的操作,做了几个简单的测试,就是使用不同的方式,往同一表里面插入1000条数据,然后统计他们各自消耗的时间。
测试准备: 数据库:Sybase 12.5 JDBC 驱动:jconn3 测试表(没有建立索引,免得影响插入):TEST(NAME varchar(20),MEMO varchar(20),ITIME timestamp,UTIME timestamp,ENABLED bit, DELETED bit) 具体的操作方式有(具体代码请看下面): 1. 使用一个连接并创建一个PreparedStatement,然后只使用该ps循环1000次,不断地往表里面插入数据,平均消耗时间大概是2s 2. 使用连接池(DBCP),循环1000次,每次都从连接池中取出一个连接并创建一个PreparedStatement,插入一条数据,之后就关闭ps和conn。其平均消耗时间大概要8s 问题: 1. 为什么使用连接池会慢那么多?而且连接池的poolPreparedStatements参数已经配置成true 2. 究竟PreparedStatement是针对一个连接还是整个数据库的?即这个连接创建的ps,其他连接是不能共用的,只能再进行一次预编译? 具体代码如下: 1. 一个连接内操作
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
public class OneConnectTest
{
private Connection getConnection()
{
try
{
Class.forName("com.sybase.jdbc3.jdbc.SybDriver").newInstance();
String url = "jdbc:sybase:Tds:ip:5000/db";
return DriverManager.getConnection(url, "name", "pwd");
}
catch (ClassNotFoundException e)
{
e.printStackTrace();
}
catch (InstantiationException e)
{
e.printStackTrace();
}
catch (IllegalAccessException e)
{
e.printStackTrace();
}
catch (SQLException e)
{
e.printStackTrace();
}
return null;
}
public void start() throws SQLException
{
Connection conn = getConnection();
String sql = "insert into TEST(NAME,MEMO,ITIME,UTIME,DELETED,ENABLED)values(?,?,?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
long start = System.currentTimeMillis();
for(int i=0;i<1000;i++)
{
ps.clearParameters();
ps.setString(1, "Name"+i);
ps.setString(2, "Memo"+i);
ps.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
ps.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
ps.setBoolean(5, false);
ps.setBoolean(6, true);
ps.executeUpdate();
}
long end = System.currentTimeMillis();
System.out.println("cost:"+(end-start));
ps.close();
conn.close();
}
public static void main(String[] args) throws SQLException
{
OneConnectTest test = new OneConnectTest();
test.start();
}
}
2.使用连接池操作 连接池配置文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">
<bean id="dataSource"
class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName">
<value>com.sybase.jdbc3.jdbc.SybDriver</value>
</property>
<property name="url">
<value>jdbc:sybase:Tds:ip:5000/db</value>
</property>
<property name="username">
<value>name</value>
</property>
<property name="password">
<value>pwd</value>
</property>
<property name="initialSize">
<value>5</value>
</property>
<property name="maxActive">
<value>10</value>
</property>
<property name="maxIdle">
<value>5</value>
</property>
<property name="poolPreparedStatements">
<value>true</value>
</property>
</bean>
</beans>
package mcbp.core.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import javax.sql.DataSource;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
public class DBCPTest
{
public void start() throws SQLException
{
ApplicationContext context = new FileSystemXmlApplicationContext("conf/dbcp.xml");
DataSource dataSource = (DataSource)context.getBean("dataSource");
String sql = "insert into TEST(NAME,MEMO,ITIME,UTIME,DELETED,ENABLED)values(?,?,?,?,?,?)";
Connection conn;
PreparedStatement ps;
long start = System.currentTimeMillis();
for (int i = 0; i < 1000; i++)
{
conn = dataSource.getConnection();
ps = conn.prepareStatement(sql);
ps.clearParameters();
ps.setString(1, "Name" + i);
ps.setString(2, "Memo" + i);
ps.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
ps.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
ps.setBoolean(5, false);
ps.setBoolean(6, true);
ps.executeUpdate();
ps.close();
conn.close();
}
long end = System.currentTimeMillis();
System.out.println("cost:" + (end - start));
}
public static void main(String[] args) throws SQLException
{
DBCPTest test = new DBCPTest();
test.start();
}
}
声明:JavaEye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
|
|
| 返回顶楼 | |


