October 10, 2012 at 11:26 pm
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?
October 11, 2012 at 2:11 am
Firstly, why are you adding hints at all? What is the purpose, what is the reasoning behind the hints?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 11, 2012 at 2:58 am
aadharjoshi (10/10/2012)
What OPTION (RECOMPILE, QUERYTRACEON 8649) will do when i add it to query?...
If you suspect you are experiencing performance problems with one or more of your queries, then post the actual execution plan as an attachment (.sqlplan file).
As Gail suggests, it's pointless using these query hints unless you can anticipate that they may work. RECOMPILE is most frequently used if different values passed to parameters used by a query can cause very different plans to be generated. QUERYTRACEON 8649 encourages the optimiser to use a parallel plan.
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]
October 11, 2012 at 3:01 am
ChrisM@home (10/11/2012)
As Gail suggests, it's pointless using these query hints unless you can anticipate that they may work.
And adding hints to a query when you don't know what they do is worse than pointless.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 11, 2012 at 3:02 am
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]
October 11, 2012 at 4:03 am
In regards to the query wouldnt this run using a more optimal plan
;With Cte_Newcompany(NewCompanyId)
AS
(
SELECT CompanyID
FROM new.CompanyIndex AS CI
INNER JOIN UserData.dbo.CriteriaDistribution AS CD
ON CI.SegmentNo =[CD].[SegmentNo]
AND CI.[CriteriaID] = CD.[CriteriaID]
AND CD.[CriteriaID] = 1
WHERE [LocalIdentifierID] = 1
AND isExcluded = 'FALSE'
)
Select
Count(t.companyId)
,Count(UserDataId)
From
TargetsBeforeCurrentCriterion AS T
INNER JOIN Cte_Newcompany Nc
ont.CompanyId=Nc.NewCompanyId
LEFT OUTER JOIN [UserData].dbo.[UserData] as UD
ON t.CompanyId=ud.CompanyId
AND ud.IsActive=1
where
[UD].[LocalIdentifierID] = @LocalIdentifierID
as you're getting rid of a potentially expensive IN statement and using an Inner join from a CTE, the variable is no longer on the join and in the Where clause where it really belongs.
I'm curious if this is better or worse than the original, or matches ChrisM's.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 11, 2012 at 4:41 am
IN is not an expensive operation. For matching rows, it's cheaper than join.
Moving a filter from a join to the where when you have an outer join changes the logic of the query and likely the results.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 11, 2012 at 4:49 am
GilaMonster (10/11/2012)
IN is not an expensive operation. For matching rows, it's cheaper than join.Moving a filter from a join to the where when you have an outer join changes the logic of the query and likely the results.
Interesting about the IN, I've always found them to be more expensive than Joins and so avoided them.
Mentally noted about the Outer Join and filter moving, does it have the same effect on an Inner Join.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 11, 2012 at 4:55 am
Jason-299789 (10/11/2012)
...as you're getting rid of a potentially expensive IN statement and using an Inner join from a CTE, the variable is no longer on the join and in the Where clause where it really belongs.
The IN construct in the OP's query is probably there to preserve the cardinality of the main part of the query with respect to CompanyID, which would make it functionally different to an IJ.
I'm curious if this is better or worse than the original, or matches ChrisM's.
The rewrite I posted isn't for performance - it's for accuracy.
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]
October 11, 2012 at 5:01 am
Jason-299789 (10/11/2012)
GilaMonster (10/11/2012)
IN is not an expensive operation. For matching rows, it's cheaper than join.Moving a filter from a join to the where when you have an outer join changes the logic of the query and likely the results.
Interesting about the IN, I've always found them to be more expensive than Joins and so avoided them.
The difference isn't usually noticable, but it's there.
http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/
http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup/
Also they are not semantically equivalent. Join can result in duplicate rows, IN can not.
Mentally noted about the Outer Join and filter moving, does it have the same effect on an Inner Join.
With an inner join the filter has the same effect and performance whether in the join or the where.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 11, 2012 at 5:18 am
Thank you all..!
I appreciate your help.. Most probably i can remove in clause and use inner join..
October 11, 2012 at 5:21 am
aadharjoshi (10/11/2012)
Most probably i can remove in clause and use inner join..
Why? To make the query slightly less efficient and possibly duplicate rows?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 11, 2012 at 5:57 am
GilaMonster (10/11/2012)
Jason-299789 (10/11/2012)
GilaMonster (10/11/2012)
IN is not an expensive operation. For matching rows, it's cheaper than join.Moving a filter from a join to the where when you have an outer join changes the logic of the query and likely the results.
Interesting about the IN, I've always found them to be more expensive than Joins and so avoided them.
The difference isn't usually noticable, but it's there.
http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/
http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup/
Also they are not semantically equivalent. Join can result in duplicate rows, IN can not.
Mentally noted about the Outer Join and filter moving, does it have the same effect on an Inner Join.
With an inner join the filter has the same effect and performance whether in the join or the where.
Thanks for the links Gail, they're an intesting read, and flys in the the face of almost everything that was drilled into me by seniors when I started coding T-SQL 12-13 years ago.
One last question, was this always the case or is there a case to say that performance enhancements with the query engine over the last few revisions have caused this to blur the lines more than say under SQL 6.5/2000?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 11, 2012 at 6:12 am
GilaMonster, Its extremely necessary to improve query performance..even if it process execution paralleled.
October 11, 2012 at 6:27 am
aadharjoshi (10/11/2012)
Thank you all..!I appreciate your help.. Most probably i can remove in clause and use inner join..
Folks here would be happy to help you tune your query. Can you post the actual plan as a .sqlplan attachment?
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]
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply