Enhance Sql Server Procedure Performance

I have the following procedure and it's performance is kinda slow when I execute it for long range of time for big data
I have table and storing records of Cars parking
the table contains millions of records

I hope everything is clear and I can find some help to improve performance of my query

CREATE PROCEDURE [dbo].[Report_Parking]
@PageNumber        INT = 1, 
@PageSize          INT = 50, 
@StartTime         int = 0 , --I'm using unix timestamp
@EndTime           int = 0, 
@ColumnSort       NVARCHAR(50), 
@OrderSort         NVARCHAR(50),
@CarIDs          NVARCHAR(max)--array of cars
AS 
SET nocount ON 
SET fmtonly OFF 
BEGIN 
CREATE TABLE #parkingTmp 
(
ID	          int IDENTITY(1,1) NOT NULL,
rownum            bigInt,
CarID             NVARCHAR(23) NOT NULL,
Start_Time        DateTime, 
End_Time          DateTime,
Parking_Time      NVARCHAR(50),
Address		  NVARCHAR(max),
Long		  NVARCHAR(20),
Lat		  NVARCHAR(20),
Plate_Number	  NVARCHAR(max),
PageCounts	  bigInt
)
DECLARE @query NVARCHAR(MAX)='';
SET @query+= N'insert into #parkingTmp 
SELECT   * FROM [dbo].CarsParking 
WHERE	((EndTimeParking BETWEEN '+cast(@StartTime as varchar)+' AND '+cast(@EndTime as varchar)+') and [CarID] in('''+@CarIDs +''')'
exec(@query)
-- insert default records for not found cars 
Declare @Id nvarchar(23)
Declare @Ids Table (id nvarchar(23) primary Key not null)
Insert @Ids(id) select * from [dbo].[SplitStrings_ToList](@CarIDs,',') 
delete from @ids  
WHERE Id IN (SELECT DISTINCT CarID FROM #parkingTmp)
While exists (Select * From @Ids)
Begin
Select @Id = MAX(id) from @Ids
INSERT INTO #parkingTmp 
SELECT  top 1 *
FROM CarsParking T
WHERE CarID = @Id
order by Start_Time DESC 
Delete from @Ids Where id = @Id
End
--- end inserting 
-- select final result with paging
DECLARE @FirstRec INT= (@PageNumber -1) * @PageSize; 
DECLARE @LastRec INT= @PageNumber * @PageSize +1;
declare @sql nvarchar(Max)=''	 
SET @sql = N'WITH ctepaging AS (SELECT ROW_NUMBER() OVER(ORDER BY Plate_Number,'+@ColumnSort+' '+@OrderSort+') AS rownumber,* FROM #parkingTmp)'
SET @sql += ' SELECT * FROM ctepaging '
SET @sql += ' WHERE  rownumber > '+ CONVERT(NVARCHAR(12), @FirstRec) + ' AND rownumber < ' + CONVERT(NVARCHAR(12), @LastRec)
exec sp_executesql @sql 
END

I added index on ID which is primary key by default and
also non clustered index in these columns which I filter on where statement CarID,ParkingTime
it improves the performance to procedure so it's taking 1 minute for 100k records now
before it was taking more than 5 mins
taking 1 minute for 100k records
but still the performance is not that good

here's a sample of data generated by script

I would probably write something like this:

CREATE OR ALTER PROCEDURE [dbo].[Report_Parking]
@PageNumber        INT = 1, 
@PageSize          INT = 50, 
@StartTime         int = 0 , --I'm using unix timestamp
@EndTime           int = 0, 
@ColumnSort       NVARCHAR(50), 
@OrderSort         NVARCHAR(50),
@CarIDs          NVARCHAR(max)--array of cars

AS 
SET nocount ON 
SET fmtonly OFF 
BEGIN 
   
	DECLARE @query NVARCHAR(MAX)=''
	,	@FirstRec INT= (@PageNumber -1) * @PageSize
	,	@LastRec INT= @PageNumber * @PageSize +1;
    
	create table #IDs (id nvarchar(23) primary key)
	
	insert into #IDs (
		id
	)
	SELECT	DISTINCT * 
	from	[dbo].[SplitStrings_ToList](@CarIDs,',') 

	SET @query = N'
		select *
		from 
(
			SELECT *, row_number() over(order by  Plate_Number,'+@ColumnSort+' '+@OrderSort+') as rownumber2
			FROM (
					SELECT	ROW_NUMBER() OVER(PARTITION BY CarID ORDER BY Start_Time DESC) AS rownumber1
					,	cp.* 
					FROM	[dbo].CarsParking cp
					INNER JOIN #IDs ids
						ON	ids.ID = cp.CarID
					WHERE   EndTimeParking BETWEEN @StartTime  AND @EndTime 
				) x
			WHERE x.rownumber1 = 1 -- Take last car post
			) y
		WHERE  rownumber2 > @FirstRec -- Sort
		AND	rownumber2 < @LastRec '
    
    exec sp_executesql @query 
    ,	N'@starttime INT, @endtime INT, @FirstRec INT, @LastRec INT'
    , @StartTime = @StartTime, @EndTime = @EndTime, @FirstRec = @FirstRec, @LastRec = @LastRec
END
GO

-- test code
exec report_parking 1, 30,111, 222, 'test,', 'test2', '1,2,3,4'

I like to put the IDs into temp table with index so the join is fast.

For the dynamic query, i pass the variables as parameters instead of building whole SQL string like you do. In some cases your version is faster, but it's usually better to parametrise things.

Finally, to handle sort and paging, i do two row_numbers, first to remove duplicates from cars, and second to properly sort them by pages. This part might be slow but hopefully you have few rows in the @CarIDs list so this won't be a huge problem

The query is a bit untested but you should be able to get the general idea

• Avoid Using Dynamic SQL: Dynamic SQL should be avoided, as it has several disadvantages, such as the possibility of SQL injection and performance issues. Instead, use parameterized queries which are not only safer but are often faster due to SQL Server being able to reuse the execution plans.

• Filter Early: Instead of inserting all records into a temporary table and then filtering, try to filter the data as early as possible to reduce the amount of data being handled. Use the WHERE clause in the first query to filter out only the records needed.

• Use Proper Indexes: Indexes are key to optimizing query performance. Create indexes on the columns that are frequently accessed in your WHERE clause, JOIN, and ORDER BY clauses. If you already have indexes, make sure they are being used efficiently. Using SQL Server's Query Execution Plan can be helpful in analyzing which indexes are being used.

• Use Proper Data Types: I noticed that you are using NVARCHAR(MAX) for some columns like Address and Plate_Number. It's usually best to avoid using MAX as the length unless it is really necessary. Instead, try to use an appropriate data length for the data stored in these columns.

• Batching and Pagination: You are already using paging which is good, but it can still be optimized. Try to avoid using SELECT * and instead select only the columns you need.

• Optimize Loop: The while loop where you're inserting default records can be slow. Consider using a set-based approach instead of a loop if possible. For example, you might insert all the records at once instead of in a loop.

• Avoid Table Variables for Large Data Sets: Table variables can be less efficient for large data sets compared to temp tables. You might consider just using temp tables if you are dealing with a lot of data.

• Use Query Hints: Sometimes, providing query hints can boost performance. For instance, you might find that using the (NOLOCK) hint makes sense in your case. However, use these cautiously and only if you are sure they are needed and appropriate for your case.

• Avoid Using Functions in WHERE Clause: If [dbo].[SplitStrings_ToList] is a function, using it in the WHERE clause could slow down your query. Try to avoid using functions in the WHERE clause.

• Review Server Settings and Resources: Ensure your SQL Server has enough resources (CPU, memory) and review SQL Server settings. Sometimes, performance issues are not just because of the query but because of the server resources and configuration.

Remember that tuning performance can sometimes be a process of trial and error. Always thoroughly test your changes to ensure they are improving performance and not affecting functionality or data integrity.