Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

OPTION (RECOMPILE, QUERYTRACEON 8649) Expand / Collapse
Author
Message
Posted Wednesday, October 10, 2012 11:26 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:52 AM
Points: 1,608, Visits: 375
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?
Post #1371215
Posted Thursday, October 11, 2012 2:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 39,866, Visits: 36,207
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 2008, MVP
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

Post #1371264
Posted Thursday, October 11, 2012 2:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:40 AM
Points: 1,030, Visits: 6,674
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
Post #1371295
Posted Thursday, October 11, 2012 3:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 39,866, Visits: 36,207
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 2008, MVP
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

Post #1371297
Posted Thursday, October 11, 2012 3:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:40 AM
Points: 1,030, Visits: 6,674
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
Post #1371298
Posted Thursday, October 11, 2012 4:03 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:09 AM
Points: 878, Visits: 2,395
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
Post #1371324
Posted Thursday, October 11, 2012 4:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 39,866, Visits: 36,207
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 2008, MVP
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

Post #1371345
Posted Thursday, October 11, 2012 4:49 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:09 AM
Points: 878, Visits: 2,395
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
Post #1371348
Posted Thursday, October 11, 2012 4:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:40 AM
Points: 1,030, Visits: 6,674
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
Post #1371353
Posted Thursday, October 11, 2012 5:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 39,866, Visits: 36,207
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 2008, MVP
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

Post #1371355
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse