Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

[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?

share|improve this question
    
(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. –  MichaelT Aug 30 '13 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. –  Robert Harvey Aug 30 '13 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 '13 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 '13 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 '13 at 9:03
show 6 more comments

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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