[GENERAL] 26h Query.

[Jason Leach]

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

[GENERAL] The Ghost.

[Jason Leach]

hi,

Does anyone know of a thechnical brief on Pg I can look up and read?  For example how 
it spawns a new process, uses mem, and what not.  Not detailed algorithms, but an 
overview of how it works; and maybe a why.

If no such animal exists, perhaps, is a few people are interested, we coudl put one 
together.   Maybe even database  of such information - but perhaps a tech-faq.

J

[GENERAL] Java Ex.

[Jason Leach]

hi,

Does anyone have a Java example of connecting to a DB and
executing a simple SQL command?

Thanks,
Jason