SQL为内联选择语句选择查询优化 [英] SQL Select Query optimization for Inline select statements
问题描述
我在这里用一些例子来定义我的情况：
< pre lang =sql> DECLARE @ TB1 TABLE （PK_ID1 INT ，NAME NVARCHAR （ 50 ））
INSERT INTO @ TB1
VALUES （ 1 ，' ABC'）
，（ 2 ，' DEF'）
，（ 3 ，< span class =code-string>' GHI'）
，（ 4 ，' JKL'）
，（ 5 ，' MNO'）
SELECT * FROM @ TB1
DECLARE @ TB2 TABLE （PK_ID2 INT ，PROJECT NVARCHAR （ 10 ），MANAGER_ID INT ，LEADER_ID INT ，USER1_ID INT ，USER2_ID INT ）
INSERT INTO @ TB2
VALUES （ 1 ， ' Proj-1'， 1 ， 4 ， NULL ， NULL ）
，（ 2 ，' Proj-2'， 3 ， NULL ， NULL ， NULL ）
，（ 3 ， ' Proj-3'， 3 ， NULL ， 4 ， 1 ）
，（ 4 ，' Proj-4'， 2 ， 1 ， 3 ， 5 ）
SELECT * FROM @ TB2
SELECT PK_ID2
，PROJECT
，MANAGER_ID
，（ SELECT TOP 1 名称 FROM @ TB1 WHERE PK_ID1 = MANAGER_ID）MANAGER_NAME
，LEADER_ID
，（ SELECT TOP 1 名称 FROM @ TB1 WHERE PK_ID1 = LEADER_ID）LEADER_NAME
，USER1_ID
，（ SELECT TOP 1 名称 FROM @ TB1 WHERE PK_ID1 = USER1_ID）USER1_NAME
，USER2_ID
，（ SELECT TOP 1 名称 FROM @ TB1 WHERE PK_ID1 = USER2_ID）USER2_NAME
FROM @ TB2
有没有办法不为每条记录使用select top 1，因为这会在DB上产生很多负载，因为我的数据库每张表中会有数千条记录。
加入是一个选项，但我的where子句已有12个不同的左连接。
不幸的是，表结构无法更改。
请建议。
谢谢&此致，
Abhishek Kumar
如果每个子选择只能返回1行，则不需要Top 1。既然你已经命名了字段PK_ID1，我认为它是一个主键吗？
我假设你将查询真实的表而不是临时表例。如果这不正确（即，您正在查询本练习的临时表）并且表很大，则在临时表中添加主键定义将改善选择性能，如下所示：
DECLARE @ TB1 TABLE（PK_ID1 INT PRIMARY KEY，NAME NVARCHAR（50））
让我们从备注开始：< br $>
1）的坏表设计@ TB2！
2）错误的SELECT语句！
看看例子：
DECLARE @ TB1 表（PK_ID1 INT IDENTITY （ 1 ， 1 ），NAME NVARCHAR （ 50 ））
INSERT INTO @ TB1 （NAME）
VALUES （' ABC'），（' DEF'），（ ' GHI'），（' < span class =code-string> JKL'），（' MNO'）
DECLARE @ TB2 TABLE （PK_ID2 INT IDENTITY （ 1 ， 1 ），PROJECT NVARCHAR （ 10 ），RoleName VARCHAR （ 30 ），USRID INT ）
INSERT INTO @ TB2 （PROJECT，RoleName，USRID）
VALUES （' proj-1'，' 经理'， 1 ），
（' proj-1'，' Leader'， 4 ），
（'' proj-2'，' Manager'， 3 ），
（' proj -3'，' 经理'， 3 ），
（' proj-3'，' 用户'， 4 ），
（' proj-3'，' 用户'， 1 ），
（' proj-4'，' 经理'， 2 ），
（ ' proj-4'，' 领导者'， 1 ），
（' proj-4'，' 用户'， 1 ），
（' proj-4'， ' 用户'， 1 ）
< span class =code-keyword> SELECT t2.PK_ID2，t2.PROJECT，t2.USRID，t1.NAME，t2.RoleName
FROM @ TB2 AS t2 LEFT < span class =code-keyword> JOIN @ TB1 AS t1 ON t2.USRID = t1.PK_ID1
结果：
1 proj-1 1 ABC经理
2 proj-1 4 JKL领导人
3 proj-2 3 GHI经理
4 proj-3 3 GHI经理
5 proj-3 4 JKL用户
6 proj-3 1 ABC用户
7 proj-4 2 DEF经理
8 proj-4 1 ABC领导者
9项目4 1 ABC用户
10 proj-4 1 ABC用户
如果要显示所有角色，则需要定义数据透视表：
SELECT 项目，[经理]，[领导者]，[用户]
FROM （
SELECT t2.PROJECT，t1.NAME，t2.RoleName
FROM @ TB2 AS t2 LEFT JOIN @ TB1 AS t1 ON t2.USRID = t1.PK_ID1
） AS DT
PIVOT（MAX（名称） FOR RoleName IN （[Manager]，[Leader]，[用户]）） AS PT
结果：
proj-1 ABC JKL NULL
proj-2 GHI NULL NULL
proj-3 GHI NULL JKL
proj-4 DEF ABC ABC
你看到了区别吗？
最后，我强烈建议你阅读关系数据库 [ ^ ]，加入 [ ^ ]等，如： SQL连接的可视化表示 [ ^ ]
记住：查询优化是不可能的没有数据库优化（结构，表格定义等）。
请阅读对Solution1的评论。
我试图创建查询使用公用表格表达式 [ ^ ]，但是......（阅读下面代码中的注释）：
; WITH 经理 AS
（
< span class =code-keyword> SELECT t2.PK_ID2，t2.PROJECT，t2.MANAGER_ID，T1.NAME AS MANAGER_NAME，t2.LEADER_ID， t2.USER1_ID，t2.USER2_ID
FROM @ TB2 AS t2 LEFT JOIN @ TB1 AS t1 ON t2.MANAGER_ID = t1.PK_ID1
），
领导者 AS
（
SELECT t2。*，t1.NAME AS LEADER_NAME
FROM 经理< span class =code-keyword> AS t2 LEFT JOIN @ TB1 AS t1 ON t2.LEADER_ID = t1.PK_ID1
），
UsersOne AS
（
SELECT t2 。*，t1.NAME AS USER1_NAME
FROM 领导 AS t2 LEFT JOIN @ TB1 AS t1 ON t2.USER1_ID = t1.PK_ID1
），
UsersTwo AS
（
SELECT t2。*，t1.NAME AS USER2_NAME
FROM UsersOne AS t2 LEFT JOIN @ TB1 AS t1 ON t2.USER2_ID = t1。 PK_ID1
）
SELECT PK_ID2，PROJECT，MANAGER_ID，MANAGER_NAME，LEADER_ID，LEADER_NAME，USER1_ID，USER1_NAME，USER2_ID，USER2_NAME
FROM UsersTwo
注意：我对性能感到害怕。我无法保证此解决方案比多SELECT语句更快。
有关CTE的更多信息，请参阅：
使用公用表格表达式 [ ^ ]
公用表格式 [ ^ ]
使用公用表表达式的递归查询 [ ^ ]
[/编辑]
Hi,
I'm defining my situation here with creating some example:
DECLARE @TB1 TABLE (PK_ID1 INT, NAME NVARCHAR(50))
INSERT INTO @TB1
VALUES (1, 'ABC')
,(2, 'DEF')
,(3, 'GHI')
,(4, 'JKL')
,(5, 'MNO')
SELECT * FROM @TB1
DECLARE @TB2 TABLE (PK_ID2 INT, PROJECT NVARCHAR(10), MANAGER_ID INT, LEADER_ID INT, USER1_ID INT, USER2_ID INT)
INSERT INTO @TB2
VALUES (1, 'Proj-1', 1, 4, NULL, NULL)
,(2, 'Proj-2', 3, NULL, NULL, NULL)
,(3, 'Proj-3', 3, NULL, 4, 1)
,(4, 'Proj-4', 2, 1, 3, 5)
SELECT * FROM @TB2
SELECT PK_ID2
, PROJECT
, MANAGER_ID
, (SELECT TOP 1 NAME FROM @TB1 WHERE PK_ID1 = MANAGER_ID) MANAGER_NAME
, LEADER_ID
, (SELECT TOP 1 NAME FROM @TB1 WHERE PK_ID1 = LEADER_ID) LEADER_NAME
, USER1_ID
, (SELECT TOP 1 NAME FROM @TB1 WHERE PK_ID1 = USER1_ID) USER1_NAME
, USER2_ID
, (SELECT TOP 1 NAME FROM @TB1 WHERE PK_ID1 = USER2_ID) USER2_NAME
FROM @TB2
Is there any way not to use "select top 1" for every record since this will create a lot of load on DB as my database will have thousands of records in each table.
Joining is an option but my where clause already have 12 different left joins.
Unfortunately the table structure can't be changed.
Kindly suggest.
Thanks & Regards,
Abhishek KumarYou don't need Top 1 if each sub-select can only return 1 row. Since you have named the field PK_ID1, I assume that it serves as a primary key?
I assume that you will be querying real tables rather than the temporary ones in the example. If this is not correct (i.e., you are querying temporary tables for this exercise) and the table is large adding a primary key definition to your temporary table will improve select performance like so:
DECLARE @TB1 TABLE (PK_ID1 INT PRIMARY KEY, NAME NVARCHAR(50))
Let's start from remarks:
1) bad table design for@TB2!
2) bad SELECT statement!
Have a look at example:
DECLARE @TB1 TABLE (PK_ID1 INT IDENTITY(1,1), NAME NVARCHAR(50)) INSERT INTO @TB1 (NAME) VALUES ('ABC'),('DEF'), ('GHI'),('JKL'), ('MNO') DECLARE @TB2 TABLE (PK_ID2 INT IDENTITY(1,1), PROJECT NVARCHAR(10), RoleName VARCHAR(30), USRID INT) INSERT INTO @TB2 (PROJECT , RoleName , USRID ) VALUES('proj-1', 'Manager', 1), ('proj-1', 'Leader', 4), ('proj-2', 'Manager', 3), ('proj-3', 'Manager', 3), ('proj-3', 'User', 4), ('proj-3', 'User', 1), ('proj-4', 'Manager', 2), ('proj-4', 'Leader', 1), ('proj-4', 'User', 1), ('proj-4', 'User', 1) SELECT t2.PK_ID2, t2.PROJECT, t2.USRID, t1.NAME, t2.RoleName FROM @TB2 AS t2 LEFT JOIN @TB1 AS t1 ON t2.USRID = t1.PK_ID1
Result:
1 proj-1 1 ABC Manager 2 proj-1 4 JKL Leader 3 proj-2 3 GHI Manager 4 proj-3 3 GHI Manager 5 proj-3 4 JKL User 6 proj-3 1 ABC User 7 proj-4 2 DEF Manager 8 proj-4 1 ABC Leader 9 proj-4 1 ABC User 10 proj-4 1 ABC User
If you want to display all roles, you need to define pivot table:
SELECT PROJECT, [Manager], [Leader], [User] FROM ( SELECT t2.PROJECT, t1.NAME, t2.RoleName FROM @TB2 AS t2 LEFT JOIN @TB1 AS t1 ON t2.USRID = t1.PK_ID1 ) AS DT PIVOT(MAX(Name) FOR RoleName IN([Manager], [Leader], [User])) AS PT
Result:
proj-1 ABC JKL NULL proj-2 GHI NULL NULL proj-3 GHI NULL JKL proj-4 DEF ABC ABC
Do you see the difference?
Finally, i strongly recommend to read about relational database[^], joins[^], etc., like: Visual Representation of SQL Joins[^]
Remeber: query optimization is not possible without database optimization(structure, table definition, etc.).
[EDIT]
Please, read the comments to the Solution1.
I tried to create query using Common Table Expressions[^], but... (read note under below code):
;WITH Managers AS ( SELECT t2.PK_ID2, t2.PROJECT, t2.MANAGER_ID, T1.NAME AS MANAGER_NAME, t2.LEADER_ID, t2.USER1_ID, t2.USER2_ID FROM @TB2 AS t2 LEFT JOIN @TB1 AS t1 ON t2.MANAGER_ID = t1.PK_ID1 ), Leaders AS ( SELECT t2.*, t1.NAME AS LEADER_NAME FROM Managers AS t2 LEFT JOIN @TB1 AS t1 ON t2.LEADER_ID = t1.PK_ID1 ), UsersOne AS ( SELECT t2.*, t1.NAME AS USER1_NAME FROM Leaders AS t2 LEFT JOIN @TB1 AS t1 ON t2.USER1_ID = t1.PK_ID1 ), UsersTwo AS ( SELECT t2.*, t1.NAME AS USER2_NAME FROM UsersOne AS t2 LEFT JOIN @TB1 AS t1 ON t2.USER2_ID = t1.PK_ID1 ) SELECT PK_ID2, PROJECT, MANAGER_ID, MANAGER_NAME, LEADER_ID, LEADER_NAME, USER1_ID, USER1_NAME, USER2_ID, USER2_NAME FROM UsersTwo
Note: I'm affraid about performance. I can't guarantee that this solution is faster then multiSELECTstatement.
For further information about CTE, please see:
Using Common Table Expressions[^]
Common Table Expressions[^]
Recursive Queries Using Common Table Expressions[^]
[/EDIT]
这篇关于SQL为内联选择语句选择查询优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
