主页 / user-164369

Badger's questions

Martin Hope
Badger
Asked: 2021-04-20 04:54:48 +0800 CST
  • 0

以下查询在通过 db2 命令从控制台运行时有效。但是通过 DBeaver 运行时失败。

BEGIN NOT ATOMIC
  FOR R AS C CURSOR WITH HOLD FOR
    SELECT ROW_NUMBER() OVER() AS INDEX, ID
    FROM A_TABLE a
    WHERE a.ID BETWEEN 1 AND 1000
    ORDER BY a.ID
  DO
    UPDATE A_TABLE SET A_COL = 1 WHERE ID = R.ID;--
    IF MOD(R.INDEX, 100) = 0 THEN
      COMMIT;--
    END IF;--
  END FOR;--
  COMMIT;--
END;

DBeaver 给出以下结果

SQL 错误 [42601]：在“WHERE ID = R.ID”之后发现了意外的标记“END-OF-STATEMENT”。预期的标记可能包括：“<psm_semicolon>”.. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.26.14

db2 cursors
Martin Hope
Badger
Asked: 2018-11-06 23:07:28 +0800 CST
  • 3

你能强制 SQL Server 在计算列上使用索引来进行交换操作吗？

不幸的是，我们在 SQL Server 中有一个表如下

CREATE TABLE [dbo].[Data](
  [ID] [int] NOT NULL,
  [ValDate] [datetime] NOT NULL,
  [ValHour] [int] NOT NULL,
  [ValMin] [int] NOT NULL,
  [Value] [float] NULL,
  [Flag_ID] [int] NOT NULL,
  CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
  ([ID],[ValDate],[ValHour],[ValMin]) ON [PRIMARY]
) ON [PRIMARY]

即使 ValDate 是 DATETIME，它也只存储日期部分。

使用此数据库的一些旧应用程序进行以下查询

SELECT ID, ValDate, ValHour, ValMin, Value
FROM DATA
WHERE ID = @id 
  AND @start < DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate))
  AND DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate)) <= @end

如果有大量数据，这会减慢速度，因为 SQL 无法使用 WHERE 子句中指定的日期来查找，并且必须扫描 ID = @id 的每个表条目以查找所有行。

我们无法更改应用程序，所以我决定添加一个计算列并在其上放置一个 INDEX

ALTER TABLE dbo.Data ADD ComputedDateTime AS
  DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate))
GO
CREATE NONCLUSTERED INDEX [Data_ComputedDateTime_IDX] ON [dbo].[Data]
  ([ComputedDateTime], [ID])
INCLUDE ([ValDate], [ValHour], [ValMin], [Value])

这使得原始查询速度很快，但是如果我改变 DATEADD 的顺序，将分钟添加到日期，然后是不使用索引的小时。

所以我假设这是因为 SQL Server 没有意识到操作是可交换的，即 (date + hour) + minute = (date + minute) + hour

有没有办法在不创建两个计算列和两个索引的情况下加快两个计算顺序？

sql-server