I am facing an issue with the performance. The selection of data from table is taking more than 20 sec to retrieve close to 300k records. Created a clustered index on col1 and col2 but no improvement.
Select Distinct Col1,Col2,Col3 + CHAR(13) + CHAR(10) Col3
From Table
Order by Col1,Col2
Update
Created indexes on col1
and col2
but col3
data is very big, more than 8000 characters, so SQL-server will not allow index on such column.
I am not sure how group by will help in removing the distinct.
As I mentioned it is simple select from table, yes 300k records has to be returned from table.
CREATE TABLE Tablex(
[Col1] [nvarchar](5) NULL,
[Col2] [nvarchar](20) NULL,
[Col3] [nvarchar](max) NULL) ON [PRIMARY]
Create Clustered Index ix_FCL_Tran On Tablex (Col1 Asc, col2 Asc)
Let me know if any more details is required.
Update
No change after adding option fast here is the execution plan
Distinct
, do you have rows with same values in Col1, Col2 and Col3 ?CREATE TABLE
statement and the indexes of table?SELECT COUNT(*) FROM (SELECT 1 FROM Tablex GROUP BY Col1,Col2 HAVING COUNT(*)>1) t;