Wouldn't it be better to do a join between the two tables in the first place.

So instead of returniing a list of provinceid's you would get the province directly.

Or are there downsides to this method?

1. It's not object oriented
2. It causes duplicate data to be retrieved from the database as you'll get the same province data over and over.
3. Retrieving duplicate data reduces performance
4. It ties the two tables together in the query results when in fact they are distinctly seperate objects.
5. Now you MUST go to the database to get the province info
6. Always hitting the database reduces performance
7. As the amount of related data increases beyond just a single join to provinces, your queries can become unmanageable.

I do not entirely agree on that.

1) it is object orientated, only the objects look a little different (instead of returning an id for the province I return a provincename)
2) That is true, but the same goes for retrieving duplicate id's although the id's might use less Kb
3) Thats the same argument you gave under point 2
4) So what
5) You must always go to the database to get the provinceinfo, one way or another
6) Í will call the database only once, and in one call I get all the data
7) At a certain point things will become difficult, ofcourse. But it would also become difficult if you put that code in vb. So wether you would use difficult code in a stored procedure or in your vb code, yoú will always end up with difficult code at some point.

First, I am a little off track as to what is trying to be accomplished here.  If the person seeking help could wrap up the current issue and solution into a new post, that would make it easier.  (I just got sidetracked looking through all of this)

In reference to 7 there are a couple things to think about here:

• If the amount of data increasing (rows returned) makes your query unmanageable, you shouldn't be using that query in the first place.
• Using joins and seperate rows to retreive data is far more efficient than processing it in the code itself.  Joins are a very efficient way to accomplish things like this, if done correctly. This also reduces network traffic as you are not passing as much data from db server to web server.
• If you have a table you plan on using for lookups that will returns many results often, you should keep in mind that long and skinny tables perform well (many rows, but few columns)

Depending on what exactly you need, there are several ways to approach this to maxmize performance. 

nixie wrote:

I do not entirely agree on that.

1) it is object orientated, only the objects look a little different (instead of returning an id for the province I return a provincename)
2) That is true, but the same goes for retrieving duplicate id's although the id's might use less Kb
3) Thats the same argument you gave under point 2
4) So what
5) You must always go to the database to get the provinceinfo, one way or another
6) Í will call the database only once, and in one call I get all the data
7) At a certain point things will become difficult, ofcourse. But it would also become difficult if you put that code in vb. So wether you would use difficult code in a stored procedure or in your vb code, yoú will always end up with difficult code at some point.

1) tenet of object oriented programming - choose composition over inheritance
By melding the two objects into one, you end up with one object trying to manage two things.  in a composition design, you would have one object that may contain many other related objects.  the provinceinfo example is quite simple so it may not seem worth the extra effort but i have table that uses up to 10 foreign keys for related entities.  so anytime i want to look at my object i should use a join to pull aaaallll that data back??  no way.  i pull up my object and i fill my object graph only as deeply as required.  In some instances, this does result in extra hits to the database but by far our database activity has been significantly reduced.

2) i wouldnt consider duplicate id's to be duplicate data.

3) Duplicate data consumes additional memory.  duplicate data also increases network traffic from the webserver to the db server.  so duplicate data can affect your performance in more ways than one.

4) You decision to create a query that joins two objects creates a new object.  now you must manage the new object.  what happens when you need the original data but you dont need the province info?  do you just use your query and pull provinceinfo anyways.  what happens when you need companyinfo and provinceinfo, or companyinfo but not provinceinfo.  as you begin to create related tables, the number of permutations for queries begins to become exponential.  you end up spending a lot of your time setting up and managing these special case queries.

5) in the model i propose, i would go to the database theoretically once per application run to get the provinceinfo data.  not once per page.  highly reusable data is held in cache and i only need to go to the database when the data is missing from the cache.  the farther away from your webserver you need to go to get data, the more of a performance boost this will give your application.

6) There are [at least] two performance metrics to consider when building applications.  a) how quickly can i build it.  b) how will it perform.  if you create a unique query for every use case you will spend a lot of your development time coding and managing queries.  In my model, i may call the database once for that data and not need to hit the database again until the app restarts.

7) i disagree.  i cant remember the last time i wrote a stored procedure or even a query.  I use some highly reusable core classes that we created to get access to all our data.  our application coding has been immensely simplified because we work with objects and not database tables.  the details how these objects ultimately persist themselves is of no concern to the application. it is of concern only to the data access layer.  people get concerned that adding a "layer" may affect performance and indeed it may.  in our case we saw a 40+% performance increase at our data layer when we starting using business entity collections vs datasets.  This performance increase does not include any data caching.  We have also experienced a coding performance increase as these objects have been created to be highly resuable.  Code should not become overly difficult.  When it does, it is time to refactor.

thecrispy1 wrote:

First, I am a little off track as to what is trying to be accomplished here.  If the person seeking help could wrap up the current issue and solution into a new post, that would make it easier.  (I just got sidetracked looking through all of this)

In reference to 7 there are a couple things to think about here:

• If the amount of data increasing (rows returned) makes your query unmanageable, you shouldn't be using that query in the first place.
• Using joins and seperate rows to retreive data is far more efficient than processing it in the code itself.  Joins are a very efficient way to accomplish things like this, if done correctly. This also reduces network traffic as you are not passing as much data from db server to web server.
• If you have a table you plan on using for lookups that will returns many results often, you should keep in mind that long and skinny tables perform well (many rows, but few columns)

Depending on what exactly you need, there are several ways to approach this to maxmize performance. 

• agree
• partially agree.  joins are efficient if done correctly.  network traffic is only reduced on a join with a cardinality of 1 to 1.  1 to many joins increase network traffic as the "1" side of the join gets its data duplicated based on the "n" side of the join.
• for lookup type data, the biggest performance boost is to cache the data in the webservers memory so you do not need to repeatedly hit the database.

mbanavige wrote:

• agree
• partially agree.  joins are efficient if done correctly.  network traffic is only reduced on a join with a cardinality of 1 to 1.  1 to many joins increase network traffic as the "1" side of the join gets its data duplicated based on the "n" side of the join.
• for lookup type data, the biggest performance boost is to cache the data in the webservers memory so you do not need to repeatedly hit the database.

I am not sure why you would be returning multiple 1 to n's in a single query to populate a drop down list.  Isn't that what this thread started about?

It is not necessary to use cache and take up memory for lookup data if it is only used in one sport or very little.  For example, I would never use cache to store lookup data for something using an Admin module action like "Edit Configuration" if it wasn't used anywhere else.  So while making use of cache can be good it can also be very bad.  Thinking like this is why it takes GB's of memory to do anything on a windows machine these days. 

populating a DDL shouldnt require any joins to gather up the appropriate lookup data but this thread has apparently drifted into a broader discussion.

as far as using cache goes, i referred to "highly reusable data".  clearly we would all agree that memory should not be wasted trying to cache data that is considered a "one-off".