Regarding Distinct Keyword - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Regarding Distinct Keyword

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2008-04-05 : 05:35:54

hi guys i have a query that contains several table joins
when i run the query without select distinct x,y,z,w,.. or order by docno it takes around 20 second to finish execution, when i add select distinct x,y,z,w,.. or order by docno it ruturns the same result in just 2 seconds
is adding distict keyword or order by acts as an index for the query or what ?

.
.
.
.

here is my query :

SELECT distinct p.indocno,p.CHAR_FIELD2_AR, p.CHAR_FIELD1, p.REVISION_NO, CAST(p.INDOCNO AS int) AS INDOCNO, p.CHAR_FIELD3, p.CHAR_FIELD7_AR, T.DESCRIPTION,J.DESCRIPTION AS [Section], p.SUBJECT
FROM dbo.TECHNICAL_MAIN p INNER JOIN
(SELECT MAX(revision_no) d, char_field1 c, char_field2_ar e, subcat_id j
FROM technical_main m
WHERE revision_no IN ('0', '1', '2')
GROUP BY char_field1, char_field2_ar, subcat_id) b
ON p.REVISION_NO = b.d AND p.CHAR_FIELD1 = b.c AND p.CHAR_FIELD2_AR = b.e AND p.REVISION_NO IN ('0', '1', '2')
INNER JOIN dbo.CUST_HIERARCHY_LOOKUP T ON p.CHAR_FIELD7_AR = T.ID
INNER JOIN dbo.CUST_HIERARCHY_LOOKUP J ON p.CHAR_FIELD3_AR = J.ID AND p.SUBCAT_ID = b.j

Good luck for all the folks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-05 : 05:50:59

To get an idea about it run the query with and without ORDER BY,DISTINCT along with Execution plan option enabled and have a look at the execution plans to spot the differences. You can see whether it involves any kind of index usage from execution plan.

ann06
Posting Yak Master

171 Posts

Posted - 2008-04-05 : 07:59:47

Execution plan is not showing indexing but for the one without orderby or distict the parent of the tree is starting with computer scalar 6% then hatch match 21%.... but for the one with distinct or order by the parent node of the tree will start with
sort cost 25% then computer scalar 0% then hatch match 17 %...
any help regarding this??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-05 : 14:24:03

Any higer cost steps in plan without order by/distinct?

ann06
Posting Yak Master

171 Posts

Posted - 2008-04-06 : 08:04:22

This is the plan with order by execution takes 3 secondes

