SQL performance

Hi All,
This is my first post in this forum. I am facing a problem regarding performance of SQL server 2000.
From application (front end as PERL), I am trying to generate reports with selected fields. Because of this kind of functionality, query is becoming huge with so many join statements. Once I run this big query, the entire application slows down.
I tried indexes. In index tuning wizard the performance improvance is coming as 0%.
Kindly help me in any of the following:
1) performane of query should become more that in less time results should appear.
or
2) when the big query is runnning for remaining queries, it should not affect the performance.
Thanks in advance.

Hi Luis Martin,
I have done in the suiggested way following are the results:
Table 'Worktable'. Scan count 15570, logical reads 31139, physical reads 0, read-ahead reads 0.
Table 'project_status'. Scan count 1, logical reads 366, physical reads 0, read-ahead reads 161.
Table 'Worktable'. Scan count 15570, logical reads 31139, physical reads 0, read-ahead reads 0.
Table 'bc_summary'. Scan count 1, logical reads 391, physical reads 0, read-ahead reads 59.
Table 'Worktable'. Scan count 12357, logical reads 24719, physical reads 0, read-ahead reads 0.
Table 'action_log'. Scan count 2, logical reads 8568, physical reads 0, read-ahead reads 1838.
Table 'Worktable'. Scan count 15567, logical reads 31136, physical reads 0, read-ahead reads 0.
Table 'project_update'. Scan count 2, logical reads 7574, physical reads 200, read-ahead reads 7214.
Table 'project_state'. Scan count 7954, logical reads 17675, physical reads 0, read-ahead reads 0.
Table 'project'. Scan count 2, logical reads 9372, physical reads 1044, read-ahead reads 1232.
Table 'milestone'. Scan count 102584, logical reads 205170, physical reads 0, read-ahead reads 0.
Table 'project_milestone'. Scan count 23855, logical reads 75541, physical reads 0, read-ahead reads 0.
Table 'phase'. Scan count 7951, logical reads 7951, physical reads 0, read-ahead reads 0.
Table 'state'. Scan count 7951, logical reads 15902, physical reads 0, read-ahead reads 0.
Table 'work_standard'. Scan count 1, logical reads 317, physical reads 0, read-ahead reads 123.
Table 'opo_user'. Scan count 1, logical reads 929, physical reads 2, read-ahead reads 12.
Table 'project_member'. Scan count 1, logical reads 599, physical reads 0, read-ahead reads 0.
Table 'role'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 3.
Table 'project_custbill_paymnt'. Scan count 1, logical reads 428, physical reads 0, read-ahead reads 420.
Table 'benefit_type'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.
Table 'complete'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.
Table 'itn_category'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 2.
I am not using any NOLOCK in the query
Thanks in advance.

The query which we are going to execute in the application varies based on the user's selection.
IN a worst case scenario, the current query leads to maximum of 67 LEFT OUTER JOIN statements
1) If I run this kind of SQL query inside stored procedure, will it be helping me in any way?
2) Which of the following do you all feel will be a better approach?
a. splitting the queries and using the concept of temporary table or directly running a single query containing JOIN statements in stored procedure.
Thanks in advance.

Seems to be one of the reporting queries with joins depending on user selection. I have few suggestions for you:

• Try to eliminate any function calls / expressions from left hand side of the where clause expression and transfer to right side instead
• Try to create a view for a non-dynamic part of the query
• In where clause, use more selective clause first

You may post the query here for more in-depth analysis and suggestions.

Hi Satya,
Thank you very much for the idea of executing query in stored procedure.
This has reduced the processing time to 1/3rd.
Query which was taking 6 minutes earlies is now taking around 2 minutes. Thank you so much.
Could you please help me to reduce the processing time still more.
Thanks in advance.

Hi,
Using the "with (nolock)" hint in your queries will definitely help as well. Give it a try and see if it improves performance.

Hi Luis Martin,
I have tried executing query using with nolock. I didn't find any difference in the execution time.
Thanks in advance.