April 23, 2002 at 3:01 pm
I have a complex query that I am trying to speed up. The most expensive operation in it is a Merge/Inner join that joins a large result set with a very small one. I am trying to get it to run a nested loop join, but when I do it slows down the query massively. I think that the optimizer is using the large result set as the outer input. Is there any way to get the optimizer to use the other, small result set as the outer input so that the nested loop runs faster?
April 23, 2002 at 3:05 pm
Could you post a script for illustration?
April 23, 2002 at 3:12 pm
The script is quite long, but here it is if you want to look at it.
The problematic join occurs in the WHERE NOT EXISTS subquery's first 2 tables; dbo.FaSched and dbo.FaStudentAid. FaSched is the large table and FaStudentAid is the small one.
Here is the script. It is used to create a view:
SELECT RTRIM(syStudent.LastName)
+ ', ' + RTRIM(syStudent.FirstName)
+ ' ' + syStudent.MI AS StudentName,
syStudent.SyStudentID,
syStudent.SSN,
' ' AS PrintID,
syStudent.StuNum,
SyCampus.PellID,
SyCampus.Descrip AS Campus,
AdProgram.Code AS programCode,
AdProgram.Descrip AS programDescrip,
FaStudentPell.AwardYear,
FaStudentPell.PellAmount AS EligAmt,
FaStudentPell.Model,
FaStudentPell.DateISIRRcvd,
FaStudentPell.PellPaidEFC AS EFC,
FaStudentPell.TransactionID AS PellTransactionID,
FaStudentPell.VerifStatus,
FaStudentPell.PellEnrollmentStatus,
rpt_faPELLtk0sub_vw.AmountPackaged,
rpt_faPELLtk0sub_vw.AmountDisbursed,
rpt_faPELLtk0sub_vw.AmountRefunded,
rpt_faPELLtk0sub_vw.PellCostOfAttend,
rpt_faPelltk0sub_vw.EnrollProgramCode,
rpt_faPelltk0sub_vw.EnrollProgramDescrip,
rpt_faPelltk0sub_vw.Sequence as AY,
(SELECT 'No Disbursements On File for This Student'
WHERE NOT EXISTS
(SELECT FaStudentAid.FaStudentAidID
FROM dbo.FaSched (NOLOCK) INNER JOIN --CHANGE TO LOOP JOIN, BUT IT RUNS THE NESTED LOOP JOIN BACKWARDS AND MASSIVELY SLOWS THE QUERY
dbo.FaStudentAid (NOLOCK) ON
FaSched.FaStudentAidID =
FaStudentAid.FaStudentAidID INNER LOOP JOIN --CHANGE TO LOOP JOIN
dbo.faFundSource (NOLOCK) ON
FaStudentAid.FaFundSourceID =
faFundSource.faFundSourceID LEFT OUTER JOIN
dbo.AdTerm (NOLOCK) ON
FaSched.AdTermID =
AdTerm.AdTermID LEFT OUTER JOIN
dbo.FaDisb (NOLOCK) ON
FaSched.FaSchedID = FaDisb.FaSchedID
WHERE faFundSource.Code = 'PELL'
AND fastudentaid.SyStudentID = SyStudent.SyStudentID
AND fastudentaid.awardyear = fastudentPELL.awardyear)) AS message,
RefundAmountNS = isnull(
(Select Sum(isnull(Amount,0))
From dbo.FaRefund (NOLOCK)
Where FaRefund.FaStudentAidID = rpt_faPELLtk0sub_vw.FaStudentAidID
AND Source != 'S'
AND Status = 'P'),0),
StipendAmount = isnull(
(Select Sum(isnull(Amount,0))
From dbo.FaRefund (NOLOCK)
Where FaRefund.FaStudentAidID = rpt_faPELLtk0sub_vw.FaStudentAidID
AND Source = 'S'
AND Status = 'P'),0),
farefund.status,
farefund.source,
farefund.amount,
farefund.datedue,
farefund.datesent,
farefund.comment
FROM dbo.AdProgram (NOLOCK) RIGHT OUTER JOIN
dbo.syStudent (NOLOCK) ON
AdProgram.AdProgramID = syStudent.AdProgramID INNER JOIN
dbo.SyCampus (NOLOCK) ON
syStudent.SyCampusID = SyCampus.SyCampusID INNER JOIN --CHANGE TO MERGE JOIN
dbo.FaStudentPell (NOLOCK) ON
syStudent.SyStudentID = FaStudentPell.SyStudentID INNER JOIN
dbo.rpt_faPELLtk0sub_vw (NOLOCK) ON
FaStudentPell.AwardYear = rpt_faPELLtk0sub_vw.AwardYear
AND FaStudentPell.SyStudentID = rpt_faPELLtk0sub_vw.SyStudentID LEFT OUTER JOIN
dbo.FaRefund (NOLOCK) ON
FaRefund.FaStudentAidID = rpt_faPELLtk0sub_vw.FaStudentAidID
WHERE SyCampus.SyCampusID = 21
and FaStudentPell.AwardYear = '2001-02'
April 24, 2002 at 2:05 am
See my answer to your second post about nested loops in outer joins - use either FORCE ORDER query hint or SET FORCEPLAN ON
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply