Re: need help with query

Lisa M. Lewis wrote:
>
> Can anyone tell me if there is a better way to perform this update:
>
> UPDATE users u
> SET u.usermapid = ( SELECT ncm.usermapid
> FROM NewCookieMap ncm
> WHERE ncm.cookie = u.cookie )
> WHERE EXISTS ( SELECT 'x'
> FROM NewCookieMap ncm
> WHERE ncm.cookie = u.cookie );
>
> This query is in intended to update several million records.
>
> snip

Firstly, ensure that there is an index on NewCookieMap (cookie [,...])!

Secondly, if the subquery predicate is selective on USERS (i.e. identifies a relatively small subset of rows to be updated) AND the table NewCookieMap is small compared to USERS, replace the EXISTS () subquery with:

WHERE u.cookie in
(SELECT ncm.cookie from NewCookieMap)

The original form requires the subquery to be evaluated (by index access to NewCookieMap) for every row of USERS. The second does a full scan of NewCookieMap and then accesses only the qualifying rows of USERS.
In this case, of course, ensure that there is also an index on Users (cookie [,...)

Hope this helps.

-- 
Chrysalis

PS.  Before anyone tells me that the optimiser should treat both forms
of the query in the same way, I invite him/her to consider the case
where NewCookieMap has no rows: the two forms are NOT directly
equivalent.

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"