Sql-server – Simple select is taking too long

performancequery-performancesql server

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
enter image description here

Best Answer

If you only need to know that a row is distinct, and don't need the actual contents of col3, then perhaps returning the hash of col3 would speed up the query?

You could even perhaps pre-compute the hash using a calculated column so that you aren't computing the hash on the fly.

If you do need the contents of col3, but have a lot of duplicates of col1+col2+col3, then it still may be beneficial to work with the hash to remove duplicates as a sub-query, then only return the col3 contents for the distinct rows.

Related Question