I have used below query to fetch maximum of 6000 records in SQL Server 2012.
Select A.Date, A.City
From dbo.TBL_Auction (NoLock) A
Inner Join dbo.TBL_PLACE (NoLock) B On A.Place_Fk_Id = B.Place_Pk_Id
Inner Join dbo.TBL_SALES (NoLock) C On A.AucCode = C.AucCode
Inner Join dbo.TBL_SERVICES (NoLock) D On C.AucCode = D.Servicecode And C.Regno = D.ServiceRegno
Inner Join dbo.TBL_REGISTRATION E On Left(D.ServiceId,12) = E.REGNO
Where C.Status = 'L' And E.Status = 'L'
But query execution time sometimes takes above 4 minutes and sometimes within 2 seconds. I have created indexes in all the joining tables. But no luck.
In last three tables over 200k records are there. I have checked execution plan. In execution plan, Clustered Index Scan is used instead of Seek. I have created indexes for all columns in joining tables. In development environment, when more number of developers accessing database, at that time query takes more time.
How to boost up query execution time?