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.deleted
 IS 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)