The FROM
(SELECT ROW_NUMBER () OVER (PARTITION BY task. FormId ORDER BY task. The StartDate DESC) AS rownum, dbo. TRebatesForm. RebatesFormID, dbo. TRebatesForm. DealerCode, dbo. TRebatesForm. DealerName,
(SELECT TOP (1) the EndDate
The FROM dbo. TTask
The WHERE (FormId=dbo. TRebatesForm. RebatesFormID) AND (IsFinished=1) AND (StepState & lt;> 777) the AN
(StepState & lt;> 888)
The ORDER BY EndDate DESC) AS PreEndDate,
(SELECT TOP (1) StepId
The FROM dbo. TTrack
The WHERE (FormId=dbo. TRebatesForm. RebatesFormID) AND (StepState & lt;> 1) AND (StepState & lt;> 777) AND
(StepState & lt;> 888)
The ORDER BY EndDate DESC) AS PreStepId,
(SELECT TOP (1) StepState
The FROM dbo. TTrack
The WHERE (FormId=dbo. TRebatesForm. RebatesFormID) AND (StepState & lt;> 1) AND (StepState & lt;> 777) AND
(StepState & lt;> 888)
The ORDER BY EndDate DESC) AS PreStepState,
RoleId (SELECT TOP (1)
The FROM dbo. TTrack
The WHERE (FormId=dbo. TRebatesForm. RebatesFormID) AND (StepState & lt;> 1) AND (StepState & lt;> 777) AND
(StepState & lt;> 888)
The ORDER BY EndDate DESC) AS PreRoleId,
(SELECT TOP (1) CompleteUser
The FROM dbo. TTask
The WHERE (FormId=dbo. TRebatesForm. RebatesFormID) AND (IsFinished=1) AND (StepState & lt;> 777) AND
(StepState & lt;> 888)
The ORDER BY EndDate DESC) AS PreCompleteUser
The FROM dbo. TRebatesForm
INNER JOIN dbo. TRebatesType on dbo. TRebatesForm. RebatesType=dbo. TRebatesType. TypeValue
LEFT the JOIN dbo. TRebatesCustomer on dbo. TRebatesForm. RebatesCustomerID=dbo. TRebatesCustomer. RebatesCustomerID
LEFT the JOIN tdealer d on d.D ealerCode=dbo. TRebatesForm. DealerCode
INNER JOIN TTask task ON dbo. TRebatesForm. RebatesFormID=task. FormId
LEFT the JOIN TTrack AS track ON a task. The TaskId=track. The TaskId
LEFT the JOIN [TVWTaskRebate] as TW on TW. FormId=TRebatesForm. RebatesFormID) T
WHERE rownum=1
CodePudding user response:
- 1. Put the main table in a temporary table
SELECT *
INTO # TMP
The FROM (
The SELECT ROW_NUMBER () OVER (PARTITION BY task. FormId ORDER BY task. The StartDate DESC) AS rownum, *
The FROM dbo. TRebatesForm
) T
WHERE rownum=1
- 2. Create a connection column, do a, other you add
The CREATE INDEX ix_ # # tmp_RebatesFormID ON TMP (RebatesFormID);
Query - 3.
The SELECT dbo. TRebatesForm. RebatesFormID,
Dbo. TRebatesForm. DealerCode,
Dbo. TRebatesForm. DealerName,
Tt EndDate AS PreEndDate,
Tt StepId AS PreStepId,
Tt StepState AS PreStepState,
Tt RoleId AS PreRoleId,
Tt.Com pleteUser AS PreCompleteUser
The FROM # TMP AS TRebatesForm
INNER JOIN dbo. TRebatesType
ON TRebatesForm. RebatesType=dbo. TRebatesType. TypeValue
LEFT the JOIN dbo. TRebatesCustomer
ON TRebatesForm. RebatesCustomerID=dbo. TRebatesCustomer. RebatesCustomerID
LEFT the JOIN tdealer d
ON d.D ealerCode=TRebatesForm. DealerCode
INNER JOIN TTask task
ON TRebatesForm. RebatesFormID=task. FormId
LEFT the JOIN TTrack AS track
ON a task. The TaskId=track. TaskId
LEFT the JOIN [TVWTaskRebate] AS TW
ON TW. FormId=TRebatesForm. RebatesFormID
The OUTER APPLY (
SELECT TOP (1) *
The FROM dbo. TTask
The WHERE (FormId=dbo. TRebatesForm. RebatesFormID)
AND (IsFinished=1)
AND (StepState & lt;> 777)
AND (StepState & lt;> 888)
The ORDER BY
EndDate DESC
) AS tt
- 4. Drop the temporary table
DROP TABLE # TMP
CodePudding user response:
OUTER APPLY efficiency is not high, so no original query fast, according to the specific conditions of lookup is very slowCodePudding user response:
Outer apply, but only checked once, do you want to check a lot of time,In addition, the fast unhappy, want to see the actual effect and the execution plan, you try to do to say again,
How many seconds, the modified (plus index), remember how many seconds, you posted on the actual test data,
CodePudding user response:
When the where screening data if the data volume is small, about five seconds to top value directly from the results, and the OUTER APPLY when the data volume is very small I do correlation is very time consuming, because TTask, and TTrack table data quantity is large, if detected data of how are you this fast, so very embarrassed