Solved

More than Two Relationships between Custom Entity Classes  Caused poor Performance

Posted on 2009-04-03
4
450 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
creating a flowchart from an algorithm 5 30
Office.Interop.Word Document - Detect Macros not working 16 36
Memory Usage 2 49
how to read json value 2 27
Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

948 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

19 Experts available now in Live!

Get 1:1 Help Now