Question Re: Optimization issue in Technology Q&A

Optimization issue

Former Member — Thu, 02 Aug 2012 08:58:06 GMT

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;

Re: Optimization issue

justin_willey — Thu, 02 Aug 2012 09:30:57 GMT

Have a look at this Question - it may help.

Re: Optimization issue

Former Member — Fri, 03 Aug 2012 08:58:28 GMT

Thanks for the edit Mark.

Re: Optimization issue

Former Member — Fri, 03 Aug 2012 09:02:20 GMT

Thanks to Justin for the edit too.

Re: Optimization issue

Former Member — Fri, 03 Aug 2012 10:24:34 GMT

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;

Re: Optimization issue

Former Member — Fri, 03 Aug 2012 10:34:48 GMT

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.

Re: Optimization issue

Breck_Carter — Fri, 03 Aug 2012 11:43:00 GMT

@pasha19: See How To Capture A Graphical Plan

Re: Optimization issue

Former Member — Sat, 04 Aug 2012 07:41:02 GMT

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.

Re: Optimization issue

Former Member — Sat, 04 Aug 2012 07:42:14 GMT

Thanks for the info.

Re: Optimization issue

Breck_Carter — Sat, 04 Aug 2012 08:29:24 GMT

See Database Tracing In 50 Easy Steps

Re: Optimization issue

Former Member — Sat, 04 Aug 2012 08:42:13 GMT

Thanks again -- I'm investigating the option.

Re: Optimization issue

Former Member — Mon, 06 Aug 2012 01:27:48 GMT

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

Re: Optimization issue

Former Member — Mon, 06 Aug 2012 02:36:45 GMT

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.

Re: Optimization issue

Former Member — Tue, 07 Aug 2012 13:44:17 GMT

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