You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by John English <jo...@gmail.com> on 2012/12/04 19:46:34 UTC
Re: AW: AW: Pivoting tables?
On 28/11/2012 16:30, Malte.Kempff@de.equens.com wrote:
> Hi John, It sounds to me that your first solution could work flexible enough,
> while the other might have lecks of flexibility, haven't they? I am not sure
> how much you like to studdy and translate it in derby and Java procedures in
> the example of the page from last answer.
I've finally got my head around the various options, and rather than wait for
10.10 so I can experiment with user-defined aggregates, I've cobbled together
something based on what you suggested. I programmatically build a query and use
it as the table to be displayed by my monstrous showTable() method:
"(SELECT username,SUM(" + total + ") AS total" + values +
" FROM (SELECT username" + cases + " FROM products WHERE dept=?) AS s" +
" GROUP BY username) AS t"
where the variables look like this when the items have IDs 53, 75 and 82:
cases: ",CASE WHEN itemid=53 THEN cost ELSE NULL END AS c53" +
",CASE WHEN itemid=53 THEN discounted ELSE 0 END AS d53" +
",CASE WHEN itemid=75 THEN cost ELSE NULL END AS c75" +
",CASE WHEN itemid=75 THEN discounted ELSE 0 END AS d75" +
",CASE WHEN itemid=82 THEN cost ELSE NULL END AS c82" +
",CASE WHEN itemid=82 THEN discounted ELSE 0 END AS d82"
total: "d53+d75+d82"
values: ",SUM(c53) AS v53,SUM(c75) AS v75,SUM(c82) AS v82"
This speeds things up by a factor of somewhere between 20x and 50x compared to
the temporary table approach, which is absolutely wonderful! (The code is still
totally opaque, but you can't have everything...)
However, if I try to format the username nicely by pulling the user's surname
and initials out of the users table, I end up with this:
"(SELECT surname||','||initials,username,SUM(" + total + ") AS total" + values +
" FROM (SELECT username" + cases + " FROM products WHERE dept=?) AS s,users" +
" WHERE s.username=users.username GROUP BY users.username,surname,initials)
AS t"
which slows things down again by about 15x. In the temporary table approach I
was putting the formatted name into the table as I built it, so there wasn't
much extra overhead.
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?
Thanks for all the help!
--
John English
Re: AW: AW: Pivoting tables?
Posted by John English <jo...@gmail.com>.
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