June 17, 2009 at 5:32 am
HEre is an example of what I mean:
create table employee
(empno int,
name varchar(100),
Office varchar(100))
create table Posting
(
ser int,
empno int,
office varchar(100),
startdate datetime
)
Insert into employee
select 1,'pradeep','mumbai'
union
select 2,'kumar','pune'
union
select 3, 'singh','bangalore'
insert into posting
select 1,1,'delhi', getdate()-200
union
select 2,1,'noida',getdate()-100
union
select 3,1,'mumbai',getdate()
union
select 4,2,'pune', getdate()
union
select 5,2,'chennai',getdate()-100
union
select 6,2,'bangalore',getdate()-150
--MY SOLUTION
;WITH CTE as
(
SELECT ser,
Office,
Empno,
startdate,
ROW_NUMBER() over (PARTITION BY EmpNo ORDER BY startdate DESC) as orderbY
FROM posting
)
select emp.empno, emp.name, posting.office, posting.startdate
from employee emp
Inner Join CTE Posting on emp.empno=posting.empno
WHERE orderbY = 1
--ORGINAL SOLUTION
select emp.empno, emp.name, posting.office, posting.startdate
from employee emp Inner Join Posting
on emp.empno=posting.empno
where posting.ser in (select max(ser) from posting group by empno)
Here are the stats for the two examples as well:
You will see that the solution using the IN statement has more logical reads and a highier scan count:
--QUERY ONE
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'employee'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posting'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
--QUERY 2
Table 'Worktable'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posting'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'employee'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 17, 2009 at 5:36 am
Christopher Stobbs (6/17/2009)
My other concern is that using the MAX on the "Identity" of the postings table is prob not a good idea as this does not mean 100% that you gonna get the MAX date along with it.
That will happen only if there is a manual insert in the Identity column. For example, if i'm working in mumbai today and in Pune tmr, the identity value will be higher for pune record right?
Or am i missing something here:hehe:
Sorry I'm not trying to be funny or Arsy but just want the OP to understand the query that they going to be using.
No, its good for OP to get better grasping of this:-).
June 17, 2009 at 5:40 am
That will happen only if there is a manual insert in the Identity column. For example, if i'm working in mumbai today and in Pune tmr, the identity value will be higher for pune record right?
Thats 100%. Yeah it's only for manual inputs, but in does happen in some systems. Prob not in this example you right, but thought I would point it out.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 17, 2009 at 5:40 am
Great analysis Christopher!! in fact subqueries are more often slower than not.
and yeah, I'm not that proficient in CTE 😉
Got something to learn here. Thanks 🙂
June 17, 2009 at 5:43 am
Funny you say that.
Very often, using an IN or an EXISTS will out perform a JOIN (NOT ALWAYS).
It's just something that needs to be tested and retried I guess.
The CTE is in fact a "subquery" I just like writing CTE's cause it looks neater...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 17, 2009 at 5:47 am
My next task would be learn CTE 😎 I've seen many solutions using CTE here.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply