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.
(*)