Member Avatar for apanimesh061

Suppose I want retrieve values froma table that are greater than 10, should I first store all the values in a list and then check condition of ">10" using any language or should I just just retrieve all values directly from databse using query :
Select id from table where id > 10;

Which is the better way ?

Please help!
Thanks!

  • 4 Contributors
  • 3 Replies
  • 109 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by ~s.o.s~
Member Avatar for stultuske

depends on more than just that.
do you need it often? is it always the same query? do you have rights on the DB? ...

Member Avatar for richieking

Also is the data supposed to be used often and saved for future checkups...?
if the above is false, then keeping in array is the best idea cos it is only for a short period of time.

Member Avatar for ~s.o.s~

Suppose I want retrieve values froma table that are greater than 10, should I first store all the values in a list and then check condition of ">10" using any language or should I just just retrieve all values directly from databse using query :

Almost always the latter. Let's consider a scenario:

  • You write the code to load in all the values from the table into your program memory
  • It works great for a couple of hundred rows so you leave it as it is
  • The number of rows in your table keep growing from a few hundred to thousands
  • Now, just for a single request you need to load the entire table in memory and perform the filtering
  • The number of clients keep increasing and suddenly BOOM :)

The problems with loading everything in-memory are:

  • Memory issues for all practical purposes which result from loading everything in-memory
  • Increased I/O cost which will basically lock up a connection till the entire data is transferred from the database server to the client

You shouldn't shy away from off-loading the processing to a database since pretty much every industry strength database (PostgreSQL, Oracle etc.) handles heavy row crunching/processing like a champ esp with indexes enabled on selected columns. Processing stuff at the data source is almost always efficient rather than bringing the data to a new location (a different process / machine) and then processing it.

Edited by ~s.o.s~
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.