?
Solved

More than Two Relationships between Custom Entity Classes  Caused poor Performance

Posted on 2009-04-03
4
Medium Priority
?
467 Views
Last Modified: 2012-05-06
hi ,
i have a custom class calls User which contains the following 2 collections,
UserProducts (collection of Product class) ,  UserPictures (collection on Picture class).
now i want to retrive all the users from database , construct the User calss and then add the class to UserCollection,
i wrote a method to fulfill that requirement but the method takes more than 2 minutes to finished (the amount of the users in the database is about 40,000) , i must mention that i can't implement Lazy load pattern cause when the application is up i need all the data.
the construcor of the User calss is like followed :
1. i called stored procedure to retrive all the users from the database into datatable
2. fill primitive types of the user.
3. for each user i called a second stored procedure to retrive all the user products
4. for each user i called a third stored procedure to retrive all the user pictures

i tested the inner running time of the consturcor (using StopWatch) and i saw that step 3-4 takes 80% of the time.
what makes me think that the database round trip causes the lower performance.
what is the best approach for this situation ?
retrive all the data once into seperate datatable under one dataset and the question the appropiate datatable ?
implement inner join between all the 3 tables ? what happend when i want to add another relation?
thanks in advance.
0
Comment
Question by:YKMM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 11

Expert Comment

by:Anurag Agarwal
ID: 24070265
The best way to solve this problem is to load all the users first in to the collection (step 1 & 2 ) . And while using that collection in your application, load the secondry data (User Products & User Pictures - step 3 & 4) of user in question at that time.
So write your program in such way that is loads the secondary data whenever needed, not always for each & every user.
Other thing.. you said the User Picures you are loading for the user, are these the picture URLs or the blob data from database as pictures. If you are extracting the blob then it is very costly. so load it whenever needed.
Anurag
0
 

Author Comment

by:YKMM
ID: 24070377
hi Anurag
thanks for your response.
about paragraphs 1 and 2 : as i said , when the application starts i need that the picures and user products to be seen , so i need them at loadint time , another more thing the products and the pictures for each user are permanent (about 4-6 items).

for paragraphs 3 : the picures are just url's.
0
 

Author Comment

by:YKMM
ID: 24071527
hi
just updating.
i solve the problem.
what i did is to retrieve all the UserProducts ,  UserPictures , Users in 3 tables under Dataset
and then iterating through.
the total time reduced to few seconds.
thanks,
0
 
LVL 7

Accepted Solution

by:
illusio earned 2000 total points
ID: 24072776
The fastest solution is retrieving the data in one fetch. And then connect the objects. You can chain the commands and retrieve the data using a datareader (myDataReader.NextResult() does the trick to advance to the next resultset). In oracle you have to return three ref cursors, in the most other databases you just write three select statements and seperate them with a ";" (or return three selects with one stored proc or whatever method you find in the documentation of the database).

The real trick lies in retrieving the data SORTED on the user and then on the key you need for the sub-tables. That way, you can travel over the user table, create the user and then for each Picture / Product you find for that user, create the sub objects and add them. This only requires one forward pass over the data. This can effectively reduce the loading time under one second.

I hope this helps you.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question