Too many objects loaded at the same time and cause OutOfMemory exception.

[elaine]

I'm working on a .net web application. The architect of this web
application is quite different than other web applications i worked
before. Since we use a set of tools to generate most of the basic
code. Every table in the database related to an object in the
application. There is no stored procedures in database; In every
object of the application, the tool generates methods for basic
database operations like insert, delete, update, selete, and methods
to compose a select query inside the table(object)

But it definitly is a bad design, since we have more and more users,
big performance issues coming out when generating reports for users.
For example, if i use this architect for a school, i can easily create
objects of grade, class, student, lesson, semester, now let's say i
have 6 grades, each grade has 10 class, each class has 20 students,
each student has 5 lessons. If i want to generate a score report for
all students's score in fall of 2006, i have to first load grade
object collection, loop into grade collection; load class object
collection, loop into class collection; load student object
collection, loop into student collection; load lesson object
collection, loop into lesson collection. I don't know how .net garbage
collection will work on this situation, whether or not it does load
all 6x10x20x5=6000objects in memory at the same time. I noticed a huge
memory overhead for aspnet_ws process, then i got Service Unavailable
error in my browser, and the memory of aspnet_ws back to normal.

Does anyone know how .net framework works on my situation? Any
resolution to this OutOfMemory issue except using stored procedure? I
don't think asynchronized process will help, what do you think? What's
the best way to scale out for the current system?

thank you for your help,
-Elaine

 

[Mark Fitzpatrick]

Elaine,
Stored procedures won't necessarily help here, they would simply
push the select and other data access mechanisms onto the database server,
though they will improve the speed of the queries slightly since they will
allow the database to pre-plan the implementation. Stored Procedures will
also drastically improve your security since they promote parametrized
queries and help avoid SQL Injection attacks. You're still stuck with the
application design overall. It seems to be a rather cumbersome design to say
the least. There should be a way to load objects without having to load all
of these collections. I think a better, more direct approach would need to
be taken. This would make sense if you were drilling down into a data
wherehouse and using a pivot table and incrementally loading data as needed.

The garbage collection should occur sometime after the page unloads.
You can attempt to speed it up by calling the dispose method on the
collections (if available) and then setting them to null. I don't think this
architecture will work very well as it doesn't sound very stable at all. You
should be able to create functionality to get this sort of data directly
from the db as there should be no need to call all these objects just get
the results of what should be a common type of sql query.

 

[elaine]

Hi Mark,
Thank you for your response.

First, I think Stored procedures will help me here, as you said in
your post "You
should be able to create functionality to get this sort of data
directly
from the db as there should be no need to call all these objects just
get
the results of what should be a common type of sql query. ", I think
stored procedure can do it for me. The reason i don't want to use
stored procedure here is this system is not small, each report might
need over 20 objects involved, i have to write complex stored
procedures. And besides reports, i think later on, other
functionalities might have the same issue as reporting, if i choose to
use stored procedures to feed all my need, it seems like i need to
rewrite the whole application.

I will try to add dispose method in the loops to see if this will
work.

I'm also interested in the way you mentioned "This would make sense if
you were drilling down into a data
warehouse and using a pivot table and incrementally loading data as
needed. ", can you give me some references to check about how to use
data warehouse in my case?"

Thanks again for your help.
-Elaine