Best practice ASP SQL

[ThatsIT.net.au]

I was wondering about performance implications for 2 ways of creating a
collection of objects.

Lets say I have 2 classes category and subcategory

in the category class I have a function that returns a collection of
subcategory objects.

what I do here is open a reader to a table in the database with the
subcategory info in it.
I then call the subcategory class each loop of the reader creating an array
of objects like so

myarray(n) = new subcategory(id,name,other,other,other...)

as you can see am passing all the data from the database table to the class
so that no trip to the database is needed from the subcategory class all
data is supplied.

Now I know that this second way is not as efficient as it make many trips to
the database like this

myarray(n) = new subcategory(id)

passing the id only I then have to load the other data from the database
each time a subcategory object is created.

Now I know that this is a performance cost but how much of a coast I don't
know. some times these second way is a real time saver where there is much
data to pass across. since in the second way you are only getting one row
from the database each time the amount of data retrieved from the database
is the same but there is many connections to the database the second way.

Should I worry too much about this overhead?

 

[Luis Roquette Valdez]

Hi there,

It's quite a picke you got there... lol...

Well... I'd advise you to get your SQL profiler out of your pocket and test
it by yourself...
I would personally just pass the ID and let my method get all the data it
needed for it. This way, next time you add another different column you want
to get from your database, all you need to change is your method and not all
calls to it... but that's about good/bad development and not SQL...

Do have a look at Profiler... get the times from it and make your judgement
based on that...

Cheers

 

[Arne Vajhøj]

I was wondering about performance implications for 2 ways of creating a
collection of objects.

Lets say I have 2 classes category and subcategory

in the category class I have a function that returns a collection of
subcategory objects.

what I do here is open a reader to a table in the database with the
subcategory info in it.
I then call the subcategory class each loop of the reader creating an
array of objects like so

myarray(n) = new subcategory(id,name,other,other,other...)

as you can see am passing all the data from the database table to the
class so that no trip to the database is needed from the subcategory
class all data is supplied.

Now I know that this second way is not as efficient as it make many
trips to the database like this

myarray(n) = new subcategory(id)

passing the id only I then have to load the other data from the database
each time a subcategory object is created.

Now I know that this is a performance cost but how much of a coast I
don't know. some times these second way is a real time saver where there
is much data to pass across. since in the second way you are only
getting one row from the database each time the amount of data retrieved
from the database is the same but there is many connections to the
database the second way.

Should I worry too much about this overhead?

The first way is OK.

The second way is bad. It will cost in performance.

The third (!) way i OK.

It is the same as the second but with an important twist. You
only load categories and hand it to the caller. When the caller
then tries to access the subcategory information it get loaded.
If subcategory information may not be needed or only be needed
for one or a few categories, then this lazy loading may even
perform better than the first way.

Arne

 

[Mr. Arnold]

I was wondering about performance implications for 2 ways of creating a
collection of objects.

Lets say I have 2 classes category and subcategory

in the category class I have a function that returns a collection of
subcategory objects.

what I do here is open a reader to a table in the database with the
subcategory info in it.
I then call the subcategory class each loop of the reader creating an
array of objects like so

myarray(n) = new subcategory(id,name,other,other,other...)

as you can see am passing all the data from the database table to the
class so that no trip to the database is needed from the subcategory class
all data is supplied.

That's the way I would do it.

Now I know that this second way is not as efficient as it make many trips
to the database like this

myarray(n) = new subcategory(id)

passing the id only I then have to load the other data from the database
each time a subcategory object is created.

Now I know that this is a performance cost but how much of a coast I don't
know. some times these second way is a real time saver where there is much
data to pass across. since in the second way you are only getting one row
from the database each time the amount of data retrieved from the database
is the same but there is many connections to the database the second way.

Should I worry too much about this overhead?

I don't know why you wouldn't have a sproc with two T-SQL statements.

1) to select category
2) to select subcategory by category-id assuming that there is a parent
child relationship.

If you do that with two Selects pulling the data, then you can do a read on
category with a data reader building its array of objects. Then you do a
Result Set.Move Next and with a new data reader read the subcategories into
its array.

 

[ThatsIT.net.au]

thanks I know profiler, but a bit to lazy to use it when there is people
that have analyzed thing better then I would who I ca ask, but for now I
will just pass the id in this project because its never going to be a
scalability problem, but would like to know for further projects what is the
cost

 

[ThatsIT.net.au]

That's the way I would do it.



