T-SQL 2 versions of the same query: Why one runs in 10 seconds, the other in 206 seconds (table variable vs subquery)

The relevant schema for this database is there are Jobs which have Job Phases which have DailyInfos. I wrote a function that takes the DailyInfoID and returns a bunch of data related to that Daily. The idea is that if I want Job Phase To Date values, I would call the function for every DailyInfo in that JobPhase by using cross apply on a query that returns all the DailyInfoIDs for that JobPhase. This works well on the JobPhase level.

Now when I go to the Job level, the query runs MUCH slower. Let's say there are 5 phases in a job, if I run the Job Phase to Date query 5 times for each Job Phase, it runs in a reasonable amount of time. However if I run the first Job To Date query below, it takes MUCH longer. The second version of the query, which uses a table variable instead of a subquery, takes the correct amount of time (ie the sum of the 5 Job Phase To Date queries). Why does the table variable version take so much less time and # rea开发者_如何转开发ds than the subquery version?

Version 1 - Duration: 210,225ms CPU: 206,203ms Reads: 38,737,658

SELECT
    di.DailyInfoID,
    DailyCycleTimes.NetHaulCY,
    DailyCycleTimes.PayCY,
    DailyCycleTimes.DigCY,
    DailyCycleTimes.FillCY,

    DailyCycleTimes.DelayMinutes,
    DailyCycleTimes.PumpMinutes,
    DailyCycleTimes.TurnMinutes,
    DailyCycleTimes.SailToMinutes,
    DailyCycleTimes.SailFromMinutes,
    DailyCycleTimes.ConnectMinutes,
    DailyCycleTimes.DisconnectMinutes,
    DailyCycleTimes.DischargeMinutes
FROM
(
    SELECT di.DailyInfoID
    FROM DailyInfo di
    INNER JOIN JobPhase jp ON jp.JobPhaseID = di.JobPhaseID
    INNER JOIN Job j ON j.JobID = jp.JobID
    WHERE j.JobID = @JobID
)di
CROSS APPLY calc.fGetDailyCycleTimes(di.DailyInfoID) DailyCycleTimes

Version 2 - Duration: 9,654 CPU: 9,593 Reads: 2,039,088

DECLARE @DailyInfo table(DailyInfoID int)

INSERT INTO @DailyInfo
SELECT di.DailyInfoID
FROM DailyInfo di
INNER JOIN JobPhase jp ON jp.JobPhaseID = di.JobPhaseID
INNER JOIN Job j ON j.JobID = jp.JobID
WHERE j.JobID = @JobID

SELECT  
    di.DailyInfoID,
    DailyCycleTimes.NetHaulCY,
    DailyCycleTimes.PayCY,
    DailyCycleTimes.DigCY,
    DailyCycleTimes.FillCY,

    DailyCycleTimes.DelayMinutes,
    DailyCycleTimes.PumpMinutes,
    DailyCycleTimes.TurnMinutes,
    DailyCycleTimes.SailToMinutes,
    DailyCycleTimes.SailFromMinutes,
    DailyCycleTimes.ConnectMinutes,
    DailyCycleTimes.DisconnectMinutes,
    DailyCycleTimes.DischargeMinutes
FROM @DailyInfo di
CROSS APPLY calc.fGetDailyCycleTimes(di.DailyInfoID) DailyCycleTimes

---

Check the actual execution plan for each in Management Studio - that will tell you where the cost is attributed for each.

---

The table variable can't be optimized by the query engine, where the underlying table (when used directly) can.