[edit]Turns out there's 1.5 million records... The SQL database is taking about 17-20 seconds to run the query. Would it be best to have Java handle the joins and rearranging in that case? Since this is a web app, if multiple users are getting Excel exports, would the long queries reduce the overall performance of the site?[/edit]

I have a dataset that contains roughly 70,000 records. For simplicity's sake, let's say the data currently looks like this:

User ID | Question | Answer

In our web application, the data needs to be rotated 90 degrees and exported into Excel, such that we have the following:

User ID | Question 1 | Question 2 | Question 3 ....

I think we end up with around 50 or so columns.

Should the data be rearranged on the database side using pivot tables or should it be rearranged on the application side? Which solution would be the easiest to maintain? Which solution would have the greatest performance?

  • (Not completely sure, so not answer) - The database server is likely beefier than the excel app... don't confuse excel more than needs be done. Rotate on the server. See support.microsoft.com/kb/175574 for how to do it in Microsoft SQL Server.
    – user40980
    Aug 30, 2013 at 19:35
  • 3
    It should only take you about 10 minutes to knock up a prototype on each side, and find out which one performs better. Aug 30, 2013 at 19:42
  • @RobertHarvey I wish... The problem we have is that the values for the columns are unknown as the questions are created by the end users. I'm going to give this link a shot and test it against writing a java app to do it... It's just that the project lead on our project won't let us use stored procedures. :(
    – Pete
    Aug 30, 2013 at 19:47
  • 1
    Opposed to MichaelT: knowing Excel and some SQL databases, I am pretty sure the most databases (including Oracle and MS SQL) are far from beeing "beefier" than Excel for such things.
    – Doc Brown
    Aug 30, 2013 at 20:22
  • 1
    @user2708395: You are not going to export the 1,5 mio records into one excel file, I guess? And you are talking of some "joins in Java"? There is obviously some missing information about what is really going on, so come on, give a better description of the problem or expect not to get any good answers.
    – Doc Brown
    Aug 31, 2013 at 9:03

2 Answers 2


Since you are exporting data, in this situation I'd typically build the export file manually in code. Pull back the rows sorted by user id, and loop through them. While the dataset would be large, the query itself would be very simple and shouldnt stress the database much. No need to otherwise burden the sql server with keeping the 'compiled' data set maintained when its not needed.


It depends.

Usually app tiers are easier to scale horizontally than databases. So when possible, doing more on the app tier (or client), and less on the DB allows you to throw hardware at the problem if 100 users decide to run their 20 second query at once.

If you don't care about scalability, then I'd look at the explain plan for the query first, and what causes it to be slow. It might be possible to make it faster server side.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.