SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OPTION (RECOMPILE, QUERYTRACEON 8649)


OPTION (RECOMPILE, QUERYTRACEON 8649)

Author
Message
Aadhar Joshi
Aadhar Joshi
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1860 Visits: 451
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225165 Visits: 46321
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ChrisM@home
ChrisM@home
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5210 Visits: 10607
aadharjoshi (10/10/2012)
What OPTION (RECOMPILE, QUERYTRACEON 8649) will do when i add it to query?
...


RECOMPILE

QUERYTRACEON 8649

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.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225165 Visits: 46321
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ChrisM@home
ChrisM@home
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5210 Visits: 10607
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




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5001 Visits: 3232
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
on t.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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225165 Visits: 46321
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5001 Visits: 3232
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
ChrisM@home
ChrisM@home
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5210 Visits: 10607
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.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225165 Visits: 46321
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search