CODEWITHSUNDEEP
.netdatabaseperformance
jammus
jammus

Reputation: 2550

One method for creating several objects or several methods for creating single objects?

If I have the following:

Public Class Product
    Public Id As Integer
    Public Name As String
    Public AvailableColours As List(Of Colour)
    Public AvailableSizes As List(Of Size)
End Class

and I want to get a list of products from the database and display them on a page along with their available sizes and colours, should I

  1. have one method (GetProducts()) which makes use of a single view that joins the relevant tables, that then loops through each row and creates the objects as required? Or…
  2. have several methods which are responsible only for creating one object each? eg. GetProducts(), GetAvailableColoursForProduct(id), etc

I'm currently doing a) but as I add other other properties (multiple images, optional tassels, etc) the code is getting very messy (having to check that this isn't the same product as the previous row, has this colour already been added, etc) so I'm tempted to go with b) however, this will really ramp up the number of round trips to the database.

Upvotes: 0

Views: 235

Answers (8)

Anthony Mastrean
Anthony Mastrean

Reputation: 22394

You should look into Castle's ActiveRecord ORM, which works on top of NHibernate. You develop a data model (like you've done with 'Product') which inherits from AR's base class, which provides great querying abilities. AR/NHibernate provide aggressive query optimization and caching. Performance and scalability problems may disappear. At the very least you have a decent framework within which to tweak. You could easily split your data models up to test B.

Upvotes: 0

Richard Dorman
Richard Dorman

Reputation: 24110

If you are using any of the agile tenants in your coding practises then "a" is fine for now but as the complexity of your query grows you should consider refactoring, that is, build your code based on what you know now and refactor when necessary.

If you do refactor I would suggest introducing the factory pattern into your code. The factory pattern manages the creation of complex objects and allows you to hide the details of object construction from the code that consumes the object (your UI in this case). This also means that as your object becomes more complex the consumers will be protected from the changes that you may need to make to manage the complexity.

Upvotes: 0

joelhardi
joelhardi

Reputation: 11169

You're probably best off benchmarking both and finding out. I've seen situations where just doing multiple queries (MySQL likes this) is faster than JOINs and one big slow query that takes a lot memory and causes the DB server to thrash. I say benchmark because it's going to depend on your database server, how much memory and concurrent connections it has, sizes of your tables, how your indexes are optimized and the size of your typical recordsets. JOINs on large unindexed columns are very expensive (so you should either not do them or add indexes).

You will probably also learn a bit more/be more satisfied in the end if you write at least a little of both implementations (you don't need to write the methods, just a bunch of test queries) and then benchmark, vs. just going with one or the other. The trickiest (but important) part of testing though is simulating concurrent users hitting the DB at the same time -- realistic production memory and cpu load.

Keep in mind you are dealing with 2 issues: One is the DBA issue, how do I make it fastest and most efficient. The second is the programmer who wants pretty, maintainable code. (b) makes your code more readable and extensible than just having giant queries with complicated JOINs, so you may decide to prefer it over (a) as long as it isn't drastically slower.

Upvotes: 1

Veynom
Veynom

Reputation: 4147

b would be faster (performance wise) while reading your setup but it will require you more maintenance code when you will update your class (updating each function).

Now if performance is your true goal, just benchmark it. Write both a and b, load your DB with a few (hundreds of) thousands record and test. Then select your best solution. :)

/Vey

Upvotes: 0

Josti
Josti

Reputation: 63

Go for Option b) it makes your attributes independent from the Presentation of the Data (e.g. a table)

I think you would benefit from learning more about the MVC-Architecture. It stands for Model (Your Data -> Product), View (the Presentation -> Table) and Controller (a new Class that will gather the Data from the Model and processes it for View output)

Confused? It isn't that complicated. Which language is your code snippet from? Many Frameworks like Ruby on Rails, Java Struts or CakePHP practice this seperation of Program layers.

Upvotes: 0

Bob Dizzle
Bob Dizzle

Reputation: 1163

In the case above I would probably just have a single static load method especially if all or most of the properties are normally needed:

Public static function Load(Id as integer) as Product

Product.Load(Id)

If say the color property is rarly used and fairly expensive to load then you may want to still use the above but not load the color property from there but dynamically load it from the getter like so:

private _Colors as list(Of Color)
public property Colors() as List(Of Color)
    get
        if _Colors is nothing 
            . .. . load colors here
        end if
    end get. . . . .

Upvotes: 0

JipFromParis
JipFromParis

Reputation:

You got it. Solution b won't scale up so solution a is key, as far as performance are of concern. By the same time, why should you constrain GetProductDetails() method to grab every data in a single request (hence the SQL view approach) ? Why not have this method perform 3 requests and say goodbye to your messy logic :

  • One for id and name retrieval
  • One for the colors list
  • One for sizes list

Depending on the SQL engine you use, these 3 requests could be grouped in a single batch query (one round trip) or would require 3 reound-trips. When adding additional properties to your class, you will have to decide whether to enhance the first request or to add a new one.

Upvotes: 1

user1228
user1228

Reputation:

Personally, I'd get more data from the database through fewer methods and then bind the UI against only those parts of the data set that I currently want to display. Managing lots of small methods that get out specific chunks of data is harder than getting out large chunks and using only those parts you need.

Upvotes: 0

Related Questions