exists rewrite SQL to make it go right execution plan

Database environment: SQL SERVER 2005

Today I see a SQL, not very complicated to write, returned more than 7,000 data, but executed 15s. The amount of data in SQL text and tables is as follows:

SELECTAcinv_07.id_item,SUM(acinv_07.dec_endqty) Dec_endqty fromacinv_07WHEREAcinv_07.fiscal_year*  - +Acinv_07.fiscal_period=(SELECT DISTINCTCtlm1101.fiscal_year *  -                                                              +Ctlm1101.fiscal_period fromctlm1101 WHEREFlag_curr= 'Y'                                                               andId_oprcode= 'ACINV'                                                               andAcinv_07.id_wh=ctlm1101.id_table)GROUP  byAcinv_07.id_item----------------------------------------SELECT  COUNT(*) fromctlm1101WHEREFlag_curr= 'Y'         andId_oprcode= 'ACINV'-- -        SELECT  COUNT(*) fromAcinv_07--1347176

View Code

Let's take a look at the SQL execution plan first.

2 tables are associated with a nested loop, and the large table acinv_07 is the driver table, ctlm1101 was scanned 1,347,176 times,

Ctlm1101.id_table is the connection column, and then the acinv_07.fiscal_year * + acinv_07.fiscal_period filter is associated,

Therefore, it is natural to slow down.

If we can rewrite, according to the meaning of the original SQL, we use exists rewrite as follows, check the data is correct

SELECTAcinv_07.id_item,SUM(acinv_07.dec_endqty) Dec_endqty fromacinv_07WHERE   EXISTS(SELECT NULL                  fromctlm1101WHEREFlag_curr= 'Y'                         andId_oprcode= 'ACINV'                         andAcinv_07.id_wh=ctlm1101.id_table andCtlm1101.fiscal_year=Acinv_07.fiscal_year andCtlm1101.fiscal_period=acinv_07.fiscal_period)GROUP  byAcinv_07.id_item

View Code

After rewriting, the execution of the plan is to go to the hash connection, the data is a query seconds out. Let's analyze the rewritten execution plan,

Small table ctlm1101 as a hash-connected driver table, id_table,fiscal_year,fiscal_period as a connection column, and a large table acinv_07

Most data is filtered when associated, so a hash match allows you to quickly return all results.

exists rewrite SQL to make it go right execution plan