Thread: A cache for the results of queries ?

Hi everybody,

i'd like to know if it exists a system of cache for the results of
queries.

What i'd like to do :

select whatever_things from (selection_in_cache) where special_conditions;

The interesting thing would be to have a precalculated
selection_in_cache, especially when selection_in_cache is a very long
list of joins...

For example, a real case:
SELECT
    p.id_prospect,
    p.id_personne1,
    INITCAP(p1.nom) AS nom,
    INITCAP(p1.prenom) AS prenom,
    a1.no_tel,
    a1.no_portable,
    p.dernier_contact,
    cn.id_contact,
    cn.id_vendeur,
    cn.id_operation,
    CASE WHEN p.dernier_contact IS NOT NULL THEN cn.date_contact::ABSTIME::INT4 ELSE p.cree_le::ABSTIME::INT4 END AS
date_contact,
    cn.id_moyen_de_contact,
    cn.id_type_evenement,
    cn.nouveau_rdv::ABSTIME::INT4 AS nouveau_rdv,
    cn.date_echeance::ABSTIME::INT4 AS date_echeance,
    cn.date_reponse::ABSTIME::INT4 AS date_reponse,
    (CASE WHEN lower(cn.type_reponse) = '.qs( 'abandon' ).' AND cn.id_vendeur = '.qs( $login ).' THEN '.qs( 'O').' ELSE
p.abandonEND) AS abandon 
FROM
    prospect p
    JOIN personne p1 ON (p.id_personne1 = p1.id_personne)
    JOIN adresse a1 ON (a1.id_adresse = p1.id_adresse_principale)
    LEFT JOIN contact cn ON (p.dernier_contact = cn.id_contact)
    '.( $type_orig ? 'LEFT JOIN orig_pub op ON ( p.id_orig_pub = op.id_orig_pub )' : '' ).'
WHERE
    ( '.(
            $abandon
                ? ''
                : '(
                        (cn.type_reponse IS NULL OR lower(cn.type_reponse) != ' .qs( 'abandon' ) .' OR cn.id_vendeur !=
'.qs( $login ) .') 
                        AND (p.abandon != ' .qs( 'O' ) .' OR p.abandon IS NULL)) AND '
        ).' TRUE '.$condition.')
ORDER BY
    '.$primary_sort.',
    '.secondary_sort.'
LIMIT 30
OFFSET '.$offset*$page_length

There is some perl inside to generate the query ; for non-perl-people,
'.' is used for concatenation and '( a ? b : c)' means 'if a then b else c'.

$condition is a precalculated set of conditions.

Here i have a very heavy query with 4 very heavy JOIN.
That's why i'd like to have a precalculated query.
A view wouldn't help, because it would calculate the whole query each
time.

Any idea ?
Thanks in advance for any input.

--
dpradier@apartia.fr 01.46.47.21.33 fax: 01.45.20.17.98

On Thursday 26 February 2004 13:30, David Pradier wrote:
> Hi everybody,
>
> i'd like to know if it exists a system of cache for the results of
> queries.
>
> What i'd like to do :
>
> select whatever_things from (selection_in_cache) where special_conditions;
>
> The interesting thing would be to have a precalculated
> selection_in_cache, especially when selection_in_cache is a very long
> list of joins...

You might want to search the archives for the -sql list for a message
"Materialized View Summary" - some time this week. That's almost exactly what
you want.

--
  Richard Huxton
  Archonet Ltd

On Thu, 26 Feb 2004, David Pradier wrote:

> Hi everybody,
>
> i'd like to know if it exists a system of cache for the results of
> queries.

I believe there are some external libs that provide this at the
application level.  PHP's adodb is purported to do so.

David Pradier wrote:
> i'd like to know if it exists a system of cache for the results of
> queries.

If you are willing to do this at an application level, you could
calculate a MD5 for every query you plan to run and then SELECT INTO a
temporary table that's based on the MD5 sum (e.g. TMP_CACHE_45123). Next
time somebody runs a query, check to see if that table exists already.
Then you just have to figure out some way to know when results should be
expired.