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

ahmed salah 3,131 Reputation points
2020-09-01T23:28:27.46+00:00

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

{count} votes

7 answers

Sort by: Most helpful
  1. Uri Dimant 206 Reputation points
    2020-09-02T02:36:21.59+00:00

    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

    No comments

  2. EchoLiu-MSFT 14,416 Reputation points
    2020-09-02T03:05:07.987+00:00

    Hi @ahmed salah ,

    22033-image.png

    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

    No comments

  3. ahmed salah 3,131 Reputation points
    2020-09-02T07:15:07.877+00:00

    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


  4. Ronen Ariely 13,786 Reputation points Microsoft MVP
    2020-09-02T07:43:02.393+00:00

    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 (<add all the columns which you need in the query>)
    GO

    No comments

  5. EchoLiu-MSFT 14,416 Reputation points
    2020-09-02T07:55:01.757+00:00

    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

    No comments