0

I have an n-tier application which among other things includes a Data Access Layer (DAL) AND a Business Logic Layer (BLL). I use SQL queries and stored procs in the DAL which I use to fill my objects.

So here's my question:

Does an ORDER BY clause in my SQL queries violate separation of concerns?

On the one hand, it seems that sorting logic belongs in the business layer because it is our business rules that determine why we want to display the data in a particular order. Also, we may want to display the same data in more than one way. Further, shouldn't my data access code be unaware of concerns such as how it is displayed?

On the other hand databases are generally more efficient at sorting data than application code, so for performance reasons there is an incentive go with an ORDER BY clause over sorting in the BLL. In addition, I'm not sure if specifying a default sort order in the DAL really violates separation of concerns. The records have to come out of the database in some manner. Why not sort them according to the most common scenario? In cases where we need a different sort order than the default, then we can sort via some method in the BLL.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Louise Eggleton
  • 969
  • 2
  • 15
  • 27
  • Don't know if I would object to the sort being there, but personally, I think I would be uncomfortable with my business logic relying on a sort from my access layer. – Chad Schouggins Jan 24 '14 at 06:37
  • @ChadSchouggins Not sure I follow. Do you mean you don't trust that whoever does the DAL will include the sort logic? – Louise Eggleton Jan 24 '14 at 13:21

1 Answers1

0

I feel it's OK if you have a 'default' sort setting which is applied in the absence of sort settings being provided to the procedure.

That will make the code re-usable and flexible going forwards....what if, for example, you wanted to allow users to define the sort settings themselves?

I don't think it's a violation, considering many tables have obvious default sorting logic that would otherwise need to be re-applied time and time again. And you are correct in saying it's more effective to apply sorting at the database level.

trucker_jim
  • 572
  • 3
  • 7
  • Agree with @trucker_jim, adding an order by clause at the DAL level simply provides a default sort order, this can be seen as an optimizations especially if this sort is used quite often. Always try to allow the DBMS to do as much of the heavy lifting as possible, its what it is designed to do. –  Jun 09 '14 at 16:51
  • Thanks for answering. I had had pretty much come to this conclusion myself based on the fact that there were few strong responses, so I took it to mean it's not a big deal. I come from a data driven background and agree with getting the database to do the "heavy lifting", but I find in an object oriented world, many people treat the database as a second class citizen and do not want any "logic" performed at the database level. I interpret @ChadSchouggins comment about not being comfortable with a sort order coming from the DAL as indicative of that mindset. – Louise Eggleton Jun 10 '14 at 13:54