一个线上系统中发现,有一个根据部门查询此部门以及下级部门所有人员的操作,反应比较慢,一开始怀疑是sql的问题,后来询问了公司其他比较熟悉sql的同事,没什么发现太大的问题

log4jdbc输入日志如下:

select count(1) from base_employee where department_id in ( select id from base_department
where department_code like( select department_code+'%' from base_department where id = '5133e85a4ecfab00fc7525a9')
and company_number ='0001' and state= 0)
{executed in 335 msec}

这么一条sql竟然要335ms,于是我拿到数据库执行,发现只需要几十毫秒.那么毫无疑问,不是数据库的问题,就在测试的时候发现,我把这条sql直接执行(拼好参数的),执行时间降到了30ms,怎么回事呢,因为我们的数据库处理都是用的PreparedStatement,直接执行的时候也是PreparedStatement但是没有设置参数这个步骤了.OK,问题定位了.Google一下

发现了下面这个链接:

http://stackoverflow.com/questions/8948872/jpa-hibernate-native-query-for-prepared-statement-slow

答案在最后一个回答:

I'll leave this question and answer out here in case anyone has the same issue in the future

The issue is in the way the JTDS drivers send the parameter strings to MSSQL. Apparently Java will attempt to send the parameters Unicode by default, and MSSQL will translate it to Ascii. Why that takes 9 seconds, I do not know

Lot's of references to this out there, but nothing that helped my till I was able to isolate that it was an issue with the driver to MSSQL connection

This link was helpful

[http://server.pramati.com/blog/2010/06/02/perfissues-jdbcdrivers-mssqlserver/

This is the string using the Microsoft driver

jdbc:sqlserver://localhost\SQLEXPRESS;
  DatabaseName=TESTDB;
  sendStringParametersAsUnicode=fals

You just need to get the sendStringParametersAsUnicode=false passed to your driver URL setup and you are good.

大体的意思是预编译的参数传到数据库里面默认是转换成unicode的。就是这个过程耗费了比较长的时间.简单的解决方法就是在jdbc url后面加上sendStringParametersAsUnicode=false

微软官方文档中的描述:
sendStringParametersAsUnicode
布尔型
"true"|"false" true
设置为“false”,将指定作为 ASCII 而不是 Unicode 来发送字符数据的预定义参数。该参数可提高对非 Unicode、SQL Server 2000 或 SQL Server 2005 表进行字符数据索引查找时的性能。例如,ASCII 行的键可直接进行比较,无需从 Unicode 进行转换。有关详细信息,请参阅 Microsoft 讨论,网址为support.microsoft.com/kb/271566(英文)。

ps.不设置这个参数有可能会引发乱码问题,但是在sqlserver2008上我们还没有发现这个问题,之前有人出现过.http://hudeyong926.iteye.com/blog/1050248

参考:

http://msdn.microsoft.com/zh-cn/library/ms378988(v=sql.90).aspx

http://yunlongzheng.blog.51cto.com/788996/562439

http://nesta2001zhang.iteye.com/blog/1143289

Comments
Write a Comment