The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

logic in application or sql

Here is the scenario:

A magazine section is being planned. The db fields for the section are:

id, name, type, description, designer, editor, author, issue_date

every time a change occurs to the section in planning phase, we log the change to a log table that has the exact same structure plus a modified_date

when a user logs in, he shoud be able to see the number of sections (that he is authorized to see) that were modified in the last n number of days.

if a section type was changed such that user has no longer authority to view that section, that still will be counted a change for him and displayed as change.

Now, to the question:

I can get the sections that were modified using one big sql with joins. It may be the most efficient way to do it. Or I could get data in parts and do the filtering based on rules in the application layer but it may not be that efficient. we are talking 300 sections/year and each section may be modified usually 200 times apprx beefore its published.

We don't used stored procs here.

I have my bias towards application layer since I find it easier to read.

What approach is reccomended?
Urumi Send private email
Wednesday, May 02, 2007
 
 
So one performs better and the other is easier to read? Is that the issue?
David Aldridge Send private email
Wednesday, May 02, 2007
 
 
Go with what's simpler at first - be prepared to change for performance if needed
Mike S Send private email
Wednesday, May 02, 2007
 
 
+1 Mike S.  Use the "YAGNI" principle -- "You ain't gonna need it" -- and do the simplist thing that works.  Don't bother trying to double-think your performance too much before you even have any feedback as to what your performance is.
AllanL5
Wednesday, May 02, 2007
 
 
> We don't use stored procs here.

Why not?  This sounds very much like exactly the kind of scenario that you'd want to use a stored proc for.
Robert Rossney Send private email
Wednesday, May 02, 2007
 
 
@David: Yes readability vs. performance is one of the issues here. The other issue is that the norm at where I work is to use sql whenever you can.

To be honest, I can't read pages of sql as well as c# or Java code.Especially when I go back to it after a month.
Urumi Send private email
Wednesday, May 02, 2007
 
 
> to use sql whenever you can

I always shudder when I hear statements like this.  There's also the other end of the spectrum: "we use SQL as little as possible" - translation: our expensive, powerful relational database is treated like a 1970's file access library...

Use each tool in your box in its area of strength.
Mike S Send private email
Wednesday, May 02, 2007
 
 
It depends on your specific expertise in SQL and your ability to replace you if something happened to you.

If the entire department uses SQL as much as possible, then base the logic in SQL. If you're the only person who writes logic in SQL and everyone else codes it in the application layer, code it in the application layer.

Right now, the most significant issue is "how easy is it for someone else to maintain your work?"
TheDavid
Wednesday, May 02, 2007
 
 
"...we are talking 300 sections/year and each section may be modified usually 200 times apprx beefore its published."

As a frame of reference, that means one new section is created each day, and it will be modified 200 times before it is "completed." Any modern day database/web server can handle that without breaking a sweat; it's too early to be worrying about performance.*

*The exception of course is if each section is like 100MB in size.
TheDavid
Wednesday, May 02, 2007
 
 
"> We don't use stored procs here.

Why not?  This sounds very much like exactly the kind of scenario that you'd want to use a stored proc for. "

Stop it dude.  You are translating "We don't use stored procedures here" to "We don't use any data layer encapsulation technology here".  That's a big leap.  Just because they don't do it the same way you do doesn't mean they are wrong.

Just as a reference, every time I run across people who think they are really good at what they do, but they are really making a huge unmaintainable mess, I find a single-minded determination to use stored procedures.  I'm not saying people who use stored procedures make bad code, I'm saying that suggesting that someone use stored procedures isn't going to make them write better code.  It actually makes them write worse code.  People who can't write maintainable C# usually write stored procedures that make manly men cry.

Put another way, suggesting stored procedures to someone who hasn't already come to that conclusion themselves is like suggesting a person use a chainsaw when they are having trouble trimming their hedges.
JSmith Send private email
Wednesday, May 02, 2007
 
 
I am starting a new ERP program based on a DOS version of my program. I was going to use MS SQL Server 2005 on the back-end.  Anyone have any links to "debate" about the advantages and disadvantes of each position.  Maybe some best practices related to the issue.
Donald Adams
Wednesday, May 02, 2007
 
 
Based on the problem description, I would recommend using SQL - it appears to be the right tool for the job.

SQL is designed to do set operations and most programming languages still fall short in manipulations on collections.  Getting a count for a set of data with certain characteristics can usually be done in a simple SQL statement with inner joins.  If you need outer joins, I usually recommend breaking the statement into a set of more basic queries relying only on inner joins and combining the result in the application logic.  [Inner joins do an "AND" function, Outer joins do an "OR" function.]

Getting counts from collections in application code usually involves several loops and look up and comparison operations.  It will require more lines of code to create and maintain.

My preference is to do as much of the logic in the application code as possible, but for this particular operation, I feel SQL code is the better choice.
Wayne M.
Thursday, May 03, 2007
 
 
Do it in SQL on the server. That's what it's there for - to do the work and reduce network traffic.

If you don't want to / can't use a stored procedure, you could also do it using a view.
KenW Send private email
Thursday, May 03, 2007
 
 
Do not write in SQL and lose readability.
Ben
Thursday, May 03, 2007
 
 
I do not know if your DB supports GTT (Global Temporary Tables) but I would recommend going that route.

A GTT would allow you to write simple statements to populate it in multiple steps or use a delete to filter the records before bring the data back from the server.  I really do not like bringing back data that I do not need to the application side.  Using this method you can create easy to read SQL in steps that can be documented for maintainability on the application side.

In response to the view suggestion you would still end up with a large SQL statement to maintain and in reading your post I get the feeling this is what you would like to avoid.

Phillip – First time post so please be gental.
Phillip Cahoon
Thursday, May 03, 2007
 
 
"Phillip – First time post so please be gental."

Welcome.  Don't be scared, we get into some terrific arguments here but nothing personal.  Just tell yourself that you are coming here mainly to learn and you will soon lose fear of losing arguments.
Donald Duck
Friday, May 04, 2007
 
 
[and you will soon lose fear of losing arguments.]

Only a regular loser can give such an advise. Now go and polish my coins.

SQL servers. Bah. Back in Klondike, we used our bare, cold blue fingers to scratch the bits and bytes into the frozen stones.
$crooge McDuck
Saturday, May 05, 2007
 
 
"[and you will soon lose fear of losing arguments.]

Only a regular loser can give such an advise. Now go and polish my coins. "

I value Friendship more than winning or losing arguments, Character more than wealth or coins.

Unca $crooge you can just shove your coins up your a$$.
Donald Duck
Sunday, May 06, 2007
 
 
He has them already there...
Huey
Sunday, May 06, 2007
 
 
...from bathing in them...
Dewey
Sunday, May 06, 2007
 
 
...all day long.
Louie
Sunday, May 06, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz