Query speed up strategies

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?

---

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.

---

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?

---

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)