|--Sort(ORDER BY:([Expr1006] ASC))
|--Compute Scalar(DEFINE:([Expr1006]=Convert([p].[INDOCNO])))
|--Hash Match(Inner Join, HASH:([Expr1001], [m].[CHAR_FIELD1], [m].[CHAR_FIELD2_AR], [m].[SUBCAT_ID])=([p].[REVISION_NO], [p].[CHAR_FIELD1], [p].[CHAR_FIELD2_AR], [p].[SUBCAT_ID]), RESIDUAL:((([Expr1001]=[p].[REVISION_NO] AND [m].[CHAR_FIELD1]=
|--Stream Aggregate(GROUP BY:([m].[CHAR_FIELD1], [m].[CHAR_FIELD2_AR], [m].[SUBCAT_ID]) DEFINE:([Expr1001]=MAX([m].[REVISION_NO])))
| |--Sort(ORDER BY:([m].[CHAR_FIELD1] ASC, [m].[CHAR_FIELD2_AR] ASC, [m].[SUBCAT_ID] ASC))
| |--Filter(WHERE:((((((((((((((((((((((((([m].[REVISION_NO]='25' OR [m].[REVISION_NO]='24') OR [m].[REVISION_NO]='23') OR [m].[REVISION_NO]='22') OR [m].[REVISION_NO]='21') OR [m].[REVISION_NO]='20') OR [m].[REVISION_NO]='19') OR
| |--Clustered Index Scan(OBJECT:([EFILING].[dbo].[TECHNICAL_MAIN].[PK_TECHNICAL_MAIN] AS [m]))
|--Hash Match(Inner Join, HASH:(

.[ID])=([Expr1016]), RESIDUAL:([Expr1016]=

.[ID]))
|--Clustered Index Scan(OBJECT:([EFILING].[dbo].[CUST_HIERARCHY_LOOKUP].[PK_CUST_HIERARCHY_LOOKUP] AS

))
|--Compute Scalar(DEFINE:([Expr1016]=Convert([p].[CAT_ID])))
|--Hash Match(Inner Join, HASH:([Expr1017])=([p].[CHAR_FIELD7_AR]), RESIDUAL:([p].[CHAR_FIELD7_AR]=[Expr1017]))
|--Compute Scalar(DEFINE:([Expr1017]=Convert([T].[ID])))
| |--Clustered Index Scan(OBJECT:([EFILING].[dbo].[CUST_HIERARCHY_LOOKUP].[PK_CUST_HIERARCHY_LOOKUP] AS [T]))
|--Hash Match(Inner Join, HASH:([Expr1018])=([p].[CHAR_FIELD3_AR]), RESIDUAL:([p].[CHAR_FIELD3_AR]=[Expr1018]))
|--Compute Scalar(DEFINE:([Expr1018]=Convert([J].[ID])))
| |--Clustered Index Scan(OBJECT:([EFILING].[dbo].[CUST_HIERARCHY_LOOKUP].[PK_CUST_HIERARCHY_LOOKUP] AS [J]))
|--Hash Match(Inner Join, HASH:([Expr1007])=([p].[REVISION_NO]), RESIDUAL:([p].[REVISION_NO]=[Expr1007]))
|--Sort(DISTINCT ORDER BY:([Expr1007] ASC))
| |--Constant Scan
|--Clustered Index Scan(OBJECT:([EFILING].[dbo].[TECHNICAL_MAIN].[PK_TECHNICAL_MAIN] AS [p]))

================================================
this is the plan without order by execution takes only 23 seconds

|--Compute Scalar(DEFINE:([Expr1006]=Convert([p].[INDOCNO])))
|--Hash Match(Inner Join, HASH:([Expr1001], [m].[CHAR_FIELD1], [m].[CHAR_FIELD2_AR], [m].[SUBCAT_ID])=([p].[REVISION_NO], [p].[CHAR_FIELD1], [p].[CHAR_FIELD2_AR], [p].[SUBCAT_ID]), RESIDUAL:((([Expr1001]=[p].[REVISION_NO] AND [m].[CHAR_FIELD1]=[p].[
|--Stream Aggregate(GROUP BY:([m].[CHAR_FIELD1], [m].[CHAR_FIELD2_AR], [m].[SUBCAT_ID]) DEFINE:([Expr1001]=MAX([m].[REVISION_NO])))
| |--Sort(ORDER BY:([m].[CHAR_FIELD1] ASC, [m].[CHAR_FIELD2_AR] ASC, [m].[SUBCAT_ID] ASC))
| |--Filter(WHERE:((((((((((((((((((((((((([m].[REVISION_NO]='25' OR [m].[REVISION_NO]='24') OR [m].[REVISION_NO]='23') OR [m].[REVISION_NO]='22') OR [m].[REVISION_NO]='21') OR [m].[REVISION_NO]='20') OR [m].[REVISION_NO]='19') OR [m].[
| |--Clustered Index Scan(OBJECT:([EFILING].[dbo].[TECHNICAL_MAIN].[PK_TECHNICAL_MAIN] AS [m]))
|--Hash Match(Inner Join, HASH:(

.[ID])=([Expr1016]), RESIDUAL:([Expr1016]=

.[ID]))
|--Clustered Index Scan(OBJECT:([EFILING].[dbo].[CUST_HIERARCHY_LOOKUP].[PK_CUST_HIERARCHY_LOOKUP] AS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources