Performance Tuning jTDS Hitting MS SQL Server
Today I've spent a lot of the day trying to get the restart time of my web app down to a reasonable time. The problem is that I need to load upwards of two million rows from a back-end MS SQL Server back-end database into an H2 in-memory database on restart using the fastest JDBC driver for MS SQL Server I've heard of - jTDS. The reason for this is access speed of the web app. There's just so much data that needs to be available to the servlets that if I were to get it from the database, I'd have an access time that's 10x what I have now.
So I need to load a lot of rows from the back-end database. In the past, I had a restart time that was about a minute. Not horrible. Today I realized that I'm looking at something more like seven minutes. That's too long.
So I pulled in the network guys to see if they could find anything in the wiring in the server room, or settings on the box, because there were machines where the SELECT statements were executed significantly faster than my box. The question was Why?
To their credit, the network guys did an impressive job of digging into the problem. Really amazing analysis of the problem. Unfortunately, in the end, they didn't have anything that was going to significantly change the performance of the query processing for me. But I wasn't too surprised, either. There had to be things I could do to clean things up, and I had always suspected it was going to be up to me in the end.