October 24, 2003 at 2:59 pm
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 merger 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.
October 27, 2003 at 5:12 am
Can you post what you have so we can help based on your need?
October 27, 2003 at 6:58 am
Hi Antares686, thanks for your response.I am pasting a part of the query below.As you will see in all the joins I have used a loop join hint but I can not do it for the subquery as it does not use the ansi sql standards.Would there be any way of acomplishing it ?
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'
October 27, 2003 at 7:16 am
I'm reposting my answer from sql-server-performance.com:
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.
You should favor rethinking your SQL statement rather than using hints.
In addition this might help you http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=16048
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 27, 2003 at 3:58 pm
Hi Frank,
Thanks for your response.Query optimizer is indeed a smart piece of code and it comes out with an optimal plan in most cases.But as the complexity of query grows the plan generated by the optimizer may not be the best.For example the query above which has 30 joins took around 10 minutes to run.The query plan showed index scans(which translates to around 30 mil rows) for all tables.Putting in the loop join hints changed it to do index seeks bringing down the query execution time to 10 seconds.The need for putting in a forced index arose from my inability to put in a loop join hint in the subquery(My original question).
Edited by - siddhartha20 on 10/27/2003 3:58:44 PM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply