-1

I have come from an environment where I was taught to use objects and employ OOP techniques where possible and I guess it has guided me down a particular road and influenced my product designs for quite some time.

Typically my data access layer will have a number of classes which map onto database tables, so if I need to display a list of Companies, I will have a 'Company' object and a database table called 'company'. The company object knows how to instantiate itself from a DataRow from the database read using a 'SELECT * FROM company WHERE id = x' type query. So when-ever I display a companies list I will populate a list of company objects and display them. If I need to display attributes of the company I already have the data loaded.

It has been mentioned that 'select *' is frowned on and my object approach can be inefficient, but I am having issues identifying another way of working with a database table and objects which would work if you only read specific fields - the object just wouldn't be populated.

Yes I could change the list to directly query just the required fields from the database and display them but that means my UI code would need to be more closely linked to the data access code - I personally like the degree of separation of having the object separating the layers.

Always willing to learn though - I work on my own so don't always get up to speed with the latest technologies or methodologies so any comments welcome.

user1169502
  • 368
  • 1
  • 5
  • 13
  • 1
    Though I'm not necessarily frowning upon `*`, I don't get your problem here. If your point of view is the object and it knows its persistent representation, then it exactly knows its properties and to what columns these map to too. So it could easily create a list of columns, can't it? That's got nothing to do with the UI at this point. What stuff actually is shown to the user or not is still some steps away. – sticky bit Sep 03 '18 at 20:54
  • Yes I guess it could but what would be the point? Is select * any worse than select followed by a long list of column names? I guess my question is more about what techniques do others use as I haven't had the opportunity to work with other programmers for probably 10 years or so now. I have adopted the object maps to table approach and stuck to it for a long time - may not be the best approach though! – user1169502 Sep 03 '18 at 21:03
  • 2
    Well, the argument is, `*` really gets everything, even if it's not needed. But if this a has noticeable impact is another question. Say for example you have a table with large BLOBs but these aren't needed for the common tasks on the corresponding object in the application, then not fetching these BLOBs until they are really needed might have a noticeable impact. Another example is a partitioned table (column wise). Just selecting always all columns would render that pretty much useless. – sticky bit Sep 03 '18 at 21:23
  • And then of course the connection quality plays also a role. Is the database server on the same machine and the data comes over Unix domain sockets or the loopback interface or is there a LAN or even a WAN in between. Depending on that (and maybe even more) it can be a good thing to only fetch a minimum, that is needed and fetch the bulky stuff only on demand. Like a getter that fetches the BLOB if it really is accessed an hasn't been fetched before. But for a handful of names and numbers, I guess the impact is neglectable, if continuous fetching of small bits not even has the larger impact. – sticky bit Sep 03 '18 at 21:23
  • 2
    A less code-intensive option is to use an ORM (object-relational mapper) such as Entity Framework which effectively writes most of the data layer code for you, so you can focus on the business logic. So e.g. if you wanted a list of all companies in region 1, in EF-speak you'd just write something like `List companies = db.Companies.Where(c => c.Region == 1).ToList();`. All the SQL is autogenerated by EF. It's the same principle as your current setup. Other ORMs are available, you can search. Occasionally with more complex stuff they can generate inefficient SQL, though. – ADyson Sep 03 '18 at 21:24
  • You can of course also make EF only select certain columns etc. BTW working alone doesn't mean you can't keep up with everything - there's an overwhelming quantity of blogs, news sites etc covering developments in programming, plus you can attend conferences etc, etc. It's maybe harder than if you have peers to bounce off, but there are online communities like this one to learn from too, and local "real-world" meetup groups exist in many places for software-related topics. – ADyson Sep 03 '18 at 21:25
  • IMO, not using an ORM is a far worse sin than select *. You really shouldn’t be manually writing any SQL at all. Like @ADyson says there are plenty of great frameworks out there that do all that nastiness for you. – Dave M Sep 04 '18 at 01:15
  • @DaveM there are plenty of reasons not to use an ORM where the circumstances don't suit. I worked on an app recently where the SQL was very complicated and Entity Framework produced really inefficient SQL. As soon as we converted the most complex queries into stored procedures, performance of those queries went up by 100s of %. ORMs will work nicely for the majority of cases, but you have to be wary of certain cases. There's nothing nasty about SQL. Hourses for courses. – ADyson Sep 04 '18 at 06:04

1 Answers1

0

I don't think I can show you a definitive solution to that, but I'll try pointing you on the right direction (since it's more of a theoretical question).

Depending on the design pattern you follow on your application, you could decouple the data access layer from the UI and still follow this rule of not fetching all columns when they are not necessary. I mean, chosing the right design pattern for an application can bring you this sort of easyness.

For example, maybe you could interpret the less detailed version of an object as an object itself (which honestly I don't think it would be a good approach).

Also, I'll comment that the very popular rails ORM ActiveRecord only fetches from DB when the data is used. Maybe you could use a similar logic to track not only when but which columns will be used so you could limit the query.

Lucas Wieloch
  • 818
  • 7
  • 19