主页 / user-11278729

Bob Ammerman's questions

Martin Hope
Bob Ammerman
Asked: 2024-10-30 17:55:17 +0800 CST
  • 7

我有一个包含许多表的数据库。我需要一个查询，根据当前记录的键，按键顺序读取下一个记录。[注意：我正在模拟一个旧的基于 C-ISAM 的系统，它一次读取一条记录，所以我不能使用正常的基于集合的操作]。例如，给定一个只有一个键的表，我的查询可能如下所示：

SELECT TOP 1 COL1, COL2, COL3, etc.
FROM TAB1
WHERE COL1 > @P0
ORDER BY COL1

由于有两个关键列，事情会变得有点棘手：

SELECT TOP 1 COL1, COL2, COL3, etc.
FROM TAB1
WHERE COL1 = @P0 AND COL2 > @P1
   OR COL1 > @P0
ORDER BY COL1, COL2

添加额外的关键列遵循相同的模式。

现在，如果您仔细想想，SQL Server 应该能够使用 INDEX SEEK 运算符来查找所需的记录，而且它确实对我的大多数表都这样做。但是，我发现一个表，无论我尝试什么，SQL Server 都坚持执行 INDEX SCAN，这在大表（这个表）中当然要慢得多。

我尝试了很多方法，包括使用WITH (FORCESEEK)、OPTIMIZE FOR和其他几种方法。在所有情况下，我最终都选择了 INDEX SCAN。

但是，我确实发现了一个导致两次搜索和一次连接的查询模式：

SELECT TOP 1 COL1, COL2, COL3, etc.
FROM ( 
   SELECT TOP 1 COL1, COL2, COL3, etc.
   FROM TAB1
   WHERE COL1 = @P0 AND COL2 > @P1

   UNION ALL

   SELECT TOP 1 COL1, COL3, COL3, etc.
   FROM TAB
   WHERE COL1 > @P0
) AS Q
ORDER BY COL1, COL2

这是一种改进，但仍然会产生低效的查询计划。关键字段越多，计划就越糟糕。我不想使用这种技术，因为我的大多数表都适用于更简单的查询，导致计划中只有一个 INDEX SEEK。

请注意，查询代码是为每个表预先生成的，而不是根据需要手动编码的，因此很难对不同的表使用不同的方案。数据库中有 1,000 多个表，因此确定哪些表有问题，哪些表没有问题是不切实际的。

这种方案的使用频率相当高（大约 100 多个用户），包括大量遍历整个表 [再次说明：我不能使用集合操作]，因此效率很重要。例如，我有一个进程，由于索引扫描，大约需要一个小时才能遍历 100,000 条记录的表（平均每秒只有 27 条记录）。它开始时相当快，但随着它遍历整个表，速度越来越慢。扫描另一个没有出现问题的表，结果是每秒大约 2,500 条记录，这是可以接受的。

我正在寻找一种方法来“强制” SQL Server 使用 INDEX SEEK 而不是 INDEX SCAN。

以下是有效表的架构：

