View performance question

Folks,

I created a view to support comprehensive reporting on one of the
databases I work with. Unfortunately, due to the structure of the
database, which includes 25 reference tables, this requires 3 regular
joins and about 40 LEFT OUTER JOINS, outputting about 100 columns.

However, the two main data tables only have about 6000 records each.
The view is quite slow, taking almost a full minute to return any
records, on an Athalon 800 with 512mb RAM. Since the client's computer
is significantly less powerful, I'm concerned about performance.

All joined fields are indexed. Is there anything else I can do to speed
this up? (view text below)

-Josh Berkus

DROP VIEW vw_valuation_report;

CREATE VIEW vw_valuation_report AS
SELECT mergers.id as id, merger_types.name AS merger_type,
announced_on,merged_on, sec_employees, merger_intents.name as
merger_intent,
percent_own, revenue1, revenue_types1.name AS revenue1_type,
revenue2, revenue_types2.name AS revenue2_type,
revenue1_note, revenue2_note, earnings, earnings_types1.name AS
earnings_type1,
earnings2, earnings_types2.name AS earnings_type2,cash_amount,
stock_amount, other_amount, liability_assumption,total_amount,
amount_types.name AS amount_type,deal_summary, max_earnout,
accounting_methods.name AS accounting_method,entry_statuses.name AS
entry_status,
paid_subscribers,unpaid_subscribers, page_views, page_view_ranking,
visits,visitors, muv_sources.name AS muv_source,
cash_flow, cash_types.name as cash_flow_type, corp_customers,
ind_customers,classifications.name as classification, entered_on,
pc.id as pc_id, pc.name as pc_name, pc.city as pc_city,
pc_states.name as pc_state, pc.zip as pc_zip, pc_countries.name as
pc_country,
pc_sr.name as pc_stregion, pc_cr.name as pc_coregion,pc.url as pc_url,
pc_stock_exchanges.name as pc_exchange,pc.stock_symbol as pc_symbol,
pc_co_cat.name as pc_category,pc_co_sub.name as pc_subcategory,
pc_verticals.name as pc_vertical,
pc_audiences.name as pc_audience, pc_rev.name as pc_revenue,pc.sic_code
AS pc_sic,
pc.ind_code as pc_ind,pc.summary as pc_summary,pc.abstract AS
pc_abstract,
sc.id as sc_id, sc.name as sc_name, sc.city as sc_city,
sc_states.name as sc_state, sc.zip as sc_zip, sc_countries.name as
sc_country,
sc_sr.name as sc_stregion, sc_cr.name as sc_coregion,
sc.url as sc_url, sc_stock_exchanges.name as sc_exchange,
sc.stock_symbol as sc_symbol, sc_co_cat.name as sc_category,
sc_co_sub.name as sc_subcategory, sc_verticals.name as sc_vertical,
sc_audiences.name as sc_audience, sc_rev.name as sc_revenue,
sc.sic_code AS sc_sic, sc.ind_code as sc_ind,
sc.summary as sc_summary, sc.abstract AS sc_abstract
FROM mergers
LEFT OUTER JOIN merger_types ON mergers.merger_type = merger_types.id
LEFT OUTER JOIN merger_intents ON mergers.merger_intent =
merger_intents.id
LEFT OUTER JOIN revenue_types revenue_types1 ON mergers.revenue1_type =
revenue_types1.id
LEFT OUTER JOIN revenue_types revenue_types2 ON mergers.revenue2_type =
revenue_types2.id
LEFT OUTER JOIN revenue_types cash_types ON mergers.cash_flow_type =
cash_types.id
LEFT OUTER JOIN earnings_types earnings_types1 ON mergers.earnings_type
= earnings_types1.id
LEFT OUTER JOIN earnings_types earnings_types2 ON mergers.earnings_type
= earnings_types2.id
LEFT OUTER JOIN amount_types ON mergers.amount_type = amount_types.id
LEFT OUTER JOIN accounting_methods ON mergers.accounting =
accounting_methods.id
LEFT OUTER JOIN entry_statuses ON mergers.entry_status =
entry_statuses.id
LEFT OUTER JOIN muv_sources ON mergers.muv_source = muv_sources.id
LEFT OUTER JOIN classifications ON mergers.classification =
classifications.id

JOIN companies pc ON mergers.primary_co = pc.id
LEFT OUTER JOIN states pc_states ON pc.state = pc_states.id
LEFT OUTER JOIN countries pc_countries ON pc.country = pc_countries.id
LEFT OUTER JOIN state_regions pc_srt ON pc.state = pc_srt.state
LEFT OUTER JOIN regions pc_sr ON pc_srt.region = pc_sr.id
LEFT OUTER JOIN country_regions pc_cot ON pc.country = pc_cot.country
LEFT OUTER JOIN regions pc_cr ON pc_cot.region = pc_cr.id
LEFT OUTER JOIN stock_exchanges pc_stock_exchanges ON pc.stock_ex =
pc_stock_exchanges.id
LEFT OUTER JOIN co_subcategories pc_co_sub ON pc.subcategory =
pc_co_sub.id
LEFT OUTER JOIN co_categories pc_co_cat ON pc_co_sub.category =
pc_co_cat.id
LEFT OUTER JOIN verticals pc_verticals ON pc.vertical = pc_verticals.id
LEFT OUTER JOIN audiences pc_audiences ON pc.audience = pc_audiences.id
LEFT OUTER JOIN revenue_models pc_rev ON pc.revenue_model = pc_rev.id

JOIN companies sc ON mergers.secondary_co = sc.id
LEFT OUTER JOIN states sc_states ON sc.state = sc_states.id
LEFT OUTER JOIN countries sc_countries ON sc.country = sc_countries.id
LEFT OUTER JOIN state_regions sc_srt ON sc.state = sc_srt.state
LEFT OUTER JOIN regions sc_sr ON sc_srt.region = sc_sr.id
LEFT OUTER JOIN country_regions sc_cot ON sc.country = sc_cot.country
LEFT OUTER JOIN regions sc_cr ON sc_cot.region = sc_cr.id
LEFT OUTER JOIN stock_exchanges sc_stock_exchanges ON sc.stock_ex =
sc_stock_exchanges.id
LEFT OUTER JOIN co_subcategories sc_co_sub ON sc.subcategory =
sc_co_sub.id
LEFT OUTER JOIN co_categories sc_co_cat ON sc_co_sub.category =
sc_co_cat.id
LEFT OUTER JOIN verticals sc_verticals ON sc.vertical = sc_verticals.id
LEFT OUTER JOIN audiences sc_audiences ON sc.audience = sc_audiences.id
LEFT OUTER JOIN revenue_models sc_rev ON sc.revenue_model = sc_rev.id
;

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco