0

This query takes 100 ms on avarage but when you have hundreds or thousends of calls of them every second this would cost a lot. The dataset can not be cached as it may be changed at any second.

Any suggestion where to look at to optimize it: query? db engine settings?

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

select ModelID
from dbo.tblTask
where DeskModelID=@DeskModelID

(This query just a part of a little bit bigger query but consumes most of the execution time)

More details:

  • Microsoft SQL Server Web (64-bit)
  • Windows Server 2016 Standard (10.0)
  • SQL Version 14.0.2027.2
  • ModelID is primary key nvarchar(128)
  • Total rows 214050

Table and indexes definition:

/****** 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

enter image description here

Update 1: Thx to commenters here is wait statistics:

  • WaitCount 19
  • WaitTimeMs 102
  • WaitType ASYNC_NETWORK_IO

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

(3233 rows affected)

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

Update 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.

Update #4:

and using #temp table makes no sense

    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.

Update #5:

selecting both into #temp and #temp2 tables gives the following times:

 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)

thanks to everyone now I need to find out a way to speedup network/response time

1

I can't get that to cost nearly that much.

On my (very fast) desktop it's under 1ms of CPU time, and on Azure SQL Database it's only 4ms.

Perhaps your SQL Server is on a VM and is not getting full access to the host's CPUs.

EG

--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

On Azure SQL Database outputs. On my desktop it was <1ms.

 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
| improve this answer | |
  • have added "set statistics time" shows 84 ms on avarage – okarpov May 21 at 16:55
  • And what about the synthetic test I posted? If it's similar you need to look at your server. – David Browne - Microsoft May 21 at 16:57
  • I'm testing it locally as well 12 cores + SSD – okarpov May 21 at 16:57
  • please look at the update #3 - this is with #temp, does it mean that I should use #temp table to speed up the query? what else can cuse such issue? – okarpov May 21 at 17:24
  • That’s all just network time, sending the rows to the client. – David Browne - Microsoft May 21 at 17:26
0

Without further information about the table, columns, or other actions already taken, its hard to know what exactly could be done. Including the execution plan is always really useful in these cases.

I would start by making sure there is an index on DeskModelID in order for a seek to occur.

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

Adding ModelID as an included column would also remove key lookups.

I would also make sure that the type of @DeskModelID matches the type of dbo.tblTask.DeskModelID. If they don't, its possible that despite an index being present, SQL Server still has to implicitly convert all DeskModelIDs just to compare it to @DeskModelID, causing an index scan instead of a seek.

Edit: Added included column per @AaronBertrand's comment

| improve this answer | |
  • Wouldn’t you also want ModelID included? – Aaron Bertrand May 21 at 13:21
  • yes both types are nvarchar(128) – okarpov May 21 at 13:53
  • adding the index makes no difference - same result 100ms on avarage – okarpov May 21 at 14:02
  • 1
    Given the new info, my suggested index is nothing more than a slightly slimmer duplicate of the existing nonclustered index. Not surprising it did not help here... – Anton1151 May 21 at 14:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.