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;

asked 02 Aug '12, 08:58

pasha19's gravatar image

pasha19
2755717
accept rate: 14%

edited 02 Aug '12, 19:45

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273

Thanks for the edit Mark.

(03 Aug '12, 08:58) pasha19

Thanks to Justin for the edit too.

(03 Aug '12, 09:02) pasha19

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;
permanent link

answered 03 Aug '12, 10:24

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

edited 03 Aug '12, 13:22

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273

Thanks for the info.

(04 Aug '12, 07:42) pasha19

Have a look at this Question - it may help.

permanent link

answered 02 Aug '12, 09:30

Justin%20Willey's gravatar image

Justin Willey
6.8k110144212
accept rate: 20%

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.

permanent link

answered 03 Aug '12, 10:34

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

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.

(04 Aug '12, 07:41) pasha19
Replies hidden

Thanks again -- I'm investigating the option.

(04 Aug '12, 08:42) pasha19
Replies hidden

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

(06 Aug '12, 01:27) pasha19

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.

(06 Aug '12, 02:36) pasha19
1

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

(07 Aug '12, 13:44) Gary Hale
showing 4 of 7 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×31
×21
×20
×7

question asked: 02 Aug '12, 08:58

question was seen: 1,221 times

last updated: 07 Aug '12, 13:44