1

I built an java web application and it works fine with SQL Server 2008 if the size in the queried table is about 100 records. But when I increased it to 1.3 million records, it takes about 4-8 minutes to execute a single query. My application uses hibernate.

I have deployed this application on a 6gb ram server and a 12gb ram server and have also increased my java heap size to 4gb and 8gb respectively but I still encounter the same problem.

Please what can I do to improve performance?

UPDATE:

This is a one of the sql queries that is really slow on SQL Server but runs fast on Postgresql

select distinct c.company from Affiliates c where c.portalUser.userId = 'user.getUserId()' and lower(c.company.classification.name) = lower('" + companyClass + "') order by c.company.dateOfReservation desc";
user3095242
  • 133
  • 1
  • 1
  • 8

2 Answers2

1

I think the most painful part of your example query is the part:

lower(c.company.classification.name) = lower('" + companyClass + "')

Here you are forcing a table scan because for each row the names have to be lowercased and compared. If your database is not configured to compare case you might be able to omit the lower() calls. If not you could consider adding an extra column with a lower case copy of the string and use this copy for the query.

How many companyClasses are there? Could you create a separate table with all the company classes and refer to it by index? This would probably speed up this query a lot since you would not have to do any varchar comparisons anymore.

Just some ideas.

Adriaan Koster
  • 15,870
  • 5
  • 45
  • 60
0

You haven't provided any code or sufficient specific details but the usual causes are:

  1. Emitting millions of ORM queries. Use SQL Profiler (or turn on Hibernate's logging) to determine if you are emitting many selects from code when a single stored procedure to do all the work would be more appropriate. This SO answer shows you how to enable Hibernate SQL logging.

  2. Poorly indexed tables causing lots of large table scans. See How Can I Log and Find the Most Expensive Queries?. Determine your expensive queries and create indexes to improve their performance.

Note: @Adriaan Koster pointed out the lower case conversion performed in your query. By default, SQL Server is case insensitive (I've only come across one that wasn't in 20 years, and that was set up mistakenly), so you can almost certainly drop the conversions to lowercase. This would allow the query to use an appropriate index if one exists.

Community
  • 1
  • 1
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • Thanks @mitch. I had already tried SQL Profiler but emitting many selects from the code. But i wil still test with the article in How Can I Log and Find the Most Expensive Queries? – user3095242 Dec 16 '13 at 12:45
  • Also I pushed the data to postgresql and the application works fine but the client insisted on using SQL Server – user3095242 Dec 16 '13 at 12:46