We have a stored procedure in which we are creating a temp table which has more than 200,000 rows with 30 columns (2 of DATETIME, 4 of INT, 5 of [numeric](20, 3) and rest of VARCHAR(500) data types. This SP is doing a SELECT from this temp table for the final output.
Now the problem is the final SELECT statement is taking more than 3 minutes to return all results.
We tried by adding an IDENTITY column to the temp table and added a non-clustered index on the IDENTITY column. We then changed the SELECT so that in the final output we don't include this column in the column list but in the ORDER BY clause only. It is still taking more than 2.30 minutes.
We need a solution to get this SELECT statement to complete faster.