From: Mark Stosberg <mark@summersault.com>
Subject: Re: Can anyone make this code tighter?  Too slow, Please help!
Date: Fri, 09 Feb 2007 16:04:31 -0500
Organization: Hub.Org Networking Services
Lines: 31
Message-ID: <eqinlp$2vg7$1@news.hub.org>
References: <1171053125.039969.232070@m58g2000cwm.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 1.5.0.8 (X11/20061109)
In-Reply-To: <1171053125.039969.232070@m58g2000cwm.googlegroups.com>
To: pgsql-performance@postgresql.org

shane.eckel@seattlesoftware.com wrote:
> I have this function in my C#.NET app that goes out to find the
> business units for each event and returns a string (for my report).
> I'm finding that for larger reports it takes too long and times out.
> 
> Does anyone know how I can speed this process up?  Is this code very
> tight or could it be cleaner?  thanks in advance for your help, this
> is a big issue used on several reports.

Perhaps try "EXPLAIN ANALYZE" on this query, given a valid event ID:

            SELECT     Distinct Companies.Name
                        FROM         Companies INNER JOIN
                      ExpenseAllocations ON Companies.ID =
ExpenseAllocations.BusinessUnitID
                        WHERE     (ExpenseAllocations.EventID =
@EventID)
                        ORDER BY Companies.Name DESC


#######
Do the columns used in the join and WHERE clause have indexes?

It's also possible the optimization needs to happen at a different level. Perhaps you are frequently
looking up the same results in a large report, or throughout the day.

If this part doesn't need to be up-to-second fresh, perhaps your application could
cache some of the results of this function, instead of repeatedly asking the database
to recompute it.

   Mark