SQL Stored Procedure issue due to the Lack of SP Performance

The main problem is that in my sp there are different objects and logic that may cause lack of performance.

The things that I see on my SP and the tables that are in use in the SP 1- Temp tables such as shown below; (2 Temp Tables)

CREATE TABLE #TEMP_TABLE( AB INT NOT NULL, AC INT NOT NULL, AD INT NOT NULL, AF INT NULL, AG INT NULL, REFERENCE_NUMBER INT NULL ) CREATE NONCLUSTERED INDEX IX_1 ON #TEMP_TABLE (AB , AC, AD)

2- Insert process to the create temp table as TEMP1 in the SP

3- DROP TABLE #TEMP_TABLE DROP TABLE #TEMP_TABLE2 in the end of the SP

4- There are two tables used to join themselves, but not all the fields that are used "on" case of JOIN operator. For instance; there are clustered index fields declared on the first table, plus date column which is not in the clustered index groups of the first table..

For short; Clustered and non clustered indexes are important ok, but in which order should i declare them. What i need from which index type, tell me the order of cretation of indexes for further use of JOIN process etc.

5- Should I use table variable instead of temp table. Ok it will be better but there are lots of question mark that does not let me to use the table variables..

*An INSERT into a table variable will not take advantage of parallelism. ref->

6- I heard about logical reads. Should i really take care about these numbers. The details of an example data's results are shown below (*)

7- The execution plan...

*OUTPUT:

Table 'CHANNEL'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'XYZ_DATE'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'XYZ01'. Scan count 3171, logical reads 13135, physical reads 153, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'XYZ02'. Scan count 5, logical reads 51256, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#TEMP_TABLE_________________________________________________________________________________________________________000000000840'. Scan count 0, logical reads 97, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(32 row(s) affected) Table '#TEMP_TABLE2________________________________________________________________________________________________________000000000841'. Scan count 0, logical reads 64, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ''XYZ03'. Scan count 0, logical reads 107, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ''XYZ04''. Scan count 32, logical reads 129, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#TEMP_TABLE_________________________________________________________________________________________________________000000000840'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(21 row(s) affected)

(21 row(s) affected) Table 'XYZ05'. Scan count 0, logical reads 87, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'XYZ01A'. Scan count 21, logical reads 147, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'XYZ04'. Scan count 0, logical reads 84, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical开发者_运维百科 reads 0, lob read-ahead reads 0. Table '#TEMP_TABLE2________________________________________________________________________________________________________000000000841'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(*)

I did put some indexes then scans are doing seek.. now it is better.

still doing performance tests: follow here-> Testing Stored Procedure performance