0

There are 3 tables that I need to Join with different filters.

EG:

Select 'First' as Header, * 
from A join B on A.ID=B.ID 
where A.Type=1 and B.Startdate>Getdate()

Union

Select 'Second' as Header, * 
from A join B on A.ID=B.ID 
where A.Type=2 and B.Startdate=Getdate()

Union

Select 'Third' as Header, * 
from A join B on A.ID=B.ID 
where A.Type=3 and B.Startdate<Getdate()

Is any impact will be in performance change if above rewritten as

With CTE 
as
(
    Select * 
    from A join B on A.ID=B.ID
    Where A.Type in (1,2,3)
)
Select 'First' as Header, * 
from CTE 
where Type=1 and B.Startdate>Getdate()

Union

Select 'Second' as Header, * 
from CTE 
where Type=2 and B.Startdate=Getdate()

Union

Select 'Third' as Header, * 
from CTE 
where Type=3 and B.Startdate<Getdate()

Both Table A and B have over 100k records

I have noticed, Execution Query plan seems to be same for both queries but little variation in Physical, Logical and read-ahead reads with execution timing differing in milliseconds.

Does the above CTE better in performance wise than select mentioned at the start of the segment or not. Is any other methods to get above results with better performance.

James Z
  • 12,209
  • 10
  • 24
  • 44
JayaPrakash
  • 179
  • 1
  • 6
  • I would expect both queries to perform exactly the same (which seems to be confirmed by your comment *"Execution Query plan seems to be same for both queries"*). Do you consistently get a difference in logical and physical reads or is it just on the first execution? If it is only on the first, you should disregard these results and run again after the data is in the memory buffer. – GarethD Feb 22 '17 at 09:29
  • 1
    With regard to a better solution it would really depend on the indexes you have on the table, assuming that you have an index on `(Type, StartDate)` then this is probably more or less optimal. I would definitely use `UNION ALL` over `UNION` though, since your predicates eliminate the possibility of duplicates in each query, you may as well give the optimiser as much chance as possible to skip this step. – GarethD Feb 22 '17 at 09:30
  • 1
    Also, lakh is a fairly culture specific term, it would be better to use internationally known units, e.g. 100,000 records, just for clarity. – GarethD Feb 22 '17 at 09:35

0 Answers0