====== AIRS_CI-382 Contimual Improvement of DA related SQL statements ======

[[https://tasks.supplyon.de/browse/AIRS_CI-382|AIRS_CI-382]] =\\ 
[[https://tasks.supplyon.de/browse/AIRS_CI-380|AIRS_CI-380 SQL Performance Improvement for DA Creation Quicksearch Count]] + \\  
[[https://tasks.supplyon.de/browse/AIRS_CI-381|AIRS_CI-381 SQL Performance Improvement for DA Creation Quicksearch Data]]\\ 

[[https://git.seeburger.de/r/c/supplyon/airsupply/+/100427|Change in Gerrit]]

===== AIRS_CI-380 SQL Performance Improvement for DA Creation Quicksearch Count =====

==== TODO ====

  * There are different statements for seller and buyer users, which contain only those predicates, which are necessary to select the seller or the buyer specific data => no predicates like (cCustomerCGId = :11 OR cSupplierCGId = :12)
    * this means 
      * if supplier add ''  AND (cSupplierCPId IN (-1000002, 10006106, 10006107))  AND (cSupplierCGId = 100027) AND CISUNREGSUPPLIER = 0''
      * if buyer add ''AND (cCustomerCGId = 100011)''
    * Frank did this work already for "ASN Overview". Check his changes:
      * https://git.seeburger.de/r/c/supplyon/airsupply/+/94452/
  * Eliminate repetitions of identical predicates like 
<code sql>
AND ROWNUM <= 5000
AND (cCustomerPortalGroup IN (:21 ,:22 ))
AND (cSupplierCPId IN (:23 ,:24 ,:25 ,:26 ))
AND (cSupplierCGId = :27)
AND CISUNREGSUPPLIER = :28
</code>

===== AIRS_CI-381 SQL Performance Improvement for DA Creation Quicksearch Data =====

==== TODO ====
  * Eliminate repetitions of identical predicates like 
<code sql>
AND ROWNUM <= 5000
AND (cCustomerPortalGroup IN (:21 ,:22 ))
AND (cSupplierCPId IN (:23 ,:24 ,:25 ,:26 ))
AND (cSupplierCGId = :27)
AND CISUNREGSUPPLIER = :28
</code>

  * Missing selective conditions for the joins of the sub queries in the first query block (for ASN_SOURCE_TYPE 2) are present.


==== Misc ====

  * The SQL is built in ''ASNDAOImpl.getASNCreateDataStmtPerf()''
  * The statement is built (filled with parameters) in ''ASNDAOImpl.buildASNCreatePerformanceStmt()''
  * To fetch VMIs use quick search '600251'