Re: performance tuning in large function / transaction

On Mon, 17 Dec 2001, MindTerm wrote:

>        select count(*)
>        from   ACL a, APPLICATION app
>        where  ACL_APP            = app.app_id
>        and    APP_INSTALLED      = 'Y'
>        and    ACL_LEVEL          > 0
>        and    ACL_GALLERY_ID     = 1
>        and    app.APP_GALLERY_ID = 1
>        and    substr(app.app_order,1, 6 ) = '021101'
>        and    app.app_order      <> '021101'
>        and    exists
>               (select u.ug_id
>                from   user_group u, user_group_master
> ug
>                where  a.ACL_GRP = u.ug_id
>                and    u.ug_user_id = 5170
>                and    ug.ug_id     = u.ug_id
>                and   (ug.deleted   = 'N' or ug.deleted
> IS NULL)
>                and    u.gallery_id = 1
>                and    ug.gallery_id = 1 );

I don't know if it'll help, but does:

select count(*)from ACL a, APPLICATION app, user_group u,user_group_master ug       where  ACL_APP            =
app.app_id      and    APP_INSTALLED      = 'Y'       and    ACL_LEVEL          > 0       and    ACL_GALLERY_ID     = 1
     and    app.APP_GALLERY_ID = 1       and    substr(app.app_order,1, 6 ) = '021101'       and    app.app_order
<>'021101'       and    a.ACL_GRP = u.ug_id               and    u.ug_user_id = 5170               and    ug.ug_id
=u.ug_id               and   (ug.deleted   = 'N' or ug.deletedIS NULL)               and    u.gallery_id = 1
  and    ug.gallery_id = 1;
 

give the same results as the original query.  Maybe
that form will work faster.  (I'm going to play with
it a little tomorrow, but since I don't have much data
in there, I'm not sure how well it'll translate)