2

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

6
  • 3
    Is this for SQL-Server? If yes, which version? Why the Distinct, do you have rows with same values in Col1, Col2 and Col3 ? Sep 18, 2013 at 10:34
  • 2
    Can you also include the CREATE TABLE statement and the indexes of table? Sep 18, 2013 at 10:36
  • 2
    Also how many rows are in the table? Do you really need to return 300k rows to the client? Sep 18, 2013 at 10:42
  • What does this query return SELECT COUNT(*) FROM (SELECT 1 FROM Tablex GROUP BY Col1,Col2 HAVING COUNT(*)>1) t; Sep 18, 2013 at 11:14
  • what does "behandelin + CHAR(13) + CHAR(10) behandelin" do?
    – Pieter B
    Sep 18, 2013 at 14:22

3 Answers 3

2

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.

1
0

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.

-2

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);
2
  • 3
    You "Cannot specify included columns for a clustered index." Sep 18, 2013 at 17:02
  • 1
    Yeah, because by definition, all the columns are included in the CI. Sep 19, 2013 at 5:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.