2

Is it currently worth it to use filters and operations inside SQL language? i have been working lately with heavy tables and it just seems faster to bring the whole table and use any tool the current programming language has to do whatever i need with the table.

While i understand it should come to the machine running the program, arent most machines currently capable of outperforming the processing of a query with just table variable tweaking? or is it just a lucky shot i had been having better results this way?

cg7
  • 189
  • 1
  • 15
  • For any significant case it seems *highly unlikely* that fetching all of the data and filtering in the application would somehow be more efficient than filtering in the database. Mature database systems are *extremely efficient* with querying that data. As to whether or not *your specific code* has a bottleneck in your SQL queries, table designs, etc. is a different question entirely. But *in general* it's far more efficient to filter at the source of the data than at the destination of the data. – David Jun 26 '19 at 14:38
  • 1
    To illustrate, consider an analogy... Is it more efficient to decide which groceries you want at the store and only bring those ones home, or is it more efficient to bring all groceries from the store home and then decide what you want to keep and what you want to throw away? (Assuming all groceries are free.) – David Jun 26 '19 at 14:39

1 Answers1

3

Short answer, it is faster to filter in the database. The reason you are getting faster results from filtering your data in your application is probably due to bad database design. Often caused by missing indexes.

For an example, say you have a table named Student that represented all the students in a specific college. Let's also assume that this has a field to represent their GPA and that we want a query that returns only students with a GPA higher than 3.0. If the field GPA had a proper index on it, it would always be faster to do check for this in the SQL query itself. If you did not have a proper index on the GPA field, it may be faster to do this in application code.

If you want to know more about database indexing, here's another question someone asked about that: How does database indexing work?.

naedozi
  • 58
  • 7