俩句SQL执行效率比较,那句更好,Cross join 为什么不靠谱?
0
悬赏园豆：10
[待解决问题]
这是我的原始SQL.结果老大说用 Cross join 不靠谱,不明白为啥不靠谱
1 CREATE PROCEDURE [dbo].[SyncWarehouseData] 2 AS 3 BEGIN 4 SET NOCOUNT ON; 5 DECLARE @Table_MaxID int 6 --获取库存表最大ID 7 SELECT @Table_MaxID=MAX(InventoryId)+1 FROM Inventory 8 --同步仓库数据 9 INSERT INTO Inventory 10 ( 11 InventoryId 12 ,StoreId 13 ,ItemType 14 ,ItemId 15 ,ItemCode 16 ,PhysicalStock 17 ,InTransitStock 18 ,VirtualStock 19 ,FrozenStock 20 ,RealFrozenStock 21 ,StockHeightestLimit 22 ,StockLowestLimit 23 ,StockWarningDays 24 ,CostPrice 25 ,RealStockQuantity 26 ,Locations 27 ,WarehouseId 28 )--获取要同步的商品 29 SELECT 30 (ROW_NUMBER() OVER (ORDER BY ItemId ASC)+@Table_MaxID) ROW 31 ,1 32 ,ItemType 33 ,ItemId 34 ,ItemCode 35 ,0 36 ,0 37 ,0 38 ,0 39 ,0 40 ,0 41 ,0 42 ,0 43 ,0 44 ,0 45 ,0 46 ,W.WarehouseId 47 FROM Item i WITH(NOLOCK) 48 CROSS JOIN dbo.Warehouse W WITH(NOLOCK) 这里说是不靠谱.要改成游标循环 49 WHERE i.IsDeleted=0 AND W.IsDeleted=0 AND i.ItemId NOT IN (SELECT ItemId FROM Inventory WHERE WarehouseId= W.WarehouseId AND ItemId=i.ItemId) 50 --取库存表最大值 51 SELECT @Table_MaxID= MAX(InventoryId) FROM Inventory WITH(NOLOCK) 52 --更新TableNumber 最大值 53 UPDATE TableNumber SET Num=@Table_MaxID WHERE TableName='Inventory' 54 END
下面是改为后的ＳＱＬ
1 CREATE PROCEDURE [dbo].[SyncWarehouseData] 2 AS 3 BEGIN 4 SET NOCOUNT ON; 5 DECLARE @Table_MaxID INT,@WarehouseId INT 6 --循环仓库 7 DECLARE Cursor_Warehouse CURSOR 8 FOR (SELECT WarehouseId FROM dbo.Warehouse WITH (NOLOCK) WHERE ISNULL(IsDeleted,0)=0 ) 9 OPEN Cursor_Warehouse; 10 FETCH NEXT FROM Cursor_Warehouse INTO @WarehouseId; 11 WHILE @@FETCH_STATUS = 0 12 BEGIN 13 --没有商品同步,则不插入 14 IF EXISTS (SELECT * FROM Item i WITH(NOLOCK) WHERE i.IsDeleted=0 AND i.ItemId NOT IN (SELECT ItemId FROM Inventory WHERE WarehouseId=@WarehouseId AND ItemId=i.ItemId)) 15 BEGIN 16 --获取库存表最大ID, 17 SELECT @Table_MaxID=Num FROM TableNumber WITH(NOLOCK) WHERE TableName='Inventory' 18 19 --同步仓库数据 20 INSERT INTO Inventory 21 ( 22 InventoryId 23 ,StoreId 24 ,ItemType 25 ,ItemId 26 ,ItemCode 27 ,PhysicalStock 28 ,InTransitStock 29 ,VirtualStock 30 ,FrozenStock 31 ,RealFrozenStock 32 ,StockHeightestLimit 33 ,StockLowestLimit 34 ,StockWarningDays 35 ,CostPrice 36 ,RealStockQuantity 37 ,Locations 38 ,WarehouseId 39 )--获取要同步的商品 40 SELECT 41 (ROW_NUMBER() OVER (ORDER BY ItemId ASC)+@Table_MaxID) ROW 42 ,1 43 ,ItemType 44 ,ItemId 45 ,ItemCode 46 ,0 47 ,0 48 ,0 49 ,0 50 ,0 51 ,0 52 ,0 53 ,0 54 ,0 --成本价格 55 ,0 56 ,0 57 ,@WarehouseId 58 FROM Item i WITH(NOLOCK) 59 WHERE i.IsDeleted=0 AND i.ItemId NOT IN (SELECT ItemId FROM Inventory WHERE WarehouseId=@WarehouseId AND ItemId=i.ItemId) 60 61 --取库存表最大值id 62 SELECT @Table_MaxID= MAX(InventoryId) FROM Inventory WITH(NOLOCK) 63 64 --更新TableNumber Num值 65 UPDATE TableNumber SET Num=@Table_MaxID WHERE TableName='Inventory' 66 END 67 FETCH NEXT FROM Cursor_Warehouse INTO @WarehouseId; 68 END 69 CLOSE Cursor_Warehouse; 70 DEALLOCATE Cursor_Warehouse; 71
所有回答(1)
0
推荐阅读园子里的一篇博文——SQL Join的一些总结：
交叉联接（cross join）执行两个表的笛卡尔积（就是把表A和表B的数据进行一个N*M的组合）。也就是说，它匹配一个表与另一个表中的每一行；我们不能通过使用ON子句在交叉联接指定谓词，虽然我们可以使用WHERE子句来实现相同的结果，这是交叉联接基本上是作为一个内部联接了。
交叉联接相对于内部联接使用率较低，而且两个大表不应该进行交叉联接，因为这将导致一个非常昂贵的操作和一个非常大的结果集。
