2

I am experience a strange performance issue when accessing data from SQL Server from a Spring based application. In my current setup, the Spring java application runs on a separate machine accessing data from a remote SQL Server DB. I am using NamedParameterTemplate in Spring, which I believe uses Prepared Statement to execute the query. For some reason, some of the query takes a long time to complete (approx. 2 mins). The JAVA app runs on a 64bit machine running 64bit version of Java v1.6, and the SQL Server is MS SQL Server 2008 R2.

The strangeness, is if I run the same java app from my laptop running Windows XP 32bit, running the same version of Java v1.6, the query takes less than a second, accessing the exact same remote DB server (infact, I am connected through VPN)

This shows the issue is not with the Spring framework but may be with the SQL JDBC Driver. I am using Microsoft JDBC Driver 4.0 for SQL Server (sqljdbc.jar)

I am completely clueless, as what could possibly be wrong and not sure where to start my debugging process. I understand, there isn't much information in my question, so please let me know if you need any specific detail.

Thanks for any help/suggestions.

user320587
  • 1,347
  • 7
  • 29
  • 57
  • I would start by profiling the application to see where it is spending it's time. – tster Aug 29 '12 at 16:19
  • If two different machines running the same exact code have such wildly different performance, it can point to either some sort of firewall issue between the problemsome machine and the DB, or otherwise the DB might have been under extraordinary load when testing the first. Have you ruled either of these out? – matt b Aug 29 '12 at 16:26
  • @mattb I have ruled out the load part, since we have tried it multiple times and seeing the exact same behavior. Haven't rules anything out on the firewall, but don't have any clue as how firewall wold cause long query execution time. Any ideas would be very helpful. – user320587 Aug 29 '12 at 16:41
  • I'd suggest trying to capture the network traffic with something like wireshark on both machines to see what, if any, the difference is. – matt b Aug 29 '12 at 16:47
  • I have similar issue, I'm deploying my app to a x64 server and a simple request (SELECT + WHERE with prepared St) return no result where it should – TecHunter Nov 01 '13 at 08:42
  • Are you using java 6 update 29? – flup Nov 01 '13 at 11:03

1 Answers1

1

I think this may be due to the combination of your java version and jdbc driver failing to handshake the connection with the server. See Driver.getConnection hangs using SQLServer driver and Java 1.6.0_29 and http://blogs.msdn.com/b/jdbcteam/archive/2012/01/19/patch-available-for-sql-server-and-java-6-update-30.aspx

If so, switching to 1.6.0 upgrade 30 or higher and applying kb 2653857 ought to fix it.

Community
  • 1
  • 1
flup
  • 26,937
  • 7
  • 52
  • 74