I don't know why you wouldn't have a sproc with two T-SQL statements.

1) to select category
2) to select subcategory by category-id assuming that there is a parent
child relationship.

If you do that with two Selects pulling the data, then you can do a read
on category with a data reader building its array of objects. Then you do
a Result Set.Move Next and with a new data reader read the subcategories
into its array.

the maintenance, if a column is added, you need to alter code as well as
depending on the size of the table can be a lot of work when you have many
linked classes exposing its collections this way.

 

[ThatsIT.net.au]

The first way is OK.

The second way is bad. It will cost in performance.

The third (!) way i OK.

It is the same as the second but with an important twist. You
only load categories and hand it to the caller. When the caller
then tries to access the subcategory information it get loaded.
If subcategory information may not be needed or only be needed
for one or a few categories, then this lazy loading may even
perform better than the first way.

Arne

I only put forward 2 ways, so I assume by 3 you mean 2
myarray(n) = new subcategory(id)

 

[Arne Vajhøj]

I only put forward 2 ways, so I assume by 3 you mean 2
myarray(n) = new subcategory(id)

I explain above what the third method is.

Arne

 

[ThatsIT.net.au]

I explain above what the third method is.

Arne

I think that pretty much is what I'm doing, only when you call the method
getsubcategories from the categories object are they loaded but all are
loaded at that time

 

[Tom Dacon]

You're right that making each object do a separate select for its data is a
loser from a performance standpoint. It's that kind of thing that gave
n-tier object-oriented programming a bad name for performance back in the
early days when people were working this problem out.

The way I do this is I have the collection execute a stored procedure that
returns a DataReader with one row for each of the objects that it is going
to contain. Then I write a constructor for the class that represents the
content objects, that takes a DataRow as its single argument. Each instance
of the contained class then populates itself from the contents of the
DataRow.

One select, and an iteration over the contents of the DataReader, and all
the contained objects are populated. Short and sweet. And the container
class doesn't have to know anything about how the objects populate
themselves (except for the stored procedure, of course).

Try it out.

The one small downside of this is that the contained object also has to have
a stored procedure that selects the same columns, for the case where it
needs to populate itself separately from the collection. So there's a small
amount of duplicated code in the two stored procedures, and if you change
the table or view you need to make corresponding changes in two places. But
it's never been a problem for me.

Tom Dacon
Dacon Software Consulting

 

[Mr. Arnold]

the maintenance, if a column is added, you need to alter code as well as
depending on the size of the table can be a lot of work when you have many
linked classes exposing its collections this way.

This is not correct. They are not linked objects. They are two individual
selects and reads of the data that happen in sequence, and the objects are
independent of each other. It just happens that you read the data for both
tables with one call to the database.

 

[Mr. Arnold]

You're right that making each object do a separate select for its data is
a loser from a performance standpoint. It's that kind of thing that gave
n-tier object-oriented programming a bad name for performance back in the
early days when people were working this problem out.

The way I do this is I have the collection execute a stored procedure that
returns a DataReader with one row for each of the objects that it is going
to contain. Then I write a constructor for the class that represents the
content objects, that takes a DataRow as its single argument. Each
instance of the contained class then populates itself from the contents of
the DataRow.

One select, and an iteration over the contents of the DataReader, and all
the contained objects are populated. Short and sweet. And the container
class doesn't have to know anything about how the objects populate
themselves (except for the stored procedure, of course).

Try it out.

The one small downside of this is that the contained object also has to
have a stored procedure that selects the same columns, for the case where
it needs to populate itself separately from the collection. So there's a
small amount of duplicated code in the two stored procedures, and if you
change the table or view you need to make corresponding changes in two
places. But it's never been a problem for me.

Tom Dacon
Dacon Software Consulting

Yes, use a sproc that has two selects in it no inners joins. You have to
different results sets returned. You use datareader to built the first
collections of objects. You do a Resultset.MoveNext and with another
datareader, one builds the second set of objects.

Yes, short a sweet and you only need to access the data once.

 

[Cor Ligthert[MVP]]

In my idea it is all wrong practice. It depends very much if you will update
that data or not.

However still is taking the less data as needed is the best way. Selecting
column by column is real a bad practice.

Cor

 

[Michel Posseth [MCP]]

If you are really so worried about perfomance then it would also be a good
idea to implement caching
you should really perform a load test with the expected amount of traffic on
your site .

