Thread: Improving performance with a Function instead of a View

Hi all,
    I am using some views now to put together a particular format for my Java client factory to produce Java Beans from the database.

Because we support internationalisation we are representing values as an id then storing their multiple languages in unicode to support the same repesentation at the database.

This format is:

base_table, id bigint, is_disabled boolean default false.

resource_table, foreign_key_to_base_table, locale_foreign_key, display_name, is_translated

As such, my views are quite slow because there are a number of Right Joins occuring so that I can present a single "locale" field in the view that all the localised information will attach to correctly.

That way I can > select * FROM v_object where locale = 'en_GB' and object_id = 120031;

So if there are three localised joins they are bound to the single locale.

E.G

create view v_object as
     select loc.id as locale,
             obj.id as object_id,
             obj.user_data as user_data,

             type.id  as object_type,
             type_res.disp_name as object_type_display_name

            size.id   as object_size,
            size_res.disp_name as object_size_disp_name

   from  locale as loc,
          object as obj
            left join object_type as type on type.id = obj.object_type
            left join object_type_res as type_res on type_res.object_type = obj.object_type
            left join object_size  as size on size.id = obj.object_size
            left join object_size_res as size_res on size_res.object_size = obj.object_size
   where ( type_res.locale = loc.id OR type_res.locale IS NULL ) AND
           ( size_res.locale = loc.id OR size_res.locale IS NULL );

In this example the left joins are required to ensure the columns are returned even if null as not all fields are required.

Anyway,  there is a performance problem, and we have a temporary solution.

I was wondering if it is possible to create a function that will return a set of data with the correct view names and have this function perform additional and fast checks server side?

Regards

-- Hadley Willan » Director » hadley.willan@deeperdesign.com » +64(21) 28 41 463 Deeper Design Limited » +64(7) 377 3328 » www.deeperdesign.com

Hadley Willan wrote:

> Hi all,
>     I am using some views now to put together a particular format for
> my Java client factory to produce Java Beans from the database.
>
> Because we support internationalisation we are representing values as
> an id then storing their multiple languages in unicode to support the
> same repesentation at the database.
>
> This format is:
>
> base_table, id bigint, is_disabled boolean default false.
>
> resource_table, foreign_key_to_base_table, locale_foreign_key,
> display_name, is_translated
>
> As such, my views are quite slow because there are a number of Right
> Joins occuring so that I can present a single "locale" field in the
> view that all the localised information will attach to correctly.
>
> That way I can > select * FROM v_object where locale = 'en_GB' and
> object_id = 120031;


Without taking the view definition into account, the above query could
not use an index on object_id because it is of type 'bigint', but the
integer constant is parsed as 'integer'. It must either be rewritten as:

object_id = 120031::bigint

or

object_id = '120031'

or set the sequence for this identifier to start fetching values > 4.2
billion (32-bit numbers). Of course, the view definition may have other
optimization possibilities as well...

Mike Mascari

Thanks, but I don't believe this to be a problem because my JDBC layer when I construct the query is using setObject( parameter, getId, Types.BIGINT )

so by the time it arrives at the database that cast should have already occured?

I could be wrong but running the Explain Analyse shows indexes being used, but the right join for the locale stuff is the killer.

Thanks

On Thu, 2004-02-05 at 13:31, Mike Mascari wrote:

Hadley Willan wrote:

> Hi all,
>     I am using some views now to put together a particular format for 
> my Java client factory to produce Java Beans from the database.
>
> Because we support internationalisation we are representing values as 
> an id then storing their multiple languages in unicode to support the 
> same repesentation at the database.
>
> This format is:
>
> base_table, id bigint, is_disabled boolean default false.
>
> resource_table, foreign_key_to_base_table, locale_foreign_key, 
> display_name, is_translated
>
> As such, my views are quite slow because there are a number of Right 
> Joins occuring so that I can present a single "locale" field in the 
> view that all the localised information will attach to correctly.
>
> That way I can > select * FROM v_object where locale = 'en_GB' and 
> object_id = 120031;


Without taking the view definition into account, the above query could 
not use an index on object_id because it is of type 'bigint', but the 
integer constant is parsed as 'integer'. It must either be rewritten as:

object_id = 120031::bigint

or

object_id = '120031'

or set the sequence for this identifier to start fetching values > 4.2 
billion (32-bit numbers). Of course, the view definition may have other 
optimization possibilities as well...

Mike Mascari

-- 







Hadley Willan » Director » hadley.willan@deeperdesign.com » +64(21) 28 41 463

Deeper Design Limited » +64(7) 377 3328 » www.deeperdesign.com

On Thu, 5 Feb 2004, Hadley Willan wrote:

> Thanks, but I don't believe this to be a problem because my JDBC layer
> when I construct the query is using setObject( parameter, getId,
> Types.BIGINT )
>
> so by the time it arrives at the database that cast should have already
> occured?
>

The JDBC driver will not do any casting for you.  The cross type indexing
problem is is a backend issue and has been addressed in the cvs version,
but this has long been a problem for JDBC users.

Kris Jurka