Conditions in SQL statement VS conditions later in code

Question

Consider this myTable that has some same-day records:

+-----+---------------------+---------------------+
| id  | start_date          | end_date            |
+-----+---------------------+---------------------+
| 1   | 2017-07-17 12:00:00 | 2017-07-17 14:00:00 |
| 2   | 2017-07-17 8:00:00  | 2017-07-17 11:00:00 |
| 3   | 2017-07-17 13:00:00 | 2017-07-17 15:00:00 |
+-----+---------------------+---------------------+

And let's say I would like to get records that overlap 12:00:00 -> 14:00:00 (where 12->14 is target).

Essentially, records that are valid for (RecordStart <= TargetEnd) and (RecordEnd >= TargetStart)

I can do that with one of the following:

1- Condition in sql statement:

const myQuery = 
    "SELECT * FROM myTable " +
    "WHERE start_date <= '" + target_end_date +
    "' AND '" + target_start_date + "' <= end_date";

let filtered = DbConnection.query(myQuery)

2- Condition in code:

const myQuery =  "SELECT * FROM myTable";

let records = DbConnection.query(myQuery)

let filtered = [];
records.map((rec) => {
    if(Date.parse(rec.start_date) <= Date.parse(target_end_date) && Date.parse(target_start_date) <= Date.parse(rec.end_date) ) 
        filtered.push_back(rec)
})

Which practice I should follow?

And more general, choosing the best practice is case oriented? Does it depend on how large the db is and/or how big would the result be? Or there is some knowledge that I still don't know about sql vs code?

As a programmer, Do I build both approaches for each use case and test to then choose?

EDIT:

Taking into consideration that every processing-related/querying thing is gonna happen on the API side, then filtered (which is same final result for both approaches) will be sent through the network back to client.

always the filtered condition in sql .. in the same time you do select you have the filter and so avoid the code for language — ScaisEdge, Aug 22 '17 at 16:35
@scaisEdge I would like to know more, so if I have [**multiple conditions**](https://community.oracle.com/message/1328516#1326516), the more I include in sql the better? — Khalil Khalaf, Aug 22 '17 at 16:37
Related: https://softwareengineering.stackexchange.com/questions/171024/never-do-in-code-what-you-can-get-the-sql-server-to-do-well-for-you-is-this/171033 — mechalynx, Aug 22 '17 at 16:38
also if you have multiple condition and join .. also if you have complex condtion .. the best practice is this .. ever sql .. for filter the result .. rarely ...but very rarely ..could happend that you need code but when sql can't do the work . — ScaisEdge, Aug 22 '17 at 16:39

score 1 · Answer 1 · answered Aug 22 '17 at 16:41

There are two reasons to use sql to filter your records. One is to reduce the amount of data flowing across the network. The second is to minimize the amount of RAM required by your application. If you bring in too much data you can get heap space problems.

score 1 · Accepted Answer · answered Aug 22 '17 at 16:42

There are two things to consider here:

Pulling the smallest, most efficient result set possible
Hitting the DB the least amount of times possible

Almost always you will want to focus on pulling the smallest, most efficient result set possible. ie. do your filters in your SQL.

The only time you should filter in code instead of your SQL query is if you are already required to pull an entire table into memory and will filter to view it differently later.

score 0 · Answer 3 · answered Aug 22 '17 at 16:40

0

Leveraging Sql features for filtering will be: - performant - reduced network bandwidth usage - maintainable - quick to implement etc After all Database is to store data and carry out CRUD operations

answered Aug 22 '17 at 16:40

Sunil Singhal

593
3
11

Conditions in SQL statement VS conditions later in code

3 Answers3