Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from serverfault.com Sep 18 '13 at 10:31

This question came from our site for professional system and network administrators.

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 ? –  ypercube Sep 18 '13 at 10:34
2  
Can you also include the CREATE TABLE statement and the indexes of table? –  ypercube Sep 18 '13 at 10:36
2  
Also how many rows are in the table? Do you really need to return 300k rows to the client? –  Martin Smith Sep 18 '13 at 10:42
    
What does this query return SELECT COUNT(*) FROM (SELECT 1 FROM Tablex GROUP BY Col1,Col2 HAVING COUNT(*)>1) t; –  ypercube Sep 18 '13 at 11:14
    
what does "behandelin + CHAR(13) + CHAR(10) behandelin" do? –  Pieter B Sep 18 '13 at 14:22

3 Answers 3

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.

share|improve this answer
1  
Looks like a good plan. See the SQL-Fiddle –  ypercube Sep 19 '13 at 6:55

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.

share|improve this answer

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);
share|improve this answer
3  
You "Cannot specify included columns for a clustered index." –  Mikael Eriksson Sep 18 '13 at 17:02
1  
Yeah, because by definition, all the columns are included in the CI. –  ypercube Sep 19 '13 at 5:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.