当查询包含不同的 order by 和内部联接时，如何避免 Union All 中的 TempTable? [英] How To Avoid TempTable in Union All when queries contain DIFFERENT order by and inner join?
问题描述
我想要做的是始终将数量为 0 的产品发送到已经排序的临时表的末尾而不会丢失当前排序(正如我在以下问题中所述如何将零数量产品发送到 PagedList
我有一个 Sorted 已填充的临时表(按用户选择的内容排序，如 Alphabetic 、价格或较新的产品，排序基于身份 ID):
创建表#DisplayOrderTmp([Id] int IDENTITY (1, 1) NOT NULL,[ProductId] int 非空)排序 #DisplayOrderTmp :+------------+--------------+|身份证 |产品编号 |+------------+--------------+|1 |66873 |//数量为0|2 |70735 |//数量不为0|3 |17121 |//数量不为0|4 |48512 |//数量不为0|5 |51213 |//数量为0+------------+--------------+我想将此数据传递到网页，但在此之前我需要将数量为零的产品发送到此列表的末尾不丢失当前排序依据)
我返回的数据应该是这样的(排序不会改变，只是 0 数量的产品按照他们的顺序排在列表的末尾):
创建表#DisplayOrderTmp4([Id] int IDENTITY (1, 1) NOT NULL,[ProductId] int 非空)+------------+--------------+|身份证 |产品编号 |+------------+--------------+|1 |70735 ||2 |17121 ||3 |48512 ||4 |66873 ||5 |51213 |+------------+--------------+P.S:它是我的产品表，我必须将它与 tmptable 进行内部连接才能找到产品数量.
产品表是这样的:+------------+------------------------------+------------+|身份证 |库存数量 |禁用购买按钮 |+------------+------------------------------+------------+|17121 |1 |0 ||48512 |27 |0 ||51213 |0 |1 ||66873 |0 |1 ||70735 |11 |0 |+------------+------------------------------+------------+到目前为止我尝试过的是:(它工作有延迟并且有性能问题，我几乎有 30k 产品)
INSERT INTO #DisplayOrderTmp2 ([ProductId])选择 p2.ProductIdFROM #DisplayOrderTmp p2 with (NOLOCK)//已经排序的表INNER JOIN Product prd with (NOLOCK)ON p2.ProductId=prd.Id和 prd.DisableBuyButton=0//查找数量大于 0 的产品group by p2.ProductId order by min(p2.Id)//保存当前排序插入 #DisplayOrderTmp3 ([ProductId])选择 p2.ProductIdFROM #DisplayOrderTmp p2 with (NOLOCK)//已经排序的表INNER JOIN Product prd with (NOLOCK)ON p2.ProductId=prd.Id和 prd.DisableBuyButton=1//查找数量等于 0 的产品group by p2.ProductId order by min(p2.Id)//保存当前排序INSERT INTO #DisplayOrderTmp4 ([ProductId])//最后联合所有这两个数据选择 p2.ProductId 来自#DisplayOrderTmp2 p2 with (NOLOCK)//超过 0 件产品已保存订单联合所有选择 p2.ProductId 来自#DisplayOrderTmp3 p2 with (NOLOCK)//0 数量产品，已保存订单<块引用>
有什么办法可以避免在此查询中创建 TempTable?发送 0数量产品的第一个诱惑到数据列表的末尾没有创建另外三个 tempTable ，而不会丢失基于 Identity ID 的当前排序.我的查询有性能问题.
我不得不再说一次，临时表有一个标识插入 ID 列，它是基于用户传递给存储过程的排序类型.谢谢大家:)
确保临时表具有索引或主键，Id 作为前导列.这将有助于避免排序计划中的排序运算符:
创建表#DisplayOrderTmp([Id] int NOT NULL,[ProductId] int 非空，主键集群(ID));使用该索引，假设 ProductID 是 Product 表主键，您应该能够使用 UNION ALL 查询以合理的效率在没有额外临时表的情况下获得结果:
WITH 产品 AS (SELECT p2.Id, p2.ProductId, prd.stockqty, 1 AS seq从#DisplayOrderTmp p2加入产品研发ON p2.ProductId=prd.Id哪里 prd.stockqty >0联合所有SELECT p2.Id, p2.ProductId, prd.stockqty, 2 AS seq从#DisplayOrderTmp p2加入产品研发ON p2.ProductId=prd.Id哪里 prd.stockqty = 0)选择产品 ID来自产品按顺序排序，ID；您在评论中提到您最终想要一个分页结果.这可以在 T-SQL 中通过将 OFFSET 和 FETCH 添加到 ORDER BY 子句中来完成，如下所示.但是，请注意，对大型结果集进行分页会随着查询结果的深入而逐渐变慢.
WITH 产品 AS (SELECT p2.Id, p2.ProductId, prd.stockqty, 1 AS seq从#DisplayOrderTmp p2加入产品研发ON p2.ProductId=prd.Id哪里 prd.stockqty >0联合所有SELECT p2.Id, p2.ProductId, prd.stockqty, 2 AS seq从#DisplayOrderTmp p2加入产品研发ON p2.ProductId=prd.Id哪里 prd.stockqty = 0)选择产品 ID来自产品按顺序排序，ID偏移@PageSize * (@PageNumber - 1) ROWS仅获取下一个@PageSize 行；What i am trying to do is always sending Product with 0 quantity to the end of an already sorted temp Table without losing current sorting (as i described in the following question How to send Zero Qty Products to the end of a PagedList<Products>?)
I have one Sorted temptable which is filled (it is sorted by what user has selected like Alphabetic , by Price or by Newer product,sorting is based identity id) :
CREATE TABLE #DisplayOrderTmp
(
[Id] int IDENTITY (1, 1) NOT NULL,
[ProductId] int NOT NULL
)
sorted #DisplayOrderTmp :
+------------+---------------+
| id | ProductId |
+------------+---------------+
| 1 | 66873 | // Qty is 0
| 2 | 70735 | // Qty is not 0
| 3 | 17121 | // Qty is not 0
| 4 | 48512 | // Qty is not 0
| 5 | 51213 | // Qty is 0
+------------+---------------+
I want pass this data to web-page, but before it i need to send product with zero quantity to the end of this list without loosing current Sorting by)
My returned data should be like this (sorting doesn't changed just 0 quantity products went to the end of list by their order):
CREATE TABLE #DisplayOrderTmp4
(
[Id] int IDENTITY (1, 1) NOT NULL,
[ProductId] int NOT NULL
)
+------------+---------------+
| id | ProductId |
+------------+---------------+
| 1 | 70735 |
| 2 | 17121 |
| 3 | 48512 |
| 4 | 66873 |
| 5 | 51213 |
+------------+---------------+
P.S: Its My product Table which i have to inner join with tmptable to find qty of products.
Product Table is like this :
+------------+---------------+------------------+
| id | stockqty | DisableBuyButton |
+------------+---------------+------------------+
| 17121 | 1 | 0 |
| 48512 | 27 | 0 |
| 51213 | 0 | 1 |
| 66873 | 0 | 1 |
| 70735 | 11 | 0 |
+------------+---------------+------------------+
What i have tried so far is this : (it works with delay and has performance issue i almost have 30k products)
INSERT INTO #DisplayOrderTmp2 ([ProductId])
SELECT p2.ProductId
FROM #DisplayOrderTmp p2 with (NOLOCK) // it's already sorted table
INNER JOIN Product prd with (NOLOCK)
ON p2.ProductId=prd.Id
and prd.DisableBuyButton=0 // to find product with qty more than 0
group by p2.ProductId order by min(p2.Id) // to save current ordering
INSERT INTO #DisplayOrderTmp3 ([ProductId])
SELECT p2.ProductId
FROM #DisplayOrderTmp p2 with (NOLOCK) //it's already sorted table
INNER JOIN Product prd with (NOLOCK)
ON p2.ProductId=prd.Id
and prd.DisableBuyButton=1 // to find product with qty equal to 0
group by p2.ProductId order by min(p2.Id) // to save current ordering
INSERT INTO #DisplayOrderTmp4 ([ProductId]) // finally Union All this two data
SELECT p2.ProductId FROM
#DisplayOrderTmp2 p2 with (NOLOCK) // More than 0 qty products with saved ordering
UNION ALL
SELECT p2.ProductId FROM
#DisplayOrderTmp3 p2 with (NOLOCK) // 0 qty products with saved ordering
Is there any way To Avoid creating TempTable in this query? send 0 quantity products of first temptable to the end of data-list without creating three other tempTable , without loosing current ordering based by Identity ID. My query has performance problem.
I have to say again that the temptable has a identity insert ID column and it is sorted based sorting type which user passed to Stored-Procedure. Thank You All :)
Make sure the temp table has an index or primary key with Id as the leading column. This will help avoid sort operators in the plan for the ordering:
CREATE TABLE #DisplayOrderTmp
(
[Id] int NOT NULL,
[ProductId] int NOT NULL
,PRIMARY KEY CLUSTERED(Id)
);
With that index, you should be able to get the result without additional temp tables with reasonable efficiency using a UNION ALL query, assuming ProductID is the Product table primary key:
WITH products AS (
SELECT p2.Id, p2.ProductId, prd.stockqty, 1 AS seq
FROM #DisplayOrderTmp p2
JOIN Product prd
ON p2.ProductId=prd.Id
WHERE prd.stockqty > 0
UNION ALL
SELECT p2.Id, p2.ProductId, prd.stockqty, 2 AS seq
FROM #DisplayOrderTmp p2
JOIN Product prd
ON p2.ProductId=prd.Id
WHERE prd.stockqty = 0
)
SELECT ProductId
FROM products
ORDER BY seq, Id;
You mentioned in comments that you ultimately want a paginated result. This can be done in T-SQL by adding OFFSET and FETCH to the ORDER BY clause as below. However, be aware that pagination over a large result set will become progressively slower the further into the result one queries.
WITH products AS (
SELECT p2.Id, p2.ProductId, prd.stockqty, 1 AS seq
FROM #DisplayOrderTmp p2
JOIN Product prd
ON p2.ProductId=prd.Id
WHERE prd.stockqty > 0
UNION ALL
SELECT p2.Id, p2.ProductId, prd.stockqty, 2 AS seq
FROM #DisplayOrderTmp p2
JOIN Product prd
ON p2.ProductId=prd.Id
WHERE prd.stockqty = 0
)
SELECT ProductId
FROM products
ORDER BY seq, Id
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY;
这篇关于当查询包含不同的 order by 和内部联接时，如何避免 Union All 中的 TempTable?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
