Sql-Server

T-SQL 查詢還是數據庫引擎優化？

  • May 21, 2020

此查詢平均需要 100 毫秒，但是當您每秒有數百或數千次呼叫時，這將花費很多。無法記憶體數據集，因為它可能隨時更改。

有什麼建議可以在哪裡查看以優化它：查詢？db引擎設置？

declare @DeskModelID nvarchar(128)=N'abcdefgh'

select ModelID
from dbo.tblTask
where DeskModelID=@DeskModelID

（此查詢只是更大查詢的一部分，但會消耗大部分執行時間）

更多細節：

  • Microsoft SQL Server Web（64 位）
  • Windows Server 2016 標準版 (10.0)
  • SQL 版本 14.0.2027.2
  • ModelID 是主鍵 nvarchar(128)
  • 總行數 214050

表和索引定義：

/****** Object:  Table [dbo].[tblTask]    Script Date: 5/21/2020 4:43:13 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblTask](
   [ModelID] [nvarchar](128) NOT NULL,
   [OrganizationID] [nvarchar](128) NOT NULL,
   [UserModelID] [nvarchar](128) NOT NULL,
   [Name] [nvarchar](128) NULL,
   [DeskModelID] [nvarchar](128) NULL,
   [Estimate] [float] NULL,
   [TotalSpent] [float] NULL,
   [Label] [nvarchar](1024) NULL,
   [LabelName] [nvarchar](128) NULL,
   [LastAccess] [datetime] NOT NULL,
   [ListName] [nvarchar](64) NULL,
   [ShortLink] [nvarchar](16) NULL,
CONSTRAINT [PK_tblTasks] PRIMARY KEY CLUSTERED 
(
   [ModelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTask] ADD  CONSTRAINT [DF__tblTask__LastA__078C1F06]  DEFAULT (dateadd(month,datediff(month,(0),getutcdate()),(0))) FOR [LastAccess]
GO

/****** Object:  Index [NCIX_Board_Task_Estimate_TotalSpent]    Script Date: 5/21/2020 4:44:55 PM ******/
CREATE NONCLUSTERED INDEX [NCIX_Board_Task_Estimate_TotalSpent] ON [dbo].[tblTask]
(
   [DeskModelID] ASC
)
INCLUDE([Estimate],[TotalSpent]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

在此處輸入圖像描述

更新 1： 感謝評論者這裡是等待統計資訊：

  • 等待計數 19
  • 等待時間毫秒 102
  • WaitType ASYNC_NETWORK_IO

更新 2： SQL Server 執行時間：CPU 時間 = 0 毫秒，經過時間 = 0 毫秒。

（受影響的 3233 行）

SQL Server 執行時間：CPU 時間 = 0 毫秒，經過時間 = 87 毫秒。

更新 3：

SQL Server parse and compile time: 
  CPU time = 1 ms, elapsed time = 1 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 3 ms.

(3233 rows affected)

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

更新#4：

並且使用#temp 表沒有意義

   declare @DeskModelID varchar(128)=N'abcdef'
           , @cutc dateTime = GETUTCDATE()
           , @coef float = 1.0/3600.0

select ModelID, Estimate into #temp
from dbo.tblTask
where DeskModelID=@DeskModelID

   select 
           tsheet.TaskModelID as TaskModelID,
           case when sum(case when tsheet.TEnd is null then 1 else 0 end) > 0 then cast(1 as bit) else cast(0 as bit) end as IsOngoing,
           max(task.Estimate) as Estimate,
           sum(cast(DATEDIFF(second, isnull(tsheet.TStart, isnull(tsheet.TEnd, @cutc)), isnull(tsheet.TEnd, @cutc)) as float))*@coef as TotalSpent
       from dbo.tblTimesheet as tsheet
       inner join #temp as task on task.ModelID=tsheet.TaskModelID
       where tsheet.TaskModelID = task.ModelID
       group by tsheet.TaskModelID


   SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 3 ms.

(3233 rows affected)
SQL Server parse and compile time: 
  CPU time = 10 ms, elapsed time = 10 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(3231 rows affected)

SQL Server Execution Times:
  CPU time = 16 ms,  elapsed time = 150 ms.

更新#5：

同時選擇 #temp 和 #temp2 表給出以下時間：

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 15 ms,  elapsed time = 3 ms.

(3233 rows affected)
SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 7 ms.

SQL Server Execution Times:
  CPU time = 16 ms,  elapsed time = 19 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(3231 rows affected)

感謝大家現在我需要找到一種方法來加快網路/響應時間

我不能讓它花這麼多錢。

在我的（非常快的）桌面上，它的 CPU 時間不到 1 毫秒，而在 Azure SQL 數據庫上，它只有 4 毫秒。

也許您的 SQL Server 位於 VM 上，並且無法完全訪問主機的 CPU。

例如

--use tempdb
go
drop table if exists tblTask 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblTask](
   [ModelID] [nvarchar](128) NOT NULL,
   [OrganizationID] [nvarchar](128) NOT NULL,
   [UserModelID] [nvarchar](128) NOT NULL,
   [Name] [nvarchar](128) NULL,
   [DeskModelID] [nvarchar](128) NULL,
   [Estimate] [float] NULL,
   [TotalSpent] [float] NULL,
   [Label] [nvarchar](1024) NULL,
   [LabelName] [nvarchar](128) NULL,
   [LastAccess] [datetime] NOT NULL,
   [ListName] [nvarchar](64) NULL,
   [ShortLink] [nvarchar](16) NULL,
CONSTRAINT [PK_tblTasks] PRIMARY KEY CLUSTERED 
(
   [ModelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTask] ADD  CONSTRAINT [DF__tblTask__LastA__078C1F06]  DEFAULT (dateadd(month,datediff(month,(0),getutcdate()),(0))) FOR [LastAccess]
GO

/****** Object:  Index [NCIX_Board_Task_Estimate_TotalSpent]    Script Date: 5/21/2020 4:44:55 PM ******/
CREATE NONCLUSTERED INDEX [NCIX_Board_Task_Estimate_TotalSpent] ON [dbo].[tblTask]
(
   [DeskModelID] ASC
)
INCLUDE([Estimate],[TotalSpent]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


with 
n as 
(
 select top 214050 row_number() over (order by (select null)) i
 from sys.objects o, sys.messages m

),
q as 
(
   select 
          cast(newid() as nvarchar(128)) ModelId,
          cast(newid() as nvarchar(128)) [OrganizationID],
          cast(newid() as nvarchar(128)) [UserModelID],
          cast(newid() as nvarchar(128)) [Name],
          concat('DeskModelId', n.i%66)  [DeskModelID],
          n.i*17   [Estimate],
          n.i*17 [TotalSpent],
          cast(newid() as nvarchar(128)) [Label],
          cast(newid() as nvarchar(128)) [LabelName],
          getdate() [LastAccess],
          cast(newid() as nvarchar(128)) [ListName],
          'http://foo' ShortLink
   from n
)
insert into [tblTask] 
select * from q


 go

set statistics time on
declare @DeskModelID nvarchar(128)=N'DeskModelId10'

select ModelID
from dbo.tblTask
where DeskModelID=@DeskModelID
set statistics time off

在 Azure SQL 數據庫輸出上。在我的桌面上它是 <1 毫秒。

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

(3244 rows affected)

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 3 ms.

Completion time: 2020-05-21T10:35:44.3669520-05:00

如果沒有關於表、列或已採取的其他操作的更多資訊，很難知道究竟可以做什麼。在這些情況下，包括執行計劃總是非常有用。

我會首先確保 DeskModelID 上有一個索引，以便進行搜尋。

CREATE INDEX IX_DeskModelID ON dbo.tblTask(DeskModelID) INCLUDE (ModelID)

將 ModelID 添加為包含的列也會刪除鍵查找。

我還要確保@DeskModelID 的類型與dbo.tblTask​​.DeskModelID 的類型相匹配。如果他們不這樣做，那麼儘管存在索引，SQL Server 仍然可能必須隱式轉換所有 DeskModelID 才能將其與@DeskModelID 進行比較，從而導致索引掃描而不是查找。

編輯：根據@AaronBertrand 的評論添加了包含的列

引用自：https://dba.stackexchange.com/questions/267609

相關問答

Sql-Server

邏輯運算符使用不當導致查詢性能不佳

  • October 24, 2021
檢視問答
Sql-Server

查詢將每列值分為兩類：C呸呸呸呸_____這噸C嗬嗬___C這米米這n,ñ這噸C這米米這nCommon , Not_Common

  • August 17, 2021
檢視問答
Sql-Server

IN 子句中的冗餘條目

  • November 16, 2020
檢視問答
Sql-Server

常量變數為空時執行計劃不同

  • November 3, 2020
檢視問答
Sql-Server

查詢以基於“時間”列對行進行分類，而不使用 CASE 表達式

  • October 29, 2020
檢視問答
Sql-Server

對於最近的行，在 DateTime 上使用 RANK 查詢優化問題

  • February 27, 2020
檢視問答