Should I get all database table fields if I don't really need them?

Question

I have a class Employee which has some fields:

EmployeeID, FirstName, LastName, Age, Rank

And I want to just get all employees from database and print their names in ComboBox, so what I really need is:

EmployeeID, FirstName, LastName

My question is: Should I always get all fields from db to let's say fill all of my class fields, or just the ones I need?.

PS. Guys some time ago I asked a question which hasn't been really answered: Should a class have string fields for values from SQL JOIN from dictionary tables I would be really happy if you could maybe take a look at it and share your thoughts. Thanks.

Answer 1

You need to consider what you've contractually agreed to with the architecture you've implemented. Let me explain...

It depends on what you are using as your data transport mechanism.

If you are using an ADO data table, then simply fetch what you need. The consumer is expecting a data table and should test that the columns it expects are present. Note that this is not contractually sound - a column can be missed out and you'll get a runtime error. Columns can get swapped around and you can potentially cause a runtime error.

However if you are filling a data object then you need to get everything. This is because you've contractually agreed to provide the consumer of that object with all the data that should be in that data object. Of course this means you will have to consider the design of your data objects - they shouldn't just mirror the data table or view that the data is extracted from. If the source of the data is 80 columns wide and you can't restructure that data source then you should consider having a lightweight and heavyweight version of your data object, and only use the heavyweight version when you have to.

You should also consider the amount of rows you are retrieving at any one time - are you being very selective, or are you grabbing everything including rows you'll never use?

Answer 2

Should I always get all fields from db to let's say fill all of my class fields, or just the ones I need?

Yes. You should get what you want!

Dont get all the columns from the tables as that would be unnecessary for you.

Even if you take all the columns from the table and if you are not using them then that is of no use and also you are increasing the work of your SQL Server.

On a side note:-

This may depend upon your need also ie, if you want to reduce the number of database hits then you may fetch all the records at once. So in short it may depend upon how frequently you are accessing your database.

Answer 3

My opinion There are 2 ways of looking at this if this is the only time you will ever use this class Employee data and you will never need all the values for the table then just have the class select only the fields you need the performance will be faster But in case you will use these values somewhere else in the program then i would select all the values because later u might have to change or make a new one to get the values u need. and I personally like having one class do 2 jobs rater then having 2 classes.

Answer 4

Normally it's best to get only what you need - reducing the amount of data you fetch reduces the time taken to fetch it, so improves performance. It also means that your application is more resilient to change (e.g. if you replaced Age with DOB you'd need to update your application from taking an integer called Age to taking a date called DOB assuming you requested all fields - if you only requested those you needed (i.e. not age / dob) that change would not affect you.

That said, if you're populating a class it may be a good idea to fetch all columns at once - since if the class holds all of this information it's possible it may be accessed at some later point, and rather than fetching individual columns as they're needed, one up front fetch followed by pulling the data from cache would be faster overall.

So it's a case of weighing up the performance impacts and any other factors of concern (e.g. maintenance effort) based on how you expect your application to be used. There is no blanket rule; only guidelines as to what factors should influence your decision.

Answer 5

In general client-server programming the better way is to send only needed data from server to client, so general answer to your question is: "you should get only needed fields from database, this will reduce network traffic and packet parsing time"

but, if you're using DAO approach, ie each object in your code presents table from the db - you have to fetch all, because you need fully initialized object

contradiction? No, if you want to keep your DAO objects, but fetch only needed data you will need to have 2 objects:

1. FullEmployeeInfo - this object will have all fields from DB, this object will be used during DB update/full info presentation
2. PartialEmployeeInfo - this object will have only part of fields and will not be used to update data in DB, it will be just readonly object

so, for fast selection and displaying in combobox you will use 2nd one with query which returns only partial information

Answer 6

The strait forward answer is "No, you should only fetch the fields you need."

But there is always a trade off, do you want to have more or less duplicate code for different views of the same table. It depends upon how often you fetch the data, how many rows, the size of the fields.