[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

More than Two Relationships between Custom Entity Classes  Caused poor Performance

Posted on 2009-04-03
4
Medium Priority
?
472 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

650 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