由于 SP 性能不足导致的 SQL 存储过程问题

发布于 2024-08-21 18:05:39 字数 2797 浏览 6 评论 0原文

主要问题是在我的 sp 中存在不同的对象和逻辑，可能会导致性能不足。

我在 SP 上看到的内容以及 SP 中使用的表 1- 临时表如下所示； (2 个临时表)

CREATE TABLE #TEMP_TABLE( AB INT 不为空， AC INT 不为空， AD INT 不为空， AF INT 空， AG INT 空， REFERENCE_NUMBER INT 空） CREATE NONCLUSTERED INDEX IX_1 ON #TEMP_TABLE (AB, AC, AD)

2- 将进程插入到 SP 中作为 TEMP1 创建临时表

3- DROP TABLE #TEMP_TABLE 删除表#TEMP_TABLE2 在 SP 4 的末尾 -

有两个表用于连接自身，但并非所有字段都在 JOIN 运算符的“on”情况下使用。例如;第一个表上声明了聚集索引字段，加上日期列不在第一个表的聚集索引组中。

简而言之；聚集索引和非聚集索引都很重要，但是我应该按什么顺序声明它们。我需要哪种索引类型，告诉我索引的创建顺序，以便进一步使用 JOIN 过程等。

5-我应该使用表变量而不是临时表。好吧，这会更好，但是有很多问号不允许我使用表变量。

*插入表变量不会利用并行性。 ref->

6-我听说过逻辑读取。我真的应该关心这些数字吗？示例数据结果的详细信息如下所示 (*)

7- 执行计划...

*输出：

表“通道”。扫描计数 0，逻辑读取 2，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0、lob 预读读取 0.表“XYZ_DATE”。扫描计数 1，逻辑读取 2，物理读取 0，预读读取 0，lob 逻辑读取 0, lob 物理读取 0, lob 预读读取 0。表 “XYZ01”。扫描计数3171，逻辑读13135，物理读 153，预读读取0，lob逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表 “XYZ02”。扫描计数 5、逻辑读51256、物理读 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。表 '#TEMP_TABLE_______________________________________________________________________________________________________________000000000840'。扫描计数0，逻辑读取97，物理读取 0、预读读取 0、 lob 逻辑读取 0，lob 物理读取读取 0，lob 预读读取 0。表 “工作台”。扫描计数 0，逻辑读取 0、物理读取 0、预读读取 0，lob 逻辑读取 0，lob 物理读取 0、lob 预读读取 0.
（受影响的 32 行）表 '#TEMP_TABLE2_________________________________________________________________________________________________________000000000841'。扫描计数0，逻辑读取64，物理读取 0、预读读取 0、 lob 逻辑读取 0，lob 物理读取读取 0，lob 预读读取 0。表 ''XYZ03'。扫描计数0，逻辑读取 107，物理读取 0，预读读取 0，lob 逻辑读取 0, lob 物理读取 0, lob 预读读取 0。表“XYZ04”。扫描计数 32，逻辑读取 129，物理读取 0、预读读取 0、 lob 逻辑读取 0，lob 物理读取读取 0，lob 预读读取 0。表 '#TEMP_TABLE_______________________________________________________________________________________________________________000000000840'。扫描计数1，逻辑读取1，物理读取 0、预读读取 0、 lob 逻辑读取 0，lob 物理读取读取 0，lob 预读读取 0。
（受影响的 21 行）
（受影响的 21 行）表 “XYZ05”。扫描计数0，逻辑读取 87，物理读取 0，预读读取 0，lob 逻辑读取 0, lob 物理读取 0, lob 预读读取 0。表 “XYZ01A”。扫描计数 21，逻辑读取 147，物理读取 0，预读读取 0，lob 逻辑读取 0, lob 物理读取 0, lob 预读读取 0。表“XYZ04”。扫描计数0，逻辑读取84，物理读取 0、预读读取 0、 lob 逻辑读取 0，lob 物理读取读取 0，lob 预读读取 0。表 '#TEMP_TABLE2_________________________________________________________________________________________________________000000000841'。扫描计数1，逻辑读取1，物理读取 0、预读读取 0、 lob 逻辑读取 0，lob 物理读取读取 0，lob 预读读取 0。

(*)

原文

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.