How to optimize and improve performance for the following Sql Stored procedure? It fetches a large number of records , hence results in slow performance.
The column is also non clustered indexer.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO
-- Select all records from tblA based on T
ALTER PROC [dbo].[Usp_SP1]
@T nvarchar(50)
AS
IF @T = 'ABC'
BEGIN
SELECT
xyz as [XYZK],
COALESCE(RTRIM(LTRIM(T)),'') AS T,
COALESCE(RTRIM(LTRIM(V)),'') AS V,
COALESCE(RTRIM(LTRIM(Des)),'') AS Des,
COALESCE(Seq, 0) AS Seq,
COALESCE(Ac,0) AS Ac,
COALESCE(RTRIM(LTRIM(U)),'') AS U,
COALESCE(Ac,0) AS Ac,
COALESCE(L,0) AS _L,
COALESCE(M,0) AS _M,
COALESCE(E,0) AS _E,
COALESCE(S,0) AS _S
FROM tblA WITH (NOLOCK)
WHERE T = @T ORDER BY V, Seq
END
ELSE
BEGIN
SELECT
xyz as [XYZK],
COALESCE(RTRIM(LTRIM(T)),'') AS T,
COALESCE(RTRIM(LTRIM(V)),'') AS V,
COALESCE(RTRIM(LTRIM(Des)),'') AS Des,
COALESCE(Seq, 0) AS Seq,
COALESCE(Ac,0) AS Ac,
COALESCE(RTRIM(LTRIM(U)),'') AS U,
COALESCE(Ac,0) AS Ac,
COALESCE(L,0) AS _L,
COALESCE(M,0) AS _M,
COALESCE(E,0) AS _E,
COALESCE(S,0) AS _S
FROM tblA WITH (NOLOCK)
WHERE T = @T ORDER BY Seq
END
SET NOCOUNT OFF
GO
The query should take min time to execute and should be optimized.