when execute query it take 32 second to return 2 rows so how to solve

Question

I work on sql server 2017 i face issue when run simple query it take 32 second to run two rows

so it is very slow according to number of rows returned and size of row not big

my execution plan is

script table

CREATE TABLE [Parts].[TradeCodeControl]([TradeCodeControlID] [int] IDENTITY(1,1) NOT NULL,
[VersionYear] [int] NULL,[Version] [float] NULL,[CodeTypeID] [int] NULL,
[RevisionID] [bigint] NULL,[Code] [varchar](20) NULL,[ZPLID] [int] NULL,
[ZfeatureKey] [bigint] NULL,[ZValue] [nvarchar](2500) NULL,[ZfeatureType] [nvarchar](200) NULL,
[Comment] [nvarchar](3000) NULL,[ModifiedDate] [datetime] NULL,[CreatedDate] [datetime] NOT NULL,
[Modifiedby] [int] NULL,[CreatedBy] [int] NULL,[OrderSequence] [tinyint] NULL,
CONSTRAINT [PK__TradeCod__49C7EB212E609428] PRIMARY KEY CLUSTERED ([TradeCodeControlID] ASC)WITH 
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING ONGOALTER TABLE 
[Parts].[TradeCodeControl] ADD  CONSTRAINT [DF__TradeCode__Creat__2439A564]  DEFAULT (getdate()) FOR [CreatedDate]GO

Sanjay Mrinal Kumar Kodangi · Answer

Run execution plan for your select statement and check which clause is taking more consumption. Then by using the execution plan you can fix the table schema. Creating proper Non Clustered indexes will definitely help in better performance.

Amira Bedhiafi · Answer

Please share with us your query plan.

Kapil Singh Kumawat · Answer

you need to create non clustered indexes on columns used in where clause

Sagar Chavan · Answer

Try by creating one of the below indexes. CREATE NONCLUSTERED INDEX IX_TradeCodeControl_ZPLID_ZfeatureKey_ZValue ON [Parts].[TradeCodeControl](ZPLID, ZfeatureKey, ZValue) CREATE NONCLUSTERED INDEX IX_TradeCodeControl_ZPLID ON [Parts].[TradeCodeControl](ZPLID) INCLUDE(ZfeatureKey, ZValue)

Nitin Sontakke · Answer

Hello, Have you considered creating non-unique non-clustered index on [ZPLId] column? Because that is the one used in the where clause.

ahmed elbarbary · Answer

execution plan https://www.brentozar.com/pastetheplan/?id=HyaIx0I6Y