Query speed up strategies

Question

Hey all

At the company i am working on we have an application build on Jboss/Apache/Hibernate with Ms Sql 2005 db.

We have a page that loads a bunch of transactions. Now we timed this during loading of the page and it takes abnout 15-20 seconds to load the files, thsi is because the queries build (not sure if these are build by hibernate) join a big number of tables . To rectify the issue we changed some left joins to inner joins and add indexes to the tables. however this doesnt really solve the issue, it gets better, but not significantly.

any ideas?

Answer 1

What did you do to determine which indexes to add? I've always had great luck with the MSSQL Index Tuning Wizard - you can use SQL Profiler to trace the database activity during a page load, and then tell the Query Tuning Wizard to suggest new indexes and statistics based on that activity. It will generally suggest a handful of indexes that can make a huge difference.

Are the databases on high-contention disks? Maybe the queries would be faster if the database were on their own physical disks. Given the size of your underlying tables, maybe the database server is under-powered - does it have enough spare resources to handle the file loading?

Answer 2

How many records are being returned by the query?

If there are a lot of records, you may want to do some sort of custom paging and only return the # of records that are on the current page (i.e. page of 50 will only return records 1 - 50)

Answer 3

You can move your read only database instance to its own server, use solid state drives, and adjust your indexes. Another way to optimize this would be to run a query to create a table you can access with a simple query instead of running a bunch of queries in run time.