Select statement take 20 minute to return 1000 rows only why and how to minimize time?

Question

I work on SQL server 2012 Small query take too much time to return small amount of data
bout 1000 rows .

SELECT fmat.Value as PLID,c.CodeTypeId,
COUNT(DISTINCT tr.PartID) [#partsHasCodes]
into #partsHasCodes
FROM Parts.TradeCodes tr WITH(NOLOCK)
INNER JOIN Parts.Nop_Part pt WITH(NOLOCK) ON pt.PartID = tr.PartID
INNER JOIN Parts.Nop_PartsFamilyAttribute fmat WITH(NOLOCK) ON fmat.PartFamilyID=pt.PartsFamilyID AND fmat.[Key]=20281007
inner join #TempPlAndCodeType c on (c.CodeTypeId=tr.CodeTypeID) --AND (c.PLID is null OR fmat.Value=c.PLID)
WHERE (c.PLID is null OR fmat.Value=c.PLID)
GROUP BY fmat.Value,c.CodeTypeId

so How to solve issue
execution plan is
https://www.brentozar.com/pastetheplan/?id=ryCXs5jQv

what i try
create non cluster index on key field on table part family attribute but nothing changed
still take 20 minutes to run query above

CREATE TABLE [Parts].[Nop_PartsFamilyAttribute](
[PartFamilyAttributeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[PartFamilyID] [int] NOT NULL,
[Key] [int] NOT NULL,
[Value] nvarchar NOT NULL,
[CreatedDate] [datetime] NULL,
[CreatedBy] [int] NULL,
[ModifiedDate] [datetime] NULL,
[Modifiedby] [int] NULL,
[DeletedDate] [datetime] NULL,
[DeletedBy] [int] NULL,
CONSTRAINT [PK_Nop_PartsFamilyAttribute30] PRIMARY KEY CLUSTERED
(
[PartFamilyAttributeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_PartFamilyID_Key30] UNIQUE NONCLUSTERED
(
[PartFamilyID] ASC,
[Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Answer

Hi
Can you have an index on PartFamilyID,Key,Value on Parts.Nop_PartsFamilyAttribute table?
Also I would rewrite the query as

SELECT fmat.Value as PLID,c.CodeTypeId,
COUNT(DISTINCT tr.PartID) [#partsHasCodes]
into #partsHasCodes
FROM Parts.TradeCodes tr WITH(NOLOCK)
inner join #TempPlAndCodeType c on (c.CodeTypeId=tr.CodeTypeID)
INNER JOIN Parts.Nop_Part pt WITH(NOLOCK) ON pt.PartID = tr.PartID
INNER JOIN Parts.Nop_PartsFamilyAttribute fmat WITH(NOLOCK)
ON fmat.PartFamilyID=pt.PartsFamilyID AND fmat.Value=c.PLID
--AND (c.PLID is null OR fmat.Value=c.PLID)
WHERE (c.PLID is null OR fmat.[Key]=20281007)
GROUP BY fmat.Value,c.CodeTypeId

Answer

Hi @ahmed salah ,

Your execution plan shows that the most expensive is the non-clustered index lookup. In general, a non-clustered index is best established in the conditional column after the where clause.

Please try:

drop index indexname on tablename  
create nonclustered index index1 on #TempPlAndCodeType(PLID)  
SELECT fmat.Value as PLID,c.CodeTypeId,  
COUNT(DISTINCT tr.PartID) [#partsHasCodes]  
into #partsHasCodes  
FROM Parts.TradeCodes tr WITH(NOLOCK)  
INNER JOIN Parts.Nop_Part pt WITH(NOLOCK) ON pt.PartID = tr.PartID  
INNER JOIN Parts.Nop_PartsFamilyAttribute fmat WITH(NOLOCK) ON fmat.PartFamilyID=pt.PartsFamilyID AND fmat.[Key]=20281007  
inner join #TempPlAndCodeType c on (c.CodeTypeId=tr.CodeTypeID) --AND (c.PLID is null OR fmat.Value=c.PLID)  
WHERE (c.PLID is null OR fmat.Value=c.PLID)  
GROUP BY fmat.Value,c.CodeTypeId

Besides,conditions with OR is not the optimizer's best game.

Best Regards
Echo

Answer

suppose i need to create index on two column key and value both as mention above
how to do that please
can you tell me how to write to do that

Answer

Good day,

Please provide DDL+DML to all tables so we will have something to play with (together with the Execution Plan you published it will give us the tools probably)

In the meantime, Let's start the guessing game...

Try to create NON CLOUSTERED INDEX on table Parts.Nop_PartsFamilyAttribute on columns ([Key], [PartFamilyID]) (meaning that first use the KEY column) and include all the columns which you need from this table

CREATE NONCLUSTERED INDEX IX_Nop_PartsFamilyAttribute_Key_PartFamilyID
ON [Parts].[Nop_PartsFamilyAttribute] ([Key], [PartFamilyID]) INCLUDE ()
GO

Answer

Hi @ahmed salah ,

create index ：

CREATE [CLUSTERED | NONCLUSTERRED]  
INDEX index_name ON table_name (column_name)

When creating a table, the primary key column will create a clustered index by default, and a table can only have one clustered index, so please check whether the table has a clustered index.If there is already, then you can only create a non-clustered index, it can have more than one.

Here is an example of how to improve query efficiency with indexes created by myself, which contains methods for creating various indexes (including creating indexes on two columns), you can refer to：

drop table dbo.Users  
drop table dbo.Sessions  
  
CREATE TABLE dbo.Users  
(  
  username  VARCHAR(14)  NOT NULL,  
);  
  
CREATE TABLE dbo.Sessions  
(  
  id INT NOT NULL IDENTITY primary key,  
  username VARCHAR(14) NOT NULL,  
  starttime DATETIME2(3) NOT NULL,  
  endtime DATETIME2(3) NOT NULL,  
  pty INT NOT NULL,  
  CONSTRAINT CHK_endtime_gt_starttime  
    CHECK (endtime > starttime),  
  CONSTRAINT CHK_priority_range  
    CHECK (pty BETWEEN 1 AND 31)  
);  
  
-- Sample data (small)  
--TRUNCATE TABLE dbo.Sessions;  
--TRUNCATE TABLE dbo.Users;  
  
INSERT INTO dbo.Users(username) VALUES('User1'), ('User2'), ('User3');  
INSERT INTO dbo.Sessions(username, starttime, endtime, pty) VALUES  
  ('User1', '20160101 08:00:00.000', '20160101 08:30:00.000', 1),  
  ('User1', '20160101 08:05:00.000', '20160101 08:35:00.000', 2),  
  ('User1', '20160101 08:00:00.000', '20160101 08:30:00.000', 3),  
  ('User2', '20160101 08:00:00.000', '20160101 10:30:00.000', 3),  
  ('User2', '20160101 08:30:00.000', '20160101 10:00:00.000', 2),  
  ('User2', '20160101 09:00:00.000', '20160101 09:30:00.000', 1),  
  ('User2', '20160101 11:00:00.000', '20160101 12:00:00.000', 3),  
  ('User2', '20160101 11:30:00.000', '20160101 12:30:00.000', 2),  
  ('User2', '20160101 11:40:00.000', '20160101 12:40:00.000', 3),  
  ('User2', '20160101 12:00:00.000', '20160101 13:00:00.000', 2),  
  ('User3', '20160101 08:00:00.000', '20160101 09:00:00.000', 1),  
  ('User3', '20160101 08:00:00.000', '20160101 08:30:00.000', 2),  
  ('User3', '20160101 08:30:00.000', '20160101 09:00:00.000', 3),  
  ('User3', '20160101 09:30:00.000', '20160101 09:31:00.000', 1);  
  
IF OBJECT_ID(N'dbo.GetNums', N'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;  
GO  
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE  
AS  
RETURN  
  WITH  
    L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),  
    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),  
    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),  
    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),  
    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),  
    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),  
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum  
         FROM L5)  
  SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n  
  FROM Nums  
  ORDER BY rownum;  
GO  
  
DECLARE   
  @num_users      AS INT      = 2000,  
  @intervals_per_user AS INT      = 500,  
  @start_period       AS DATETIME2(3) = '20160101',  
  @end_period     AS DATETIME2(3) = '20160107',  
  @max_duration_in_ms AS INT  = 3600000; -- 60 nimutes  
    
--TRUNCATE TABLE dbo.Sessions;  
--TRUNCATE TABLE dbo.Users;  
  
INSERT INTO dbo.Users(username)  
  SELECT 'User' + RIGHT('000000000' + CAST(U.n AS VARCHAR(10)), 10) AS username  
  FROM dbo.GetNums(1, @num_users) AS U;  
  
WITH C AS  
(  
  SELECT 'User' + RIGHT('000000000' + CAST(U.n AS VARCHAR(10)), 10) AS username,  
    DATEADD(ms, ABS(CHECKSUM(NEWID())) % 86400000,  
  DATEADD(day, ABS(CHECKSUM(NEWID())) % DATEDIFF(day, @start_period, @end_period), @start_period)) AS starttime,  
    ABS(CHECKSUM(NEWID())) % 3 + 1 AS pty  
  FROM dbo.GetNums(1, @num_users) AS U  
    CROSS JOIN dbo.GetNums(1, @intervals_per_user) AS I  
)  
INSERT INTO dbo.Sessions WITH (TABLOCK) (username, starttime, endtime, pty)  
  SELECT username, starttime,  
    DATEADD(ms, ABS(CHECKSUM(NEWID())) % @max_duration_in_ms + 1, starttime)  
  AS endtime,  
    pty  
  FROM C;  
  
select * from dbo.Sessions  
select count(*) from dbo.Sessions  
  
   
   
  
--eg:select username,pty from dbo.Sessions where username='User0000000515'   
--no index  
DBCC dropcleanbuffers --Empty the cache  
set statistics io on --Open IO statistics   
select username,pty from dbo.Sessions where username='User0000000515'  
  
  
--nonclustered index  
create nonclustered index index1 on dbo.Sessions(username)  
DBCC dropcleanbuffers --Empty the cache  
set statistics io on --Open IO statistics   
select username,pty from dbo.Sessions where username='User0000000515'  
  
--create a compound index  
DROP index index1 on dbo.Sessions  
create nonclustered index index2 on dbo.Sessions(username,pty)  
DBCC dropcleanbuffers --Empty the cache  
set statistics io on --Open IO statistics   
select username,pty from dbo.Sessions where username='User0000000515'  
  
--covering index   
DROP index index2 on dbo.Sessions  
create nonclustered index index3 on dbo.Sessions(username) include(pty)  
DBCC dropcleanbuffers --Empty the cache  
set statistics io on --Open IO statistics   
select username,pty from dbo.Sessions where username='User0000000515'  
  
  
DROP index index1 on dbo.Sessions  
DROP index index2 on dbo.Sessions  
DROP index index3 on dbo.Sessions

The test table has 1 million pieces of data, so it will be slow when the table is built (it takes two or three minutes).

Best Regards
Echo

Select statement take 20 minute to return 1000 rows only why and how to minimize time?

7 answers