每天的负载用于5分钟。我更改为参数化存储过程,按照执行计划的建议应用索引,一天降至47秒,但永远负载一个月。可能是什么问题呢?平均每日负载约为50,000行。
USE [DBl]
--[dbo].[ClosedOrder] StartDate, '@EndDate'
ALTER PROCEDURE [dbo].[ClosedOrders]
( @StartDate DATETIME
,@EndDate DATETIME
)
AS
DECLARE @temp_dec DECIMAL(15,2) = 0.00
DECLARE @temp_int int = NULL
DECLARE @temp_str nvarchar(60) = NULL
DECLARE @temp_date Date = NULL
-- Michael Xia changes temp table to table variable on 05/24/2016
DECLARE @lines TABLE
(
SalesOrderID nvarchar (20),
SalesOrderLine smallint,
CustomerID_SoldTo nvarchar (14),
OrderDate date,
)
INSERT INTO @lines
SELECT
-- multiple sub queries like below
, (SELECT TOP 1 CAST(sh.ship_date AS DATE)
FROM dbl.dbo.[Shipments] sh (NOLOCK)
WHERE sh.co_num = coi.co_num AND sh.co_line = coi.co_line
AND sh.shipped=3
ORDER BY sh.ship_date DESC) 'DateLastShipped'
---multiple update to the table variable using scalar function like below
UPDATE @lines
SET __ServiceLevelDays = CASE
WHEN ScheduledToShipDate = ShipNoLaterThanDate THEN 1
ELSE [dbl].[dbo].GetNumberOfBays(SchlShipDate,ShipNoLaterThanDate,1) + 1
END
TRUNCATE TABLE [DBL].[dbo].[ClosedOrder]
INSERT INTO [DBL].[dbo].[ClosedOrder]
(
)
SELECT
*
FROM @lines
END
由于,每日负载需要几秒钟(准确地说是47秒),因此您需要通过创建临时表,存储所有日期,然后创建循环并添加来修改每月负载每天需要花几分钟(47秒 * 30次)间接地,在写入数据库之前减少存储器中要存储的总数。希望会有所帮助。