SQL Server Performance Forum – Threads Archive

Join hint in subquery

Hi,
I have a big query with two correlated subqueries in it.Sql server’s execution plan shows that its using a full index scan and a merge join for those two subqueries.I want to put the query hints in the subquery to loop join but since query hints can be put in only the ansi join format and the subqueries are written in old style joins I am having problems finding the location for these hints.Any help in this regard will be much appreciated.
TIA.

I you know the table, use Query Analyzer, Tools, Managment Statistics. I donÂ´t this show you all statistics. SELECT
RTRIM(object_name(I.id)) tablename,
RTRIM(name) name,
DATALENGTH (statblob) size,
STATS_DATE (I.id, I.indid) last_updated
FROM
sysindexes as I
WHERE
OBJECTPROPERTY(I.id, N’IsUserTable’) = 1 AND
INDEXPROPERTY (I.id , name , ‘IsAutoStatistics’ ) = 1 and
/* DATALENGTH (statblob) is null */
order by tablename,
last_updated ASC
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

Hi Luis thanks for your response.I have no problems with the statisics they are upto date.I am pasting part of the query for more clarification.As you will see I have specified loop join hints (code in blue) for all table joins.I want to do the same for the tables in the subquery but since there is no explicit join clause in the subquery I am unable to specify a hint there. inner loop join
Payment with(nolock)
on
OrderPayment.PaymentId = Payment.PaymentID
and Payment.Status <> ‘0’
and OrderPayment.Status <> ‘0’
inner loop join
CardPayment with(nolock)
on
OrderPayment.PaymentId = CardPayment.PaymentId
and CardPayment.Status <> ‘0’
and OrderPayment.Status <> ‘0’
left outer loop join
InstallmentPayment with(nolock)
on
OrderPayment.PaymentId = InstallmentPayment.PaymentId
and OrderPayment.Status <> ‘0’
and InstallmentPayment.Status <> ‘0’
inner loop join
SalesAreaView with(nolock)
on
SalesAreaView.SalesCounty =( Select
top 1 SecondaryPartyId from
PartyRelationship with(nolock,index(NC_PartyRelationship_SystemsessionId ))
where
PartyRelationship.PrimaryPartyId = CorporateOrder.PartyId
and PartyRelationship.SystemSessionId = SalesOrder.SystemSessionId
and PartyRelationship.PrimaryPartyRoleCode = ‘PR’
and PartyRelationship.PartyRelationshipCode = ‘IOC’
and PartyRelationship.Status <> ‘0’
order by
PartyRelationship.CreateDate)
inner loop join
SalesAreaView OrderView with(nolock)
on
OrderView.SalesCounty =( Select
top 1 SecondaryPartyId
from
PartyRelationship with(nolock ,index(NC_PartyRelationship_SystemsessionId ))
where
PartyRelationship.PrimaryPartyId = CorporateOrder.PartyId
and PartyRelationship.SystemSessionId = SalesOrder.SystemSessionId
and PartyRelationship.PrimaryPartyRoleCode = ‘PR’
and PartyRelationship.PartyRelationshipCode = ‘IOO’
and PartyRelationship.Status <> ‘0’
order by
PartyRelationship.CreateDate)inner loop join
Party with(nolock)
on
Party.PartyId =CorporateOrder.PartyId
and Party.Status <> ‘0’
and CorporateOrder.Status <> ‘0’

Hi ya, You could try replacing the subselect with a call to a function… create function fn_get_topsecondaryparty(
— the four parameters
) returns integer
as
begin declare @secondary_id int Select top 1
@secondary_id = SecondaryPartyId
from PartyRelationship with(nolock)
where PartyRelationship.PrimaryPartyId = @p_PrimaryPartyId
and PartyRelationship.SystemSessionId = @p_SystemSessionId
and PartyRelationship.PrimaryPartyRoleCode = @p_PrimaryPartyRoleCode
and PartyRelationship.PartyRelationshipCode = @p_PartyRelationshipCode
and PartyRelationship.Status <> ‘0’
order by
PartyRelationship.CreateDate return @secondary_id
end and then in the original query on
OrderView.SalesCounty = dbo.fn_get_topsecondaryparty( CorporateOrder.PartyId,, SalesOrder.SystemSessionId, ‘PR’, ‘IOO’ )
inner join Party with(nolock)
on … The index should ideally be on
PrimaryPartyId
SystemSessionId
PrimaryPartyRoleCode
PartyRelationshipCode
Status
CreateDate
SecondaryPartyId with the first four ordered so that the column that is most selective is first, etc… Cheers
Twan
PS I’d avoid forcing indexes unless you absolutely must…

Again, piggy-backing on Twan. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I would not force the query optimizer to do something. Hint is a misleading word in this context I think. What might seem appropriate for the now given data, could lead to suboptimal query performance once data changes significantly. The query optimizer is a very smart piece of code. Frank http://www.insidesql.de</a>

]]>