Collect statistics to get SQL to take the right execution plan

Database environment: SQL SERVER 2005

Today in the production library caught a slow run of SQL, the statement is not very complex, the returned data only more than 800 rows,

It was executed for 34 minutes, even longer.

Let's take a look at the results.

I'll post the SQL.

SELECTa.dec_yt1, B.dec_yt1, A.dec_yt2, B.dec_yt2, A.dec_yt3, B.dec_yt3 fromctlm8288 A, (SELECTt.corr_id, t.clerk_id,ROUND(SUM(ISNULL(T.DEC_YT1,0)),2) asDec_yt1,ROUND(SUM(ISNULL(T.dec_yt2,0)),2) asDec_yt2,ROUND(SUM(ISNULL(T.DEC_YT3,0)),2) asDec_yt3 from(SELECTb.corr_id, b.clerk_id, Case  whenD.feetype_flag= '1'                                      Then ROUND(SUM(A.dec_sqty*c.fee_price),2)                                END  asDec_yt1, Case  whenD.feetype_flag= '2'                                      Then ROUND(SUM(A.dec_sqty*c.fee_price),2)                                END  asDec_yt2, Case  whenD.feetype_flag= '3'                                      Then ROUND(SUM(A.dec_sqty*c.fee_price),2)                                END  asDec_yt3 fromctlm8666 A, ctlm8001 B, v_saft04 C, ctlm8206 DWHEREa.terminal_id=b.terminal_id andb.dept_id=c.dept_id anda.item_id=c.vitem_id andc.fee_id=d.fee_id andd.flag_t= 'Y'                                 andA.fiscal_year=  -                                 andA.fiscal_period<= 6                                 andC.cur_year=  -                                 andb.tertype_id like '0102%'                      GROUP  byb.corr_id, b.clerk_id, c.fee_id, D.feetype_flag) TGROUP  byt.corr_id, t.clerk_id) bWHEREa.corr_id=b.corr_id anda.clerk_id=b.clerk_id andA.fiscal_year=  -         andA.fiscal_period= 6

SQL is not much of a problem, I try to execute, and then the long waiting process ...

30 minutes later, no results, I read the implementation plan, I know where the problem is.

Looking at the place where the execution plan is red, the optimizer estimates only one row for the ctlm8666 and ctlm8001 tables, and then counts the 2 tables to see if there are really only 1 rows.

SELECT  COUNT(*) fromctlm8666 aWHEREA.fiscal_year=  -         andA.fiscal_period<= 6--1650439SELECT  COUNT(*) fromctlm8001 bWHEREb.tertype_id like '0102%'--229823

The difference between the estimated data and the actual data is very large, which is likely due to a problem with the statistical information.

Collecting statistics on the ctlm8666 table, the running SQL will soon have results.

I re-executed the next, looking at how long SQL execution will be after collecting statistics, is the result.

(End of this article)

Collect statistics to get SQL to take the right execution plan