Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Different ways to find DISTINCT values - Faster methods

Nov 27 2012 12:00AM by Madhivanan   

I have posted a blog post about Different ways to find DISTINCT values where I have shown six different ways to find DISTINCT values. Here is the analysis of how long each method takes.

Create a temporary table named #sales_details

create table #sales_details(item_id int not null)

Populate one million data into this table

insert into #sales_details (item_id)
select 
	top 1000000 
		checksum(newid())%10000
from 
	sys.objects as so1 cross join
	sys.objects as so2 cross join
	sys.objects as so3 cross join
	sys.objects as so4	

Test various methods to find DISTINCT values

SET STATISTICS TIME ON 
print '======================================================'
Print 'Method 1 starts'
print '======================================================'
select distinct item_id from #sales_details 
print '======================================================'
Print 'Method 2 starts'
print '======================================================'

select item_id from #sales_details 
Group by item_id
print '======================================================'
Print 'Method 3 starts'
print '======================================================'
select item_id from #sales_details 
UNION
select item_id from #sales_details 
print '======================================================'
Print 'Method 4 starts'
print '======================================================'
select item_id from #sales_details 
UNION
select item_id from #sales_details where 1=0
print '======================================================'
Print 'Method 5 starts'
print '======================================================'
select item_id from #sales_details 
UNION
select 0 where 1=0
print '======================================================'
Print 'Method 6 starts'
print '======================================================'
select item_id from
(
	select row_number() over (partition by item_id order by item_id) as sno,* from #sales_details 
) as t
where sno=1
print '======================================================'
Print 'Method 7 starts' Pointed by Dwain camps 
print '======================================================'
;WITH    RecursiveCTE
AS      (
        SELECT  data = MIN(T.item_id)
        FROM    #sales_details T
        UNION   ALL
        SELECT  R.item_id
        FROM    (
                -- A cunning way to use TOP in the recursive part of a CTE :)
                SELECT  T.item_id,
                        rn = ROW_NUMBER() OVER (ORDER BY T.item_id)
                FROM    #sales_details T
                JOIN    RecursiveCTE R
                        ON  R.data < T.item_id
                ) R
        WHERE   R.rn = 1
        )
SELECT  *
FROM    RecursiveCTE
OPTION  (MAXRECURSION 0);
SET STATISTICS TIME OFF

Here is the output shown in tabular format

Method # Query CPU Time Elapsed Time
1 select distinct item_id from #sales_details 421 531
2 select item_id from #sales_details Group by item_id 312 462
3 select item_id from #sales_details UNION select item_id from #sales_details 749 1125
4 select item_id from #sales_details UNION select item_id from #sales_details where 1=0 405 518
5 select item_id from #sales_details UNION select 0 where 1=0 328 460
6 select item_id from ( select row_number() over (partition by item_id order by item_id) as sno,* from #sales_details ) as t where sno=1 343 398
7 Paul White's CTE method

;WITH    RecursiveCTE
AS      (
        SELECT  data = MIN(T.item_id)
        FROM    #sales_details T
        UNION   ALL
        SELECT  R.item_id
        FROM    (
                -- A cunning way to use TOP in the recursive part of a CTE :)
                SELECT  T.item_id,
                        rn = ROW_NUMBER() OVER (ORDER BY T.item_id)
                FROM    #sales_details T
                JOIN    RecursiveCTE R
                        ON  R.data < T.item_id
                ) R
        WHERE   R.rn = 1
        )
SELECT  *
FROM    RecursiveCTE
OPTION  (MAXRECURSION 0);

468 with non clustered index

765 with clustered index

0 with clustered index with data ranges between 0 and 99

527 with non clustered index


2231 with clustered index


22 with clustered index with data ranges between 0 and 99

The performance of the method 7 that involves in recursive CTE depends on the index as well as the nature of the data. Without index, it is running for several minutes. With index it is running fast. But if there are lesser number of distinct values, it runs very fast. For example if we use checksum(newid())%100 in place of checksum(newid())%10000, method 7 outperform every other methods

Tags: 


Madhivanan
3 · 40% · 12899
12
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

6  Comments  

  • FYI, Starting recently, these posts are being sent via email two times.

    commented on Nov 27 2012 1:27AM
    dishdy
    16 · 10% · 3269
  • Thanks dishdy. I also noticed it. I will inform this to Jacob

    commented on Nov 27 2012 1:37AM
    Madhivanan
    3 · 40% · 12899
  • But what are your conclusions? Are you recommanding the usage of some methods (on specific scenarios)?

    commented on Nov 27 2012 2:22AM
    Sergejack
    41 · 4% · 1395
  • Sergejack, I would prefer either 1 or 2 because they are simple, straight-forward and almost work in all RDBMSs

    commented on Nov 27 2012 3:35AM
    Madhivanan
    3 · 40% · 12899
  • Very interesting! I learnt something new and am inspired to dig deeper into each of these methods. Thanks for sharing!

    commented on Nov 27 2012 7:08AM
    Nakul Vachhrajani
    4 · 36% · 11606
  • Thanks Nakul for your feedback

    commented on Nov 27 2012 7:29AM
    Madhivanan
    3 · 40% · 12899

Your Comment


Sign Up or Login to post a comment.

"Different ways to find DISTINCT values - Faster methods" rated 5 out of 5 by 12 readers
Different ways to find DISTINCT values - Faster methods , 5.0 out of 5 based on 12 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]