Few queries related to performance

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

SQL Server 2005 Forums

Transact-SQL (2005)

Few queries related to performance

Author

Topic

learntsql

524 Posts

Posted - 2009-12-16 : 04:02:13

Hi..
I have few queries to ask may be basic.
1.When i write JOIN clause in select statement where should i apply the filter;whether after ON clause or WHERE clause for all kind of joins.
2.If column is computed column with alias name and have order by clause in same statement
Should i write computed column again in order by clause or alias name is enough as per performance.
eg:
select datediff(DD,EndDate,GetDate()) AS Duration
FROM Tasks
ORDER BY datediff(DD,EndDate,GetDate())
(OR)
ORDER BY Duration
which one is best.
3.If SP is used for Report generation
should i order the records in query itself using ORDER BY clause or
do in Reporting service?
any links for above.
Thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-16 : 04:12:17

1 It depends on what you want. in INNER join both are same. In OUTER joins both will give different result
2 Performancewise there is no difference
3 Sort data in Report

Madhivanan

Failing to plan is Planning to fail

learntsql

524 Posts

Posted - 2009-12-16 : 04:18:51

Thanks Madhi,
Could you please elaborate the first one.(Join clause)
any stuff related to this.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-16 : 04:21:43

Using Order by If records are already sorted by an index, the processor needs to use only the index. Otherwise, the processor has to use a temporary work table to sort the records first.
Such preliminary sorting can cause significant initial delays.

-------------------------
R...

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-12-16 : 11:41:53

#1

I guess in most cases, no difference performance-wise but look at the Execution Plans. I tried a simple query and wrote it 2 ways: 1st with WHERE and 2nd with JOIN/ON. I looked at both Execution Plans and they were identical meaning when it comes time to run the query, SQL Server's game plan in retrieving the results would be the same and therefore equal performance.

But be careful with left-outer joins involving NULLs, the results will be different between the 2!

#2

No difference. SQL Server is smart enough to see that the Order Bys are equivalent.

#3

Hard to say from a Performance point of view. Probably pretty similar either way. But I do agree with Madhivanan since the sort order is more of a presentation issue so makes more sense in the Report. And that way you could have more than one Report feeding off the same Stored Proc and each Report could sort the Data how they wish.

learntsql

524 Posts

Posted - 2009-12-16 : 23:42:51

Thanks denis,
still not clear with #1(with outer joins).
Could you plz. provide some stuff for my better understand or any links.
I want to know how the select statement behaves(executes) when i use join and whare clauses.
TIA.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-17 : 01:55:05

quote:
Originally posted by learntsql

Thanks denis,
still not clear with #1(with outer joins).
Could you plz. provide some stuff for my better understand or any links.
I want to know how the select statement behaves(executes) when i use join and whare clauses.
TIA.

Run these two

select t.* from pubs..titles as t left join pubs..titleauthor as ta
on t.title_id=ta.title_id
where t.type='business'

select t.* from pubs..titles as t left join pubs..titleauthor as ta
on t.title_id=ta.title_id
and t.type='business'

and see the result

Madhivanan

Failing to plan is Planning to fail

learntsql

524 Posts

Posted - 2009-12-17 : 05:47:39

Thanks Madhi,
Sorry but still i didnt understand
how it internally executed.
i.e,First Where clause followed by Join clause or vice versa.
Could you please expalin execution steps for both LEFT and RIGHT joins.
I am working on SQL 2005.
I tried as follows
declare @tab1 table(ID int identity,type varchar(50))
declare @tab2 table(ID int identity,type varchar(50))
insert into @tab1
select 'A'
union
select 'B'

insert into @tab2
select 'A'
union
select 'B'
union
select 'C'

select t.*
from @tab1 as t
right join @tab2 as ta
on t.id=ta.id
where t.type='A'

select t.*
from @tab1 as t
right join @tab2 as ta
on t.id=ta.id
AND t.type='A'

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-12-17 : 09:46:06

quote:
Originally posted by learntsql

Thanks denis,
still not clear with #1(with outer joins).
Could you plz. provide some stuff for my better understand or any links.
I want to know how the select statement behaves(executes) when i use join and whare clauses.
TIA.

Try these 2 queries and you'll get 2 different results:

1:

with T (C1, C2) as
  (select 1, 3),
T2(C2, C3) as
  (select 3, NULL)

select * 
from T left outer join T2
  on T.C2 = T2.C2
where 
  T2.C3 = 3

with T (C1, C2) as
  (select 1, 3),
T2(C2, C3) as
  (select 3, NULL)

select * 
from T left outer join T2 
  on T.C2 = T2.C2 and T2.C3 = 3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources