Solved

More than Two Relationships between Custom Entity Classes  Caused poor Performance

Posted on 2009-04-03
4
447 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
  • 2
4 Comments
 
LVL 11

Expert Comment

by:anuragal
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 500 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Bubble user-defined Sql RAISERROR(...) to c# exception 14 118
c# code 19 61
Performance  Html.BeginForm vs jQuery 3 15
C# TextBox 11 15
Bit flags and bit flag manipulation is perhaps one of the most underrated strategies in programming, likely because most programmers developing in high-level languages rely too much on the high-level features, and forget about the low-level ones. Th…
Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now