We help IT Professionals succeed at work.

More than Two Relationships between Custom Entity Classes  Caused poor Performance

YKMM
YKMM asked
on
484 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.
Comment
Watch Question

Anurag AgarwalPython Developer

Commented:
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

Author

Commented:
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.

Author

Commented:
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,
Software Architect / Team Leader
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.