Writing resultset to file with sorted output

Question

I want to put "random" output from my result set (about 1.5 mil rows) in a file in a sorted manner. I know i can use sort by command in my query but that command is "expensive". Can you tell me is there any algorithm for writing result set rows in a file so the content would be sorted in the end and can i gain in performance with this? I'm using java 1.6, and query has multiple joins.

Answer 1

Define an index for the sort criteria in your table, then you can use the order by clause without problems and write the file as it comes from the resultset.

If your query has multiple joins, create the proper indexes for the joins and for the sort criteria. You can sort the data on your program but you'd be wasting time. That time will be a lot more valuable when employed learning how to properly tune/use your database rather than reinventing sorting algorithms already present in the database engine.

Grab your database's profiler and check the query's execution plan.

Answer 2

In my experience sorting at the database side is usually as fast or faster...certainly if the column you sort on is indexed