Optimization issue

[Former Member]

There are two sql statements in the text please try using edit to see the first statement only the second one seems to appear and I do not know how to change the text to make both appear.

I am performing a recursive update on a key column on a single table. Only certain keys MOD(ind, 10) = 0 are valid in the recursive key indcopy (enforced by a check clause). Would either of the following SQL statements eliminate the rows where MOD(ind, 10) <> 0 before attempting the inner joins? I favor the first because in theory if the where is applied at the appropriate time neither join would ever be attempted. I believe the second would only attempt the first join and not the second. (Unless the optimizer recognizes the difference and performs the desired results (neither join) for both.) This may be trivial in this case however I have other cases with large number of rows where multiple updates using a table scan and outer joins are required. Whether or not the where clause in the first example is applied at the earliest opportunity is of considerable importance to me. In the table ind rowupdkey is the primary key, ind is a unique key and indcopy (child) is a foreign key to ind (parent).

UPDATE data.ind AS t
   SET t.indcopy = f.ind
  FROM (deleted AS d INNER JOIN data.ind AS f ON f.rowupdkey = d.rowupdkey)
 INNER JOIN data.ind AS t ON t.indcopy = d.ind
 WHERE MOD(d.ind, 10) = 0
 ORDER BY t.ind;

UPDATE data.ind AS t
   SET t.indcopy = f.ind
  FROM (deleted AS d
         INNER JOIN data.ind AS f
                 ON MOD(d.ind, 10) = 0 AND f.rowupdkey = d.rowupdkey) 
 INNER JOIN data.ind AS t ON t.indcopy = d.ind
 ORDER BY t.ind;

[Former Member]

Thanks for the edit Mark.

[Former Member]

Thanks to Justin for the edit too.

[Former Member]

In general, 'local predicates' are pushed on the table scans. In your example, the execution plan should have, in both examples, a scan on 'd' with a local scan predicate 'MOD(d.ind, 10) = 0' as this predicate refers only to the table 'd'.

Please note that your two statements are equivalent and the optimizer would rewrite them in a normal form before the query optimization process as:

UPDATE data.ind AS t
   SET t.indcopy = f.ind
  FROM deleted AS d,  data.ind AS f,  data.ind AS t 
 WHERE f.rowupdkey = d.rowupdkey 
   and t.indcopy = d.ind
   and MOD(d.ind, 10) = 0
 ORDER BY t.ind;

[Former Member]

Thanks for the info.

[Former Member]

Please post the long plans (i.e., select plan('<statement>') ) or graphical plans (i.e., use dbisql.exe and save the graphical plans in 'Plan Viewer') for your statements.

[Breck_Carter]

@pasha19: See How To Capture A Graphical Plan

[Former Member]

I have encountered this several times -- this statement operates within a trigger. I would love to see a query plan eliminating the need to ask these questions. I'm not sure you can get a query plan for a SQL statement operating in a trigger. Thanks I have bookmarked the page for future reference when dealing with non-trigger SQL statements.

[Breck_Carter]

See Database Tracing In 50 Easy Steps

[Former Member]

Thanks again -- I'm investigating the option.

[Former Member]

I got it working it was not trivial and there were a few exceptions under V12 one was mine I have an AES256 encrypted database. -- Thanks

[Former Member]

Thanks Nica_SAP I believe now that I can get query plans for sql in triggers I should be able to solve this and other problems when I encounter them.

[Former Member]

To view plans for statements in triggers (or any statement actually), enable request logging with SQL+PLAN This can be done via the server command line with the option "-zr SQL+PLAN", or call the system procedure sa_server_option( 'RequestLogging','SQL+PLAN'). The server will output this to the server messages window, but it can be captured using the command line option "-zo filename, or call sa_server_option( 'RequestLogFile','filename'). This will dump the short plan for each query.

If the graphical plan is required, use Application Profiling. The easiest way to do this is to use the Application Profiling wizard. It is explained the the documentation here: SQL Anywhere Server - SQL Usage Monitoring and improving database performance Improving database performance Application profiling Application Prifiling Wizard

[justin_willey]

Have a look at this Question - it may help.