BASIC Object Oriented Cartographic Question asked by noob
I understand that in the interest of efficiency, when you query the database, you should only return the columns you want and no more.
But given that I like to use objects to store the result of a query, this leaves me with a dilemma:
If I only get the column values that I need in a particular situation, I can only partially fill the object. This, I believe, leaves my object in an imperfect state where only some of the properties and methods are available. Later, if a situation arises where I would like to reuse an object but find that the new situation requires a different but overlapping set of columns to be returned, I am faced with a choice.
Should I reuse the existing SQL and add additional fields to the list of selected columns that the new situation requires, so that the same query and object mapping logic can be reused for both? Or should I create another method that executes slightly different SQL, which only populates the properties of the object that were returned by the second query?
I strongly suspect that there is no magic answer and that the answer does "depend" on the situation, but I think I am looking for general advice. In general, my approach was to either return all columns from the requested table, or add additional columns to the query as needed, but reuse the same SQL (and collation code) code that as long as performance won't be a problem. In general, I find that unless you are fetching a large number of rows, and I usually don’t know that the cost of adding extra columns to the output has no discernible impact on performance and that the savings in development time and simplified API that result in a good tradeoff.
But how do you deal with this situation where performance becomes a factor? You create methods like
Employees.GetPersonalInfo Employees.GetLittleMorePersonlInfoButMinusSalary etc, etc etc
Or have you somehow created an API where the user of your API has to specify which columns / properties he wants to populate / return, thereby adding complexity and making your API less friendly / easier to use?
Let's say you want to get information about employees. How many facilities will typically be involved?
1) Employee object 2) An employee collection object containing one Employee object for each Employee row returned 3) An object such as EmployeeQueries that returns contains methods such as "GetHiredThisWeek" that returns a collection of Employees of 0 or more records.
I understand that this is all very subjective, but I'm looking for suggestions on what you found best for you.
a source to share
... the cost of adding extra columns to the output has no discernible effect on performance ...
Right. I don't quite understand that a "new situation" might arise, but it would be much better (IMO) to get all columns rather than run multiple queries anyway. Getting more columns doesn't require a lot of performance penalties (although more RAM will be required in queries, it shouldn't be a big issue, besides, the hardware is cheap). Plus, you would save a little development time.
As for the second part of your question, it really depends on you. As an example, Rails takes a more "user-friendly" approach, but that might not be what you want. It just depends on your needs. If you are willing to sacrifice a little convenience for performance, by all means, go for it. I would do.
a source to share
I would say make your application correct first and then think about performance in this case.
You can only optimize your queries to realize that you won't be using that query anyway. Create the most generic queries your application can use, and then, when you are confident that everything is working correctly, find problem areas as needed.
You probably won't need huge performance. Some say lazy programmers are the best programmers. Don't overpersuade things in front of you, create a separate Employee object.
If you need optimization, you will create a method / class or, however, your ORM library. This should be the exception to the rule; only do this if you have a reason to do so.
a source to share
If you are using your objects in a row-at-a-time CRUD application, then by all means, copy all the columns into your object, the additional overhead is minimal, and you will become truly recyclable for any program that needs row access to table.
However, if your SQL is performing a complex join or returning a large set of rows, query exactly and only what you want. You get two performance penalties here, each processing each column every time it consumes KPa without benefit, and the two DBMS systems have a bag of query optimization tricks (e.g., index-only access) that can only be used in case you specify exactly what columns you need.
In most of these cases, there is no reuse issue as the scan / search processes tend to be very specific to a particular use case.
a source to share