is it common practice to further manipulate query results in code

Question

I am not very experienced with either programming or scripting database queries, and I have seem to come to rely on further manipulation of results from a database instead of spending time writing a more complete query that would achieve my final objective.

In fact in some cases I am completely changing the nature of the results by combining values on columns and deleting rows. It just seems easier to manipulate in code in a loop rather than writing a more involved query.

Of course most of the times I can write the query and I get the results back from the server exactly how I want to consume it. But there are those occasions where I run the results for further filtering, because I just don't know how to do it in sql but coding seems pretty straight forward.

Answer 1

IMO, manipulate in code as little as possible. Often you can get the queries pretty darn close to what you want. However, if you cannot, manipulating data in code is not inherently a bad thing.

Answer 2

It depends on the nature of those operations. SQL is not really suitable for procedural calculations that cannot be described in relational queries. However, when you are dealing with large datasets, you have to be careful not to fetch too much data for further processing to the client and deal with it as much as possible on the server.

Answer 3

Generally you should do data manipulation in sql but try to avoid having manipulating data based on business logic as it makes it really difficult to test and can become source of bugs.

Answer 4

It depends. Generally, you should let the database do things databases are good at - filtering, sorting, and joining data. Complex post-query transformations of data (business logic) you should generally be doing in your application code, and not in the query, especially since debugging these things can really be awful in SQL, but relatively pain-free in application code.

If you are fully confident that your datasets are always going to be very small (let's say... less than a few hundred records) and your manipulations always rather simple, then doing your manipulations in your application code rather than SQL will probably make no difference in overall performance or maintainability.

On the other hand, if you datasets have the possibility of growing very large some day (you should definitely test this out - fill your tables with dummy data to test this!) then you run the risk of writing application code that is unbearably clunky and probably uses monster amounts of memory to do things that the database would be much better doing for you. At this point you might need to look into what manipulations the database can do more efficiently versus the application code, and divide the workload up as appropriately as possible. Doing so would require testing and analysis on your part of both the data and the manipulations you're working with.

The final answer? Decide based on your needs how to appropriately split up the work that can be done effectively by the database server (in SQL) and can be done efficiently and cleanly in your application code.