on 2012 Aug 02 9:58 AM
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;
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
Have a look at this Question - it may help.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
67 | |
11 | |
10 | |
10 | |
9 | |
8 | |
6 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.