低查询执行时间SQL服务器

Question

我在sql server数据库中有这个查询

SELECT [Id]
  ,[CreatedBy]
  ,[CreatedDate]
  ,[ModifiedBy]
  ,[ModifiedDate]
  ,[IsDeleted]
  ,[IsActive]
  ,[Type]
  ,[RelaseDate]
  ,[Prefix]
  ,[SubTitle]
  ,[Title]
  ,[Status]
  ,[Sequence]
  ,[Value]
  ,[Content]
  ,[Author]
  ,[Summery]
  ,[EndDate]
  ,[ViewedTime]
  ,[DefaultCategorieId]
  ,[URLTitle]
  ,[AlowComments]
  ,[HideImage]
  ,[ExternalLink]
FROM [SalesItem].[dbo].[Items]
where  Type='7a38bd0c-222f-4308-8dce-f7a2014d7d79' and IsDeleted <> 1
order by [CreatedDate] desc
OFFSET 10 ROWS
FETCH NEXT 20 ROWS ONLY; 

条目大约有200,000条记录，这个查询的执行时间大约是00:80:30

是否有任何方法来加快查询。因为同一个表可能有许多不同的查询，它们需要更长的执行时间

Answer 1

考虑[Id]为PK，启用了身份，CreatedDate上没有索引，可以按[ID]排序

As按两列排序是相同的。(希望你以后不更新CreatedDate)

这个查询应该更快

;with cte 
as
(
     SELECT ROW_NUMBER() over (order by [Id]) as rowid,
      ,[Id]
      ,[CreatedBy]
      ,[CreatedDate]
      ,[ModifiedBy]
      ,[ModifiedDate]
      ,[IsDeleted]
      ,[IsActive]
      ,[Type]
      ,[RelaseDate]
      ,[Prefix]
      ,[SubTitle]
      ,[Title]
      ,[Status]
      ,[Sequence]
      ,[Value]
      ,[Content]
      ,[Author]
      ,[Summery]
      ,[EndDate]
      ,[ViewedTime]
      ,[DefaultCategorieId]
      ,[URLTitle]
      ,[AlowComments]
      ,[HideImage]
      ,[ExternalLink]
    FROM [SalesItem].[dbo].[Items]
    where Type='7a38bd0c-222f-4308-8dce-f7a2014d7d79' and IsDeleted <> 1
)
select * from cte where rowid between 11 and 20

请让我知道执行时间