db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Re: AW: AW: Pivoting tables?
Date Wed, 05 Dec 2012 12:44:41 GMT
On 04/12/2012 20:46, John English wrote:
> I'm going to try using a user-defined function to format the name and thus avoid
> the extra GROUP BY elements (which are my immediate suspect for the performance
> hit), but does anyone have any other suggestions for improving on this solution?

Just to provide an ending to this story: The performance hit turned out to be 
the join on the users table (where the username, a varchar(15), is for 
historical reasons the primary key, and it's much too late to do anything about 
it). A function to look up the username and format it is about 10x faster than 
the join, so now it takes between 100ms and 700ms to display a sample table as 
opposed to 6500ms to 8500ms with a join (where the variation is presumably 
caused by server latency).

Again, thanks to Malte for pointing me at the solution, and to Rick for 
providing food for future thought. And now I need to look for other places where 
I can avoid a join on the users table...
John English

View raw message