Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql query optimization for deduplication. Expand / Collapse
Author
Message
Posted Friday, June 21, 2013 8:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 21, 2013 10:00 AM
Points: 2, Visits: 20
Hi All,
I have a query for de duplicating messages based on unique hash and then sorting and paging it, i was wondering how I can optimize it better.

Hash and Name column in the Message table are non clustered indexes defined with storeid, folderid, Hash or Name(either)

The only thing i see problematic is an index scan but that is same even if i remove the deduplication cte and do the main query on the Message table, i think that is more to do with select *

Any help will be greatly appreciated.


With DeduplicatedMessages as 
(
select * from Message
JOIN
(
SELECT MAX(MessageId) as UniqueMessageId
FROM Message
where StoreId=210 and FolderId=24541
GROUP BY Hash
) as UniqueMessages ON
Message.Messageid = UniqueMessages.UniqueMessageId
)

select * from (select ROW_NUMBER() over (order by Name ASC) as RI, * from DeduplicatedMessages) as SUB where sub.RI >= 0 and sub.RI <=1000


Thanks
Post #1466235
Posted Friday, June 21, 2013 9:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1466239
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse