Better to use SQL or Higher Level language?

[ronnyjljr]

Hi there,

I am having a debate with my co-worker and I hoped some of you could lend some insight.

Is it better to issue an SQL statement that limits the number of items returned based on your query? or, Is it better to return all the items from the table and then use a higher level language like Java or C++ to sort through the result set one by one?

Such as, what if I want to query table A that has 1,000,000 entries, each having 5 columns. Is it better to return all the results to find entries 'foo' and 'bar', or 'foo' through 'bar' or just limit the results statement at the SQL level?

I say it's better to limit it at the SQL level, to reduce the load on the client computer.

Consider you answers, would they be the same if the table have 100 entires? or 100,000 entries? How about several million entries?


Thanks,
Ron

typedef map<GiantX,gold, less<std::shortestpathtogold> > AwesomeMap;

 

[MarcLodge]

Hi Ron,
I guess it depends on the efficiency of the SQL. If the table is many 1000's or millions of rows in number and the query you are able to write does not hit any indexes, then I would consider the programatical route, but..... if indexes are able to be used, then, as a general rule, I would allow DB2 to do it's stuff.

As a very general rule of thumb, DB2, if matching indexes when filtering, will do a better job of it than a programming language, and I'm talking ANY programming language. As I said earlier, this is a general rule.

But......

If you have a situation where a straight forward piece of SQL is not possible and you have to start coding EXISTS or using functions or accessing the data more than once by using UNIONs, then I would look into using a programming language in order to give you the results and response you require.

I don't think there are any hard and fast rules on this, it's more a horses for courses type of thing.

Hope some of this is of help

Marc

 

[ddiamond]

My experience has been that even if your query does have to use exists, unions, and sub-queries, you are still better off doing it with sql if it performs well. And you can write extremely complex queries that perform well. This is especially true if you have to bring the data over a WAN.

Now if you are only dealing with a small amount of data, it comes down to personal preference. I generally would still us sql, but some of my co-workers prefer to do it in code.