论坛首页 入门讨论版 企业应用

怎么使用数据库连接池会慢那么多?

浏览 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推荐