Is it a good or bad to fetch the parameters used to filter a query in SQL?-CodePudding

I have a table of Users that is being used as part of a Web Application.

 ------------ 
|   Users    |
 ------------ 
| ID         |
| FirstName  |
| LastName   |
| Country    |
| BloodGroup |
| Phone      |
 ------------

I need to SELECT Users by their Country and their BloodGroup.

My question is - Is it better/worse to SELECT the given parameters (Country and BloodGroup)? Do they provide any benefit? If yes, why? (Because those parameters are already present in the WebApp and its a waste of passing them via network)

SELECT *
FROM Users
WHERE Country='given_country' AND BloodGroup='AB ';

SELECT ID, FirstName, LastName, Phone 
FROM Users
WHERE Country='given_country' AND BloodGroup='AB ';

CodePudding user response：

Avoid SELECT *.

It is almost always better, in production software, to enumerate the columns you want from a query instead of using SELECT *.

Why?

you don't waste network bandwidth or RAM on data you don't need.
the optimizer -- query planner -- may be able to do some tricks to speed things up when it knows exactly what you want.
there's one less thing to break in future if somebody adds a column to the database.
the next person working on the code (maybe yourself two years from now) will have an easier time understanding how you are handling your data.
If you have JOINs you can get duplicated columns. Wasteful.

The point of SQL database technology is to allow your apps to handle datasets many orders of magnitude larger than the RAM you have. It does that by allowing you to SELECT the precise data you need. Do that.

CodePudding user response：

Are your constraints on data transfer really that tight? You are right that first query is transferring data, which already exists, but i mean do you really need this optimization? If you don't, don't bother.

With your second approach you run the risk of having to iterate the entire collection again to add missing data in your dtos, which might be undesirable as well. To avoid it you may do something like:

SELECT ID, FirstName, LastName, Phone, 'given_country' AS Country, 'AB ' AS BloodGroup
FROM Users
WHERE Country='given_country' AND BloodGroup='AB ';

But ask yourself again - is it worth the hassle? Such micro optimizations normally don't have good outcomes. Why premature optimization is the root of all evil - question1, question2