--------------------------------------------------------------------
--Description : UnionAll for TJOIN performance measurements
--Author      : Sameer A. Verkhedkar
--Author      : Xianjin Yang

--Date        : Dec 5, 1997
--            : Assumes that the Generated tables have been
--              properly loaded
--------------------------------------------------------------------
USE tempo
GO

DROP TABLE Emp10RES
GO

CREATE TABLE Emp10RES (EID       Integer,
                       StartDate datetime,
                       EndDate   datetime)



DECLARE @resultCount Integer, @first_time datetime , @prnvar char(20)
DECLARE @last_time datetime
DECLARE @msecs Integer

SELECT @resultCount = 0
SELECT @resultCount = (SELECT COUNT(*) FROM Emp10)
SELECT @prnvar = convert(char(20),@resultCount)
print "# of Records to Operate on : %1!", @prnvar 


SELECT @first_time = getdate()

print "Start"

  INSERT INTO Emp10RES
  SELECT E1.EID, E1.StartDate, E1.EndDate
  FROM   Emp10 E1, Emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E2.StartDate <= E1.StartDate AND
         E1.EndDate <= E2.EndDate
  UNION ALL
  SELECT E1.EID, E1.StartDate, E2.EndDate
  FROM   Emp10 E1, Emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E1.StartDate > E2.StartDate AND
         E2.EndDate < E1.EndDate AND
         E1.StartDate < E2.EndDate
  UNION ALL
  SELECT E1.EID, E2.StartDate, E1.EndDate
  FROM   Emp10 E1, Emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E2.StartDate > E1.StartDate AND
         E1.EndDate < E2.EndDate AND
         E2.StartDate < E1.EndDate
  UNION ALL
  SELECT E1.EID, E2.StartDate, E2.EndDate
  FROM   Emp10 E1, Emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E2.StartDate >= E1.StartDate AND
         E2.EndDate <= E1.EndDate AND
         NOT (E2.StartDate = E1.StartDate AND
              E2.EndDate = E1.EndDate)


SELECT @last_time = getdate()

SELECT @resultCount = (SELECT COUNT(*) FROM Emp10RES)
SELECT @prnvar = convert(char(20),@resultCount)
print "Resulting Records : %1!", @prnvar 

SELECT @msecs = datediff(millisecond, @first_time,  @last_time)
SELECT @prnvar = convert(char(20), @msecs)
print "[Emp10 TJOIN UNIONALL] Elapsed Time %1! milliseconds", @prnvar

GO
