|
|
Posted by serge on 10/01/47 11:19
Hello Razvan,
Thank you for your detailed explanation.
It will take me a little bit of time to go through the points
and understand them.
Thanks again.
> 1. To complete your DDL, you should also add the foreign keys and
> unique constraints:
>
> ALTER TABLE TBLORDERTYPE ADD FOREIGN KEY (ORDERPROCESSINGNO)
> REFERENCES TBLORDERPROCESSING (IDNO)
> ALTER TABLE TBLSTATUS ADD FOREIGN KEY (ORDERPROCESSINGNO)
> REFERENCES TBLORDERPROCESSING (IDNO)
> ALTER TABLE TBLORDER ADD FOREIGN KEY (ORDERTYPENO)
> REFERENCES TBLORDERTYPE (IDNO)
> ALTER TABLE TBLORDER ADD FOREIGN KEY (LASTSTATUSNO)
> REFERENCES TBLSTATUS (IDNO)
> ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (ORDERNO)
> REFERENCES TBLORDER (IDNO)
> ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (STATUSNO)
> REFERENCES TBLSTATUS (IDNO)
>
> ALTER TABLE TBLSTATUS ADD UNIQUE (STATUS)
> ALTER TABLE TBLORDERTYPE ADD UNIQUE (ORDERTYPE)
> ALTER TABLE TBLORDERPROCESSING ADD UNIQUE (ORDERPROCESSING)
> ALTER TABLE TBLORDER ADD UNIQUE (ORDERNUMBER)
> ALTER TABLE TBLORDERSTATUSES ADD UNIQUE (ORDERNO, STATUSNO)
>
> 2. Your "INSERT INTO TMPORDERS [...]" does not perform as you expect,
> because the condition "AND VIEW3.IDNO IS NULL" is in the "LEFT JOIN"
> clause, not in the WHERE clause (and therefore it's ignored). To
> exclude from the INSERT any rows that are in VIEW3 you need to move the
> condition "VIEW3.IDNO IS NULL" to the WHERE clause.
>
> 3. This corrected "INSERT INTO TMPORDERS [...]" can be rewritten
> (without using views and with a slight performance improvement) as:
>
> INSERT INTO TMPORDERS (IDNO, OLDORDERTYPENO, LASTSTATUSNO)
> SELECT O1.IDNO, O1.ORDERTYPENO, O1.LASTSTATUSNO
> FROM TBLORDER O1
> INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
> INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
> WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
> AND NOT EXISTS (
> SELECT O2.IDNO
> FROM TBLORDER O2
> INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
> INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
> WHERE O2.IDNO IN (
> SELECT O3.IDNO
> FROM TBLORDER O3
> INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
> INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
> WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
> )
> GROUP BY O2.IDNO
> HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
> )
>
> 4. The "UPDATE TMPORDERS" statement, can be rewritten (by eliminating
> the join with the TBLORDERPROCESSING) as:
>
> UPDATE TMPORDERS SET NEWORDERTYPENO = T.IDNO
> FROM TBLORDERTYPE T INNER JOIN TBLSTATUS S
> ON T.ORDERPROCESSINGNO = S.ORDERPROCESSINGNO
> WHERE S.IDNO = TMPORDERS.LASTSTATUSNO
>
> 5. The whole story can be written in a single UPDATE statement, like
> this:
>
> UPDATE TBLORDER SET ORDERTYPENO = NEWORDERTYPENO
> FROM TBLORDER O INNER JOIN (
> SELECT O1.IDNO, (
> SELECT T4.IDNO FROM TBLORDERTYPE T4
> INNER JOIN TBLSTATUS S4
> ON T4.ORDERPROCESSINGNO = S4.ORDERPROCESSINGNO
> WHERE S4.IDNO = O1.LASTSTATUSNO
> ) AS NEWORDERTYPENO
> FROM TBLORDER O1
> INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
> INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
> WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
> AND NOT EXISTS (
> SELECT O2.IDNO
> FROM TBLORDER O2
> INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
> INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
> WHERE O2.IDNO IN (
> SELECT O3.IDNO
> FROM TBLORDER O3
> INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
> INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
> WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
> )
> GROUP BY O2.IDNO
> HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
> )
> ) X ON O.IDNO=X.IDNO
>
> I have to admit that this is a rather complex UPDATE statement and,
> while it's performance is better than your solution, maintainability
> may be less. So you may prefer using some views to improve readability
> (for example VIEW1, which can be used in two places in the above
> statement). However, there are some interesting points that you can
> learn from this complex statement:
> a) avoiding the use of temporary tables, when subqueries can be used
> b) the use of table aliases, to improve readability
> c) the use of "COUNT(DISTINCT something)" instead of two "GROUP BY"-s;
> d) the use of "NOT EXISTS" subqueries, instead of "LEFT JOIN ... WHERE
> ... IS NULL";
> e) the use of "WHERE ... IN" subqueries, instead of joins (when there
> is no column used from the joined subquery);
> f) the use of subqueries in the SELECT clause, instead of joins (when
> there is only one column used from the joined subquery).
> I think that these usages of subqueries (points d,e,f) improve
> readability (and may, in rare cases, even improve performance), but
> that's for you to decide, in each particular case.
>
> Razvan
Navigation:
[Reply to this message]
|