SET the STATISTICS TIME ON
SET the STATISTICS IO ON
SELECT the Convert (bit, 0) Selected, m1. MonthCode, m1. CusId, c.T askId, m1. LastAmt, m1. DNAmt, m1. DNReAmt,
M1 CusPayAmt, m1 AdjustAmt, m1. CurAmt, CASE WHEN the m1. DNAmt + m1. DNReAmt=0 THEN 1 ELSE ISNULL (1 - ROUND (ISNULL (md) FDAmt, 0)/(m1) DNAmt + m1) DNReAmt), 2), 1) END CusDiscount,
1 - (case when the m1. DNAmt=0 then 0 else (isnull (md2. RAdjustAmt, 0) + m1. DNReAmt * 1/*//comment don't delete + isnull (md3 RAdjustAmt, 0) */)/m1 DNAmt end) SalesProb, a.A ccType, a.A ccNum, Anderson nterestType, Anderson nterestNum
The FROM dbo. MonthBalance m1
The LEFT OUTER JOIN dbo. SalesPersonMonthDetail sm ON sm. MonthCode=m1. MonthCode and m1. CusId=sm. CusId
Left outer join the Customer c on Arthur c. usId=m1, CusId
Left OUTER join (select MonthCode, CusId isnull (sum (DNAmt) * 1.0, 0) FDAmt from MonthDetail2 where PayName='discount' and Category=3 group by MonthCode, CusId) md on md. MonthCode=m1. MonthCode and md. CusId=m1. CusId
The left outer join (select MonthCode, CusId isnull (sum (DNAmt) * 1.0, 0) RAdjustAmt from MonthDetail2 where the Category=3 group by MonthCode, CusId) md2 on md2. MonthCode=m1. MonthCode and md2. CusId=m1. CusId
- the left outer join (select MonthCode, CusId isnull (sum (DNAmt), 0) RAdjustAmt from MonthDetail2 where the Category=4 and DNAmt> 0 group by MonthCode, CusId) md3 on md3. MonthCode=m1. MonthCode and md3. CusId=m1. CusId
The left outer join AccountSet a on a.C usId=m1. CusId
WHERE isnull (sm) MonthCode, '0')='0' and m1. MonthCode like '2021-03%' order by CusId
SET the STATISTICS TIME OFF
SET the STATISTICS IO OFF
SET the STATISTICS PROFILE OFF
Where conditions of monthcode like % % x and x % respectively test
SQL Server execution time:
CPU time=0 milliseconds, milliseconds elapsed time=0,
(692 rows affected)
Table 'MonthDetail2, scanning count 699, logical read 16102548 times, physical reads zero lookahead zero, lob logical reads zero, lob physical reads zero, lob to proofread 0 times,
Table 'Customer', counting scan 0, logical read 1384 times, physical reads zero lookahead zero, lob logical reads zero, lob physical reads zero, lob to proofread 0 times,
Table 'SalesPersonMonthDetail, counting scan 0, logical read 1384 times, physical reads zero lookahead zero, lob logical reads zero, lob physical reads zero, lob to proofread 0 times,
Table 'MonthBalance, scanning count 7, logical read 687 times, physical reads zero lookahead zero, lob logical reads zero, lob physical reads zero, lob to proofread 0 times,
Table 'AccountSet, counting scan 0, logical read 1384 times, physical reads zero lookahead zero, lob logical reads zero, lob physical reads zero, lob to proofread 0 times,
Table 'Worktable, counting scan 0, logical reads zero, physical reads zero lookahead zero, lob logical reads zero, lob physical reads zero, lob to proofread 0 times,
(28 rows affected)
SQL Server execution time:
The CPU time=133206 milliseconds, elapsed time=25037 milliseconds,
SQL Server execution time:
CPU time=0 milliseconds, milliseconds elapsed time=0,
(692 rows affected)
Table 'MonthDetail2, scanning count 14, logical read 46472 times, physical reads zero lookahead zero, lob logical reads zero, lob physical reads zero, lob to proofread 0 times,
Table 'Customer', counting scan 0, logical read 1384 times, physical reads zero lookahead zero, lob logical reads zero, lob physical reads zero, lob to proofread 0 times,
Table 'SalesPersonMonthDetail, counting scan 0, logical read 1384 times, physical reads zero lookahead zero, lob logical reads zero, lob physical reads zero, lob to proofread 0 times,
Table 'MonthBalance, scanning count 7, logical read 687 times, physical reads zero lookahead zero, lob logical reads zero, lob physical reads zero, lob to proofread 0 times,
Table 'AccountSet, counting scan 0, logical read 1384 times, physical reads zero lookahead zero, lob logical reads zero, lob physical reads zero, lob to proofread 0 times,
Table 'Worktable, counting scan 0, logical reads zero, physical reads zero lookahead zero, lob logical reads zero, lob physical reads zero, lob to proofread 0 times,
(30 rows affected)
SQL Server execution time:
The CPU time=436 milliseconds, elapsed time=83 milliseconds,
Why is the impact of monthdetail2 table the table is really no index I'm begging you not indexed test result analysis a wave
Internet can search to the conclusions are walk not to walk MonthBalance index I true meng force
CodePudding user response:
Estimate index because % x % cause MonthBalance didn't go, while MonthBalance and monthdetail2 derived table is through MonthCode associated, so eventually lead to read a lot more monthdetail2 logic,What's the difference between a look at the execution plan