T-SQL 查詢還是數據庫引擎優化?
此查詢平均需要 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 的評論添加了包含的列