hi,

I'm having a bit of trouble with my SQL query. It takes about 26h to
run on a 3Ghz PC. I'd really like to speed this up.

I put this query in a loop to iterate over 20 tables (each table
including summary has 400k records), each time the table name changes.
I this case it's m_alal. Each table has an identical primary key
ECP_TAG but the RATING column is different. What I doing is:

Classifying RATING has High, Moderate, Low No Data or Nill. Then
counting how many tables classify the PK in one of these categories.
This result is kept in my summary table. I end up with a summary table
like:

ECP_CODE | HIGH | MODERATE | LOW ...
M3456 | 3 | 4 | 7 ..

Because I have 20 tables, if you were to add up along each row it
would sum up to 20.

Anyone have some suggestion on speeding this up.




update public.summary
set
"MAX_VAL" = CASE
WHEN public.m_alal."RATING" > public.summary."MAX_VAL" THEN
public.m_alal."RATING"
ELSE public.summary."MAX_VAL"
END,
"MIN_VAL" = CASE
WHEN public.m_alal."RATING" < public.summary."MIN_VAL" THEN
public.m_alal."RATING"
ELSE public.summary."MIN_VAL"
END,
"NO_DATA" = CASE
WHEN public.m_alal."RATING" = 0 THEN public.summary."NO_DATA" + 1
END,
"NILL" = CASE
WHEN public.m_alal."RATING" = -1 THEN public.summary."NILL" + 1
ELSE public.summary."NILL"
END,
"HIGH" = CASE
WHEN public.m_alal."RATING" > 75 THEN public.summary."HIGH" + 1
ELSE public.summary."HIGH"
END,
"MODERATE" = CASE
WHEN public.m_alal."RATING" > 30 THEN public.summary."MODERATE" + 1
ELSE public.summary."MODERATE"
END,
"LOW" = CASE
WHEN public.m_alal."RATING" < 25 THEN public.summary."LOW" + 1
ELSE public.summary."LOW"
END
FROM public.m_alal
WHERE public.summary."ECP_TAG" = public.m_alal."ECP_TAG" AND
public.m_alal."RATING" IS NOT NULL;

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Sun, Mar 20, 2005 at 10:10:14AM -0800, Jason Leach wrote:
> hi,
>
> I'm having a bit of trouble with my SQL query. It takes about 26h to
> run on a 3Ghz PC. I'd really like to speed this up.
>
> I put this query in a loop to iterate over 20 tables (each table
> including summary has 400k records), each time the table name changes.
> I this case it's m_alal. Each table has an identical primary key
> ECP_TAG but the RATING column is different. What I doing is:

You're going to need to post the EXPLAIN ANALYZE output if you expect
any meaningful response...
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFCPfmOY5Twig3Ge+YRAujjAKDGrp63G7hPgd6NXZDxJe hCDNDUfwCgw0wO
d+3FMEqloTu4UgbiWhtSiX4=
=msca
-----END PGP SIGNATURE-----