动态SQL运行的时间太长

Question

我正在尝试运行一个动态SQL查询，该查询应该加载超过200M条记录。我试图一次加载所有的记录，但得到了'System.OutOfMemory‘异常，因此决定在运行一个多小时后将时间间隔打破为months.The，即使我只运行了一个月。我对日期进行了硬编码，运行时没有使用“While”，它运行正常。有没有人可以检查一下，让我知道其中是否有错误。下面是我的问题

DECLARE @FromDate VARCHAR(MAX)='',
        @params nvarchar(max) = N'@StartDate Date out,@EndDate Date out,@FromDate date out,@ToDate date out',
        @SQL NVARCHAR(MAX)=’’,
        @ToDate VARCHAR(MAX)='', 
        @StartDate VARCHAR(MAX)=’’,
        @EndDate VARCHAR(MAX)=’’;

SELECT  @FromDate= DATEADD(DAY,-365, GETUTCDATE()), @ToDate =     GETUTCDATE();
SELECT @StartDate=@FromDate, @EndDate=CAST(DATEADD(MONTH,1, @FromDate) AS DATE);
SET @SQL = '
WHILE ''' + @FromDate + ''' < ''' + @ToDate + '''
   BEGIN
   INSERT INTO dbo.Sales
   (
   ProductID,
   SaleDate,
   Quantity 
   )
 Select 
      ProductID,
      SaleDate,
      Quantity 
 from 
     OPENQUERY(TeraData1,''
        Select 
        PR_ID           AS   ProductID,
        SL_Date         AS   SaleDate,
        PR_QTY          AS   Quantity
    FROM
        Sales.Product
    where
SaleDate BETWEEN ' + @StartDate + 'AND '+ @EndDate + '
'')

SET  @FromDate =DATEADD(MONTH,1,'''+ @FromDate +''')
SET  @StartDate =DATEADD(MONTH,1,'''+ @StartDate +''')
SET  @EndDate =DATEADD(MONTH,1,'''+ @EndDate +''')
END’

 EXECUTE sp_executesql @SQL, @params,
                @FromDate=@FromDate,
                @ToDate=@ToDate,
                @StartDate = @StartDate out,
                @EndDate = @EndDate out;

Answer 1

DECLARE @FromDate DATE

SET @FromDate = DATEADD(YEAR,-1,GETUTCDATE())

WHILE @FromDate < GETUTCDATE()
BEGIN
   INSERT INTO dbo.Sales
   (
      ProductID,
      SaleDate,
      Quantity 
   )
 Select 
      ProductID,
      SaleDate,
      Quantity 
 from 
     OPENQUERY(TeraData1,
       Select 
          PR_ID           AS   ProductID,
          SL_Date         AS   SaleDate,
          PR_QTY          AS   Quantity
       FROM
          Sales.Product
       where
          SaleDate >= @FromDate AND SaleDate < DATEADD(MONTH,1,@FromDate)
    )

    SET  @FromDate =DATEADD(MONTH,1,@FromDate)
END

看起来你的变量/日期比你需要的要多得多，除非你想要动态传递链接服务器名或其他一些列，否则你不需要动态SQL。

此外，请坚持使用参数的适当数据类型，而不是让SQL推断您想要的值。

另外，您对日期使用BETWEEN将会重复某些日期，因为它包含了比较的限制1。

如果您确实想要动态执行此操作，请将控制流保留在非动态SQL中并传递参数，下面是一个示例：

DECLARE @FromDate DATE

SET @FromDate = DATEADD(YEAR,-1,GETUTCDATE())

WHILE @FromDate < GETUTCDATE()
BEGIN

    DECLARE @LinkedServerName NVARCHAR(MAX) = 'TeraData1'
    DECLARE @ParamDef NVARCHAR(MAX) = '@FromDate DATE'
    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = '
      INSERT INTO dbo.Sales
      (
         ProductID,
         SaleDate,
         Quantity 
      )
    Select 
        ProductID,
        SaleDate,
        Quantity 
    from 
        OPENQUERY(' + @LinkedServerName + ',
          Select 
             PR_ID           AS   ProductID,
             SL_Date         AS   SaleDate,
             PR_QTY          AS   Quantity
          FROM
             Sales.Product
          where
             SaleDate >= @FromDate AND SaleDate < DATEADD(MONTH,1,@FromDate)
       )'

    EXECUTE sp_executesql @SQL, @PramDef, @FromDate = @FromDate

    SET  @FromDate =DATEADD(MONTH,1,@FromDate)
END