当前位置:首页 > 开发 > 数据库 > 正文

JDBC PreparedStatement的Performance问题,setString 和 varchar/nvarchar的关系

发表于: 2014-10-22   作者:dearls   来源:转载   浏览次数:
摘要: 近期为了解决一些security的问题,把DAO层JDBC的Statement全部替换为PreparedStatement,然后遇到一个非常奇怪的问题是,一个简单的SQL, 比如 SELECT * FROM MDC_DATA_TYPE WHERE name = 'A' AND number = 1; 改成使用SELECT * FROM MDC_DATA_TYPE WHERE name = ?

近期为了解决一些security的问题,把DAO层JDBC的Statement全部替换为PreparedStatement,然后遇到一个非常奇怪的问题是,一个简单的SQL, 比如

SELECT * FROM MDC_DATA_TYPE WHERE name = 'A' AND number = 1;

改成使用SELECT * FROM MDC_DATA_TYPE WHERE name = ? AND number = ?之后,执行时间几乎慢了100倍,这几乎与业界推荐使用PreparedStatement的事实背道而驰。于是花了很多时间来找原因。

 

后来找了曾在微软做SQL Server TCS的同事帮忙看,果然是高手,查看了执行计划 (Execution Plan),这里我留一些备忘:

select spid,status,hostname,program_name,text from sys.sysprocesses p
cross apply sys.dm_exec_sql_text(p.sql_handle) t
where spid>50 and program_name like 'JSQL%'
and program_name like 'JSQL%'
order by spid


select * from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle)
where session_id>50



dbcc freeproccache
dbcc freesystemcache('All')
dbcc dropcleanbuffers
dbcc freeproccache

kill 102

select * from sys.dm_exec_quests

 

// 下面这个是找到找到问题的SQL
select * from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle)
cross apply sys.dm_exec_query_plan(p.plan_handle)

 

可以看到SQLServer缓存的sql:
(@P0 nvarchar(4000),@P1 int,@P2 nvarchar(4000))SELECT * FROM MDC_DATA_TYPE WHERE rmname = @P0 AND numPeriods = @P1 AND periodUnit = @P2 

 

根本原因:

http://www.newatlanta.com/c/support/servletexec/self_help/faq/detail?faqId=183

PreparedStatement.setString() performs more slowly than PreparedStatement.setBytes(). Why?

 

  This can occur when the SQL Server data type of your table's column is different than the type being used in your query.

For example, say you have a table in your SQL Server database with a column of type char or varchar, and the query in your Java code looks like this:
"SELECT * from mytable where mytext = ?"
If you use pstmt.setString(1, "99"); in your Java code, then JTurbo inserts the parameter and builds the query using 'N' like this:
... where mytext = N'99'
This forces SQL Server to do a conversion from nvarchar to the type of your table's column (char or varchar).
A conversion like this is expensive.
If you use pstmt.setBytes(1, "99".getBytes("8859_1")); in your Java code, then JTurbo inserts the parameter and builds the query like this:
... where mytext = 0x3939
which does not require a type conversion within SQL Server.
The only way that JTurbo's PreparedStatement could avoid prepending the 'N' would be if it were to first ask SQL Server whether or not the column type was nchar, nvarchar, or ntext. Asking this for every sql query it builds could be very expensive.

Our recommendation is that you change your database column type to be one of the 'n' types:
  • nchar
  • nvarchar
  • ntext

JDBC PreparedStatement的Performance问题,setString 和 varchar/nvarchar的关系

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
最近遇到一个问题,当查询使用到模糊查询时,由于预估返回行数过高,执行计划认为索引查找+Key Look
很多时候我们在创建数据库时在给字段设置数据类型时会选择varchar或是nvarchar。当然还可以选择别的
很多时候我们在创建数据库时在给字段设置数据类型时会选择varchar或是nvarchar。当然还可以选择别的
开始什么都别说,先来个例子 打开sql server创建一个表: create database testdb go create table
This topic illustrates the best practices to improve performance in JDBC with the following s
之前看JDBC规范的时候对PreparedStatement只是简单的知道会进行sql预编译,能提高性能。具体原理也
  当在页面后台获取数据库表中某字段的数据时,需注意该数据的类型。防止因实际数据的字符长度因
本文纯属个人见解,是对前面学习的总结,如有描述不正确的地方还请高手指正~ 对列存储长度小于列的
为什么要使用PreparedStatement? 一、通过PreparedStatement提升性能 Statement主要用于执行静态SQ
一、什么是PreparedStatement 参阅Java API文档,我们可以知道,PreparedStatement是Statement的子
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号