Link to home
Start Free TrialLog in
Avatar of rpkhare
rpkhareFlag for India

asked on

Optimizing query and table design for performance

I have a long SQL Query in a stored procedure which joins around 10-15 tables. This query is generates a report but it is taking a lot of time to execute. This query is an existing code written by someone in my company long back.

I am not posting query here because it is long and has 15 tables joined.

On scrutinizing I found that many tables don't have Primary Keys and child tables were not related with Foreign Key. To resolve this, I added Primary Key to such tables and in few tables I defined relationship.

I also found that most of the columns used in the WHERE clause of this query were not indexed. So I added indexes on these columns.

Still I am not able to achieve performance.

I want to know which areas I should concentrate to make this query fetch result much faster. Apart from adding Indexes on queryable columns, what things should I search for to fill the loop holes.

I also want to know whether I need to Rebuild/Reorganize Index after adding an index to a table or not.
Avatar of John Gobert
John Gobert
Flag of United States of America image

Have you checked the execution plan for any hot-spots?  Have you run a trace with profiler to see what the metrics look like on the server side?  Are you working with a large data-set or just a lot of joins and sub-queries?  Is your database geared towards data warehousing or OLTP?  Any custom settings in place for parallelism?

How long is the query taking to run right now?  What's your goals or expectations for reduced run time?
ASKER CERTIFIED SOLUTION
Avatar of Wim_Bl
Wim_Bl
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of rshq
rshq
Flag of Iran, Islamic Republic of image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Absolutely no way to tell what needs done without seeing the query.

It could have nothing to do with indexing, particularly if the query has a large GROUP BY or some other performance killer.