Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Rauken
Posting Yak Master
108 Posts |
 Posted - 2011-12-14 : 09:22:15
|
| Hi!I have three tables, Project, Milestone and Activity. One project can have several milestones and one milstone can have several activites. Very straightforward.I have a .NET WinForms application with a tree view showing all the projects. If I click on a projects it shows the milestone for that project and then the activities.Right now I fetch all the projects, I do a foreach loop populating my Project object and inside that loop I fetch all the Milestones poplating the list of milestones in the project object and so on.Let's say I have 1500 projects, each containing 5 milestones each milestone have 100 activities. It takes around 10 sec to populate my list of projects which actually holds everything. Is there a better way than this to populate my objects? I get a couple of thousand select against the db with my method./Mag |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-14 : 09:53:14
|
| SELECT 1 - get all the relevant Projects and store then in an arraySELECT 2 - get all the relevant Milestones, for only the Projects that were in SELECT 1, and store then in a second array. ORDER BY the same order that the Projects were retrieved in SELECT 1SELECT 3 - get all the relevant Activities ...Output by "walking" through all three arrays in order.Or you could use ROLLUP, but I've found the output from that quite difficult to actually build into an application |
 |
|
|
Rauken
Posting Yak Master
108 Posts |
Posted - 2011-12-18 : 06:27:10
|
| Hi,Here how I solved it. I begin to read all the content of each table into a list of that speficic type of object. Like:List<Project> projectList = new List<Project>();Then I populate projectList with projects from the database.After I received all three lists I join them using Linq. The operation is incredible fast because it's all done in memory.My list of projects then contains everything. |
|
|
|
|
|
|
Best practice to populate objects