[性能问题描述]
SELECT p.[ID],p.[CustomerID],p.[OrderPositionID],p.[MergerPackageNo],p.[PackageIndex],p.[PackageType],p.[PackageState],p.[Country],p.[Province],p.[City],p.[Address],p.[PostCode],p.[Telephone],p.[TransportSupplierID],p.[TransportID],p.[WareHouseID],p.[TrackNumber],p.[ShipTime],p.[ShippingSupplierDiscount],p.[DeclarationPrice],p.[SystemWeight],p.[PackageWeight],p.[RegisteredFee],p.[HandleFee],p.[ShippingFee],p.[FinalShippingFee],p.[ActualShippingFee],p.[ActualShippingFeeTime],p.[ShippingAdminID],p.[ShippingAdminName],p.[GetPackageTraceTime],p.[PackageStatus],p.[DelivereDays],p.[LastPackageMemo],p.[SendEmailID],p.[SendEmailTime],p.[SecurityReturn],p.[SecurityReturnTime],p.[AddTime],p.[UpdateTime],p.[PrintBatchNo],p.[PrintInvoiceState],p.[PrintAddressState],p.[PrintTime],p.[GetTrackNumberTime],p.[PackagePositionCode],p.[TariffFee],p.[TurnNumber],p.[LogisticsOrderNo],p.[IsSameAddressMergerPackage],p.[AliLogisticsInfo],p.[PDFLabelFilePath],p.[IsAccount],p.[DeclarationPriceSum],p.[IsubmitPackConfirm],p.[PackageFollowState],p.[PackageLastFollowMsg],p.[PackageLastFollowDate],p.[DeliveredDays],p.[PackageFollowUpdateDate],p.[PickingPersonID],p.[PickingPersonName],p.[PackagingPersonID],p.[PackagingPersonName],p.[PackageLength],p.[PackageWidth],p.[PackageHeight],p.[PackageWeightDefault],p.[IsTaxAccount],p.[isWeightState],p.[PickingTime],p.[PackingTime],p.[PrintType],p.[WeighingPersonID],p.[WeighingPersonName],p.[TwoSortingExportNum],p.[IsOperateTaxFree],p.[StockOrderNumber],p.[SyncStockOrderStatus],p.[SyncStockOrderDate],p.[SyncStockOrderMsg],p.[CheckID],p.[CheckName],p.[DistributionTime],p.[AmazonCartonGuid],p.[FreightProof],p.[TaxationProof],p.[IsBiSycnComplete],p.[BiSycnCompleteTime],p.[PrintAdminID],p.[PrintAdminName],p.[PacketCost],p.[PrintInvoice],p.[FinalShippingFeeOrginAmount],p.[FinalShippingFeeOrginCurrency],p.[FinalShippingFeeOrginExchangeRMB],p.[TariffFeeOrginAmount],p.[TariffFeeOrginCurrency],p.[TariffFeeOrginExchangeRMB],p.[TallyPersonID],p.[TallyPersonName],p.[TallyTime],p.[IsStockOrderWithPackage],p.[SKUNumberWithPackage],p.[ProductNumberWithPackage],p.[BillingPersonID],p.[BillingPersonName],p.[BillingTime],p.[OrderPackageID],p.[CompleteTime],p.[ActualShippingFeeTime2],p.[PrintPackageType],p.[CheckTime],p.[VolumeWeight],p.[PackageFromType],p.[DeleteTime],p.[FirstReceivingTime],p.[NewTrackTime],p.[CompletePersonName],p.[CompletePersonID],p.[ShipmentsNo],p.[IsAutoRefreshShippingFee],p.[IsHasExceptionWhenPackageWeight],p.[OvertimeTagType],p.[FirstCompleteTime],p.[FirstPrintTime],p.[OvertimeTagTypeWithFirstComplete],p.[ThrowingRateFee],p.[VirtuallyFastType],p.[PackagePackState],p.[LogisticsAttributesId],p.[LogisticsAttributesName],p.[TallyPositionCode],p.[SynOverSeaOperateID],p.[SynOverSeaOperateName],p.[SellAddWeightType],p.[SellAddWeight],p.[SellAddWeightThrowingRate],ISNULL(p.[DataSource],0) AS [DataSource],CASE WHEN p.[PackageState] = 1 THEN 1 WHEN p.[PackageState] = 2 THEN CASE WHEN EXISTS (SELECT 1 FROM dbo.RB_Package_Picking_Package ppkg(nolock) INNER JOIN dbo.RB_Package_Picking_ChildWarehouseArea pca(nolock) ON ppkg.PickingGUID = pca.MainPickingGUID WHERE ppkg.PackageID = p.ID AND pca.PickingStatus IN (0,1)) THEN 2 ELSE 3 END WHEN p.[PackageState] = 5 THEN CASE WHEN EXISTS (SELECT 1 FROM dbo.RB_OrderInfo oi(nolock) INNER JOIN dbo.RB_OrderSource os(nolock) ON oi.OrderSourceID = os.id WHERE oi.orderCode = p.MergerPackageNo AND os.OrderSourceType = 1 AND (oi.AmazonShipmentId IS NULL OR oi.AmazonShipmentId = '')) THEN 4 ELSE 5 END WHEN p.[PackageState] = 3 THEN 6 ELSE NULL END AS package_status_view FROM dbo.RB_Procurement_Order_Package p(nolock) WHERE ISNULL(p.DataSource,0)=0 AND p.WareHouseID IN ( SELECT ID FROM RB_WareHouse ( NOLOCK ) WHERE IsStockpile = 1 );
[分析过程]
SELECT count(0) FROM dbo.RB_Procurement_Order_Package p(nolock) WHERE ISNULL(p.DataSource,0)=0 AND p.WareHouseID IN ( SELECT ID FROM RB_WareHouse ( NOLOCK ) WHERE IsStockpile = 1 )
609058 返回数据很大
两个子查询返回的数据 查询次数就一共有120万次
CASE
WHEN p.[PackageState] = 1 THEN 1
WHEN p.[PackageState] = 2 THEN
CASE
WHEN EXISTS (SELECT 1
FROM dbo.RB_Package_Picking_Package ppkg(nolock)
INNER JOIN dbo.RB_Package_Picking_ChildWarehouseArea pca(nolock)
ON ppkg.PickingGUID = pca.MainPickingGUID
WHERE ppkg.PackageID = p.ID
AND pca.PickingStatus IN ( 0, 1 )) THEN 2
ELSE 3
END
WHEN p.[PackageState] = 5 THEN
CASE
WHEN EXISTS (SELECT 1
FROM dbo.RB_OrderInfo oi(nolock)
INNER JOIN dbo.RB_OrderSource os(nolock)
ON oi.OrderSourceID = os.id
WHERE oi.orderCode = p.MergerPackageNo
AND os.OrderSourceType = 1
AND ( oi.AmazonShipmentId IS NULL
OR oi.AmazonShipmentId = '' )) THEN 4
ELSE 5
END
[期望结果]
采用读库同步 分页 处理 数据