aadharjoshi (10/10/2012)
What OPTION (RECOMPILE, QUERYTRACEON 8649) will do when i add it to query?eg..
SELECT COUNT(T.CompanyID),
COUNT(UserDataID)
FROM TargetsBeforeCurrentCriterion AS T
LEFT OUTER JOIN [UserData].dbo.[UserData] AS UD ON [UD].[LocalIdentifierID] = <@LocalIdentifierID>
AND [UD].[IsActive] = 1
AND T.CompanyID = UD.CompanyID
WHERE T.CompanyID IN (SELECT CompanyID
FROM new.CompanyIndex AS CI
INNER JOIN
UserData.dbo.CriteriaDistribution AS CD
ON [CD].[SegmentNo] = CI.SegmentNo AND CI.[CriteriaID] = CD.[CriteriaID]
WHERE [LocalIdentifierID] = 1
AND CD.[CriteriaID] = 1
AND isExcluded = ''FALSE'')
OPTION (RECOMPILE, QUERYTRACEON 8649)
===========================
I assure it works for parallel execution but what other benefit it can have?
If the table TargetsBeforeCurrentCriterion has dupes on CompanyID, then the results from this query will be meaningless. You could try something like this instead:
;WITH PartialAgg AS (
SELECT
T.CompanyID,
MAX(UD.UserCount)
FROM TargetsBeforeCurrentCriterion AS T
INNER JOIN new.CompanyIndex AS CI
ON CI.CompanyID = T.CompanyID
INNER JOIN UserData.dbo.CriteriaDistribution AS CD
ON [CD].[SegmentNo] = CI.SegmentNo
AND CI.[CriteriaID] = CD.[CriteriaID]
LEFT OUTER JOIN (
SELECT CompanyID, UserCount = COUNT(UserDataID)
FROM [UserData].dbo.[UserData]
WHERE [LocalIdentifierID] = @LocalIdentifierID
AND [IsActive] = 1
GROUP BY CompanyID
) UD ON T.CompanyID = UD.CompanyID
WHERE [LocalIdentifierID] = 1
AND CD.[CriteriaID] = 1
AND isExcluded = 'FALSE'
GROUP BY T.CompanyID
)
SELECT COUNT(CompanyID),
SUM(UserDataID)
FROM PartialAgg
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]