替代使用WHERE ... IN(...)进行慢速SQL查询 [英] Alternative to using WHERE ... IN (...) for slow SQL queries

问题描述

这实际上是一个更大的复杂查询的一部分.
根据查询计划，此语句的排序决定了较大查询的成本.
通过具体化查询的这一部分，我证实了它占了成本的主导地位.

This is actually part of a much larger complex query.
According to the query plan the sort on this statement dominates the cost of the larger query.
And by materializing this part of the query I verified it dominates the cost.

    select [sID], ROW_NUMBER() over (partition by [sID] order by [wordPos]) [rn], [wordPos], [wordID]
      from [FTSindex] 
     where [wordID] in (428,2112)
  order by [sID], [rn] 

从右到左:
-索引搜寻5％(IX_FTSindex_wordID_sID)
-排序76％
-平行度19％

From right to left:
- Index seek 5% (IX_FTSindex_wordID_sID)
- Sort 76%
- Parallelism 19%

CREATE TABLE [dbo].[FTSindex](
    [sID] [int] NOT NULL,
    [wordPos] [int] NOT NULL,
    [wordID] [int] NOT NULL,
    [charPos] [int] NOT NULL,
 CONSTRAINT [PK_FTSindex] PRIMARY KEY CLUSTERED 
(
    [sID] ASC,
    [wordPos] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_FTSindex_wordID_sID] ON [dbo].[FTSindex] 
(
    [wordID] ASC,
    [sID] ASC,
    [wordPos] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
GO

鉴于IX_FTSindex_wordID_sID包含[sID]和[wordPos]，我认为排序会非常快.
单独尝试过[wordID]和[wordID]，[sID]，但排序仍然是费用的76％.

Given the IX_FTSindex_wordID_sID includes [sID] and [wordPos] I thought that sort would be very fast.
Tried [wordID] alone and [wordID], [sID] and still the sort is still 76% of the cost.

即使是该查询

    select [sID], [wordPos] -- , wordID
      from [FTSindex] 
     where [wordID] in (428,2112)
  order by [sID], [wordPos]   

排序是排序的76％还是费用.

The sort is sort is 76% or the cost.

如何降低分类成本?
PK必须保持原样.
我可以添加或修改其他索引.

How can I get the sort cost to come down?
The PK has to stay as it is.
I can add or revise other indexes.

推荐答案

再次咯咯地笑，您可以尝试以下查询吗:

Just for giggles again, could you try this query:

  select 
    [sID], 
    ROW_NUMBER() over (partition by [sID] order by [wordPos]) [rn], 
    [wordPos], [FTSindex].[wordID]
  from [FTSindex] 
  join ( 
    values (428), (2112)
  ) w (wordID) on w.wordID = [FTSindex].wordID
  order by [sID], [rn] 

有时候，在问题上投入更多的硬件是正确的答案.尽管我同意这应该是最后的手段，而不是首先的手段.此特定问题是否需要更多的CPU，更多的内存或更多的主轴取决于许多因素，包括您目前使用的硬件.

Sometimes, throwing more hardware at the problem is the correct answer; though I agree that this should be a last resort and not a first. Whether this particular problem requires more CPU, more memory, or more spindles is dependent on many factors, including your present hardware.

您的结果集为160万行(每4个整数)，应在任何合理数量的当前硬件上快速排序.由于发生了延迟，因此似乎有可能在9亿行的基集上进行过多的处理，而挑战是找出原因.您可以附上有关查询计划的更多详细信息吗?

Your result set of 1.6 million rows, each 4 integers, should sort quickly on any reasonable amount of current hardware. Since delays are occurring it seems likely that too much processing is occurring on the base set of 900 million rows, and the challenge is to identify why. Can you attach more details about the query plan?

这篇关于替代使用WHERE ... IN(...)进行慢速SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！