ajit_cus wrote: > > I have a table with 1.1 million records > > The following queries: > Update table set somecolumnname=value where > someothercolumn=(subquery that returns a value) -(1) > > Update table set somecolumnname=value where someothercolumn=null -(2) >
BTW someothercolumn=null never become true, you should use a null predicate (see http://www.sapdb.org/htmhelp/8c/ccce2ac71c11d2a97100a0c9449261/frameset.htm). > both execute quickly(a few millisecs) > note that (1) runs quickly and will update rows > (2) will update no rows > > Note that all columns in the table are NOT NULL which is why > (2) will always update no rows > > However > Update table set somecolumnname=value where > someothercolumn=(a subquery that returns no rows) -(3) > Takes over 1 MINUTE to update NO ROWS > Note:The subquery is not correlated with the first query and > on its own executes in 1 millisec. > (3) will always update no rows for because someothercolumn > was created with NOT NULL. Note that (3) is a special case of > (1) in my code. > > > Unfortunately I could not get sqlstudio to 'explain' an update query > Any suggestions to improve performance? > Also is it possible to explain an UPDATE statement? The explain command could only be used together with a select. So you could try to explain the following selects to get a hint whats going on. (1) explain select somecolumnname from table where someothercolumn=(subquery that returns a value) for reuse (2) explain select somecolumnname from table where someothercolumn is null for reuse (3) explain select somecolumnname from table where someothercolumn=(a subquery that returns no rows) for reuse Feel free to send the explain output to me perhaps I could help you. Kind regards, Holger SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
