Simple select is taking too long

Question

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

Answer 1

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.

Answer 2

Try this:

Select Distinct Col1,Col2,Col3 + CHAR(13) + CHAR(10) Col3
From Table
Order by Col1,Col2
Option (FAST 1)

This should modify your execution plan. If it doesn't help could you post your plan for both this query and your original query (minus the Option line). Actually, eitherway I'd be intrigued to see this data.

Answer 3

For the columns that are normally too large for an index key, you may be able to gain some benefits of indexing by including them in an index. Use the Query as below,

     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) Include(Col3);