CREATE TABLE [dbo].[CUSHST](
    [ID_] [bigint] IDENTITY(1,1) NOT NULL,
    [CUSTOMER_NUMBER] [nvarchar](5) NOT NULL,
    [ORDER_NUMBER] [decimal](6, 0) NOT NULL,
    [ORDER_DATE] [date] NOT NULL,
    [ORDER_TYPE] [nvarchar](1) NOT NULL,
    [SLS_CODE] [nvarchar](3) NOT NULL,
    [INVOICE_NUMBER] [decimal](6, 0) NOT NULL,
    [INVOICE_DATE] [date] NOT NULL,
    [GOLD_PRICE] [decimal](8, 2) NOT NULL,
    [SILVER_PRICE] [decimal](8, 2) NOT NULL,
    [ITEM_NUMBER] [nvarchar](10) NOT NULL,
    [PRODUCT_CATEGORY] [nvarchar](2) NOT NULL,
    [DESCRIPTION] [nvarchar](30) NOT NULL,
    [EXT_DESCRIPTION] [nvarchar](30) NOT NULL,
    [UNIT_OF_MEASURE] [nvarchar](3) NOT NULL,
    [QTY_ORDERED] [decimal](8, 2) NOT NULL,
    [QTY_SHIPPED] [decimal](8, 2) NOT NULL,
    [PRICE_PER_UNIT] [decimal](8, 2) NOT NULL,
    [DISCOUNT_PER_UNIT] [decimal](8, 2) NOT NULL,
    [LABOR_PER_UNIT] [decimal](8, 2) NOT NULL,
    [COST_PER_UNIT] [decimal](8, 2) NOT NULL,
    [SALE_AMOUNT] [decimal](9, 2) NOT NULL,
    [COST_AMOUNT] [decimal](9, 2) NOT NULL,
    [PO_NUMBER] [nvarchar](20) NOT NULL,
    [REVERSE_DATE] [decimal](7, 0) NOT NULL,
    [COMMENTS_1] [nvarchar](35) NOT NULL,
    [COMMENTS_2] [nvarchar](35) NOT NULL,
    [SHIPTO_NAME] [nvarchar](35) NOT NULL,
    [SHIPTO_ADDRESS_1] [nvarchar](35) NOT NULL,
    [SHIPTO_ADDRESS_2] [nvarchar](35) NOT NULL,
    [SHIPTO_CITY] [nvarchar](25) NOT NULL,
    [SHIPTO_STATE] [nvarchar](2) NOT NULL,
    [SHIPTO_ZIP] [nvarchar](10) NOT NULL,
    [TERM_CODE] [nvarchar](3) NOT NULL,
    [LENGTH_CODE] [nvarchar](10) NOT NULL,
    [SIZE_CODE] [nvarchar](12) NOT NULL,
    [TYPE_CODE] [nvarchar](2) NOT NULL,
    [KARAT] [nvarchar](4) NOT NULL,
    [SHIP_VIA_CODE] [nvarchar](3) NOT NULL,
    [SHIP_DATE] [date] NOT NULL,
    [PIECES_ORDERED] [decimal](7, 0) NOT NULL,
    [PIECES_SHIPPED] [decimal](7, 0) NOT NULL,
    [SURCHARGE] [decimal](8, 2) NOT NULL,
    [TIE_BREAKER] [int] NOT NULL,
    [PD_PRICE] [decimal](8, 2) NOT NULL,
    [PT_PRICE] [decimal](8, 2) NOT NULL,
    [WIDTH_CODE] [nvarchar](10) NOT NULL,
    [BACKORDER_FLAG] [nvarchar](1) NOT NULL,
    [PROD_BATCH1] [decimal](6, 0) NOT NULL,
    [PROD_BATCH2] [decimal](6, 0) NOT NULL,
    [PROD_BATCH3] [decimal](6, 0) NOT NULL,
    [PROD_BATCH4] [decimal](6, 0) NOT NULL,
    [PROD_BATCH5] [decimal](6, 0) NOT NULL,
    [PROD_BATCH6] [decimal](6, 0) NOT NULL,
    [PROD_BATCH_STRING] [nvarchar](15) NOT NULL,
    [LINE_NUMBER] [decimal](4, 0) NOT NULL,
    [BASE_SURCHARGE] [decimal](8, 2) NOT NULL,
    [SHIPTO_COUNTRY] [nvarchar](2) NOT NULL,
    [APPROVED_BY] [nvarchar](15) NOT NULL,
    [ENTERED_BY] [nvarchar](15) NOT NULL,
    [TSR_ACCOUNT_ID] [nvarchar](4) NOT NULL,
    [CC_CHARGE] [decimal](6, 2) NOT NULL,
    [QTY_ORDERED3] [decimal](10, 3) NOT NULL,
    [QTY_SHIPPED3] [decimal](10, 3) NOT NULL,
    [USE_LINE_PRICING] [nvarchar](1) NOT NULL,
    [GOLD_PRICE_LP] [decimal](8, 2) NOT NULL,
    [SILVER_PRICE_LP] [decimal](8, 2) NOT NULL,
    [PT_PRICE_LP] [decimal](8, 2) NOT NULL,
    [PD_PRICE_LP] [decimal](8, 2) NOT NULL,
    [COMMENTS_3] [nvarchar](35) NOT NULL,
    [COMMENTS_4] [nvarchar](35) NOT NULL,
    [INCO_TERM] [nvarchar](3) NOT NULL,
    [CURRENCY_FACTOR] [decimal](8, 7) NOT NULL,
    [PF_SIZE_CODE] [nvarchar](25) NOT NULL,
    [PF_BANDWIDTH] [nvarchar](20) NOT NULL,
    [LOGO] [nvarchar](1) NOT NULL,
    [SHIPTO_ATTENTION] [nvarchar](35) NOT NULL,
    [PROFORMA_AIR_DEST] [nvarchar](35) NOT NULL,
    [TIME_PREPPED] [int] NOT NULL,
    [OWN_LABEL] [nvarchar](1) NOT NULL,
    [FROM_MEMO] [nvarchar](1) NOT NULL,
    [FROM_MEMO_CUSTOMER_NUMBER] [nvarchar](5) NOT NULL,
    [PURCHASE_TO_POOL] [nvarchar](1) NOT NULL,
    [ROWVERSION] [timestamp] NOT NULL,
 CONSTRAINT [PK_CUSHST] PRIMARY KEY CLUSTERED 
(
    [CUSTOMER_NUMBER] ASC,
    [INVOICE_DATE] ASC,
    [INVOICE_NUMBER] ASC,
    [ITEM_NUMBER] ASC,
    [TIE_BREAKER] ASC,
    [ID_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

这是失败的表格：

CREATE TABLE [dbo].[GLJNL](
    [ID_] [bigint] IDENTITY(1,1) NOT NULL,
    [ACCOUNT_NUMBER] [decimal](7, 0) NOT NULL,
    [TRX_DATE] [date] NOT NULL,
    [DEBIT_AMOUNT] [decimal](11, 2) NOT NULL,
    [CREDIT_AMOUNT] [decimal](11, 2) NOT NULL,
    [SOURCE] [nvarchar](6) NOT NULL,
    [REFERENCE] [nvarchar](35) NOT NULL,
    [REF_1] [nvarchar](35) NOT NULL,
    [REF_2] [nvarchar](35) NOT NULL,
    [ROWVERSION] [timestamp] NOT NULL,
 CONSTRAINT [PK_GLJNL] PRIMARY KEY CLUSTERED 
(
    [ACCOUNT_NUMBER] ASC,
    [TRX_DATE] ASC,
    [ID_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
sql-server