My personal opinion is load what you need when you need it, and about all
those connections don`t worry they invented connection pooling for that
some smart guys at MS have already taken care of that .

HTH

Michel Posseth

 

[ThatsIT.net.au]

If you are really so worried about perfomance

Well i'm not overerly concerned, but i just wanted to know how bad it it was

then it would also be a good

idea to implement caching
you should really perform a load test with the expected amount of traffic
on your site .

I will use caching but not in the classes themselves

My personal opinion is load what you need when you need it, and about all
those connections don`t worry they invented connection pooling for that
some smart guys at MS have already taken care of that .

Now that what I wanted to here, its not the end of the world?
This is the way I would like to do it as it makes coding more simple and
more elegant

 

[Michel Posseth [MCP]]

Now that what I wanted to here, its not the end of the world?
This is the way I would like to do it as it makes coding more simple and
more elegant

It is always good coding practice to try to keep things as simple as
possible , also for future maintainebility this pays back

With web projects for a fact you are in a luxury position , with a desktop
app the user is annoyed after just a few seconds of no response while with a
web app the user becomes annoyed after 8 seconds of no interaction .

It is hard to tell wich of the 2 solutions would give the highest perfomance
in a ASP.Net scenario as solution 2 would consume more memory and thus a
recycle of the ASP.NET worker process is more frequent to occure , so what
started out as a possible perfomance boost might in the end degrade the
perfomance of the app

So my opinion would be try to keep it as OOP as possible and keep the
thought in mind that the web should be stateless so this means in short a
challenge response scenario , MS SQL server is perfect in this scenario as a
db backend due to its superior connection pooling mechanism .

HTH

Michel Posseth

 

[Arne Vajhøj]

I think that pretty much is what I'm doing, only when you call the
method getsubcategories from the categories object are they loaded but
all are loaded at that time

No.

The key difference is between getting all subcategories now and only
getting those needed when they are needed.

Arne

 

[ThatsIT.net.au]

It is always good coding practice to try to keep things as simple as
possible , also for future maintainebility this pays back

With web projects for a fact you are in a luxury position , with a desktop
app the user is annoyed after just a few seconds of no response while with
a web app the user becomes annoyed after 8 seconds of no interaction .

It is hard to tell wich of the 2 solutions would give the highest
perfomance in a ASP.Net scenario as solution 2 would consume more memory
and thus a recycle of the ASP.NET worker process is more frequent to
occure , so what started out as a possible perfomance boost might in the
end degrade the perfomance of the app

So my opinion would be try to keep it as OOP as possible and keep the
thought in mind that the web should be stateless so this means in short a
challenge response scenario , MS SQL server is perfect in this scenario as
a db backend due to its superior connection pooling mechanism .

HTH

Michel Posseth

thanks for your input

 

[tomilay]

I was wondering about performance implications for 2 ways of creating a
collection of objects.

Lets say I have 2 classes category and subcategory

in the category class I have a function that returns a collection of
subcategory objects.

what I do here is open a reader to a table in the database with the
subcategory info in it.
I then call the subcategory class each loop of the reader creating an array
of objects like so

myarray(n) = new subcategory(id,name,other,other,other...)

as you can see am passing all the data from the database table to the class
so that no trip to the database is needed from the subcategory class all
data is supplied.

Now I know that this second way is not as efficient as it make many tripsto
the database like this

myarray(n) = new subcategory(id)

passing the id only I then have to load the other data from the database
each time a subcategory object is created.

Now I know that this is a performance cost but how much of a coast I don't
know. some times these second way is a real time saver where there is much
data to pass across. since in the second way you are only getting one row
from the database each time the amount of data retrieved from the database
is the same but there is many connections to the database the second way.

Should I worry too much about this overhead?

There is a performance hit each trip to the database. This may not be
a big issue depending on circumstances. However you could use XML to
get the categories and sub-categories from the database in one trip.
You can then serialize this XML into your collection using the
XMLSerializer class.

 

[sloan]

If you look at my examples (at the URL below)

I use the IDataReader and the .NextResult to hit the database one time.

However, I use the (results) to populate objects and child collection
objects.

Its based on Northwind

Customer --> Orders --> Order_Details

http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!139.entry

I use Controller(or Manager) classes to control the "correct level of
deepness" ......

Sometimes I want a list of customers. Sometimes I want a customer with
their orders.
Sometimes I want a customer and their orders and their order details.

That's what the Controller class takes care of. Not too much, not too
little, "just right".

Code is downloadable and if you check the blog, there is a 2.0 version as
well.