November 11, 2008 at 11:31 am
Here's the scenario we have a wide table about 40 columns ...
Of these about only 10 columns are ever used in vast majority of scenarios and these are all indexed columns......
I recently found a developer had created a class, against a stored proc that brought back all columns in the table regardless whether or not they were used.
The argument given was that as a rule each table should have a class, and so if the developer identified they needed more elements from the table when writing the app they wouldn't have to change the class and DAO, the data would already be present.
My opinion was that the app tier should only ever retrieve and bring back what we needed since bring the entire record back would result in repeated potetnialy unessacry reads against disk and usage of Network to transmit data (if as in this case we don't use these other values).
The argument was also identified that this was inline with ORM techs such as LINQ to entitiess etc ...which is fine but were not using LINQ plus doesn't LINQ have options to only bring back what you need or something to that effect...
I'm just unsure where to go with this in my opinion you should only bring back what you need ..... I also recognise that the table is probabbly 2 wide but i can't change that...
Help opinions greatly welcomed
November 11, 2008 at 12:04 pm
Of these about only 10 columns are ever used in vast majority of scenarios and these are all indexed columns......
I am not sure if you meant anything by including the information about the indexing, but this statement struck me as weird and I would question why you think the indexing information is important to the discussion.
As for bringing back columns that are not used - both you and the developer are correct. It is bad to have to change the data access layer to get access to columns that are already in the table but not yet used, but including all columns will add overhead.
I would tend to disagree that for each table you should have a class. That suggests that your database and your object layer should be identical. That is usually not the case and often not desirable. For example, you may have a "Person" table and a "Person" class, but you may have a "Customer" class that inherits from your "Person" class and includes information from a "Person", "Order", and "Address" table. Part of the point of an object layer is to help translate well-normalized data into real-world business objects because they are pretty unlikely to match. Now, if you have a business-object layer and a data access layer and you want to do this translation in the business object layer and have the data access layer match the database structure that may make sense in some cases.
From all of that rant, what I am really saying is you have to weigh the additional overhead against the code re-useability. If your object layer only returns one record and the additional columns don't consist of 4gb of image data, it may be pretty low overhead to include everything. However, if you are building a system that will need to process 50,000 transactions per minute, a little overhead saved can mean the difference between working and not.
November 11, 2008 at 12:13 pm
Michael Earl (11/11/2008)
Of these about only 10 columns are ever used in vast majority of scenarios and these are all indexed columns......
This table is a core table i.e. its one of the most queried table in the system.
I have notice generaly querys covered by the indexes didn't result in many physical reads .... since i guess the entire index is loaded into cache but the entire table is not.
My understanding could be off here i'm not a DBA ..that's not an excuse do 🙂
November 11, 2008 at 12:48 pm
As a developer I only bring back the fields I want/need.
I can see your colleagues point though if they are doing some kind of code generation where classes/data objects are being built from a tables schema.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply