0

Looking at the example given on the PetaPoco help section - section "One-To-Many Relationships"

It describes how to use InnerJoin and a mapper to handle data mapping of a one-to-many relationship...

var authors = db.Fetch<author, post, author>(
new AuthorPostRelator().MapIt,
"SELECT * FROM authors LEFT JOIN posts ON posts.author = authors.id ORDER BY posts.id"
);

This is fine for small tables, but for large quantities of data, the loading of this data is very slow.

It's not an 'author', but to keep the language the same...

My author has 30 columns. I have 90,000 authors and each author has 50 posts.

That query above will bring down 4,500,000 rows of data...in that data the author rows are duplicated 50 times...does this matter?

Is there a way to load in this data any quicker? Have I missed a trick anywhere along the line?

Paul
  • 9,409
  • 13
  • 64
  • 113
  • How slow? Just speculating but it sounds like you're pulling an awful lot of data into memory at once, and that it would likely be sluggish no matter how you do it. Are you sure PetaPoco is your bottleneck? – Todd Menier Jun 29 '14 at 16:44
  • Why do you need to load all the data at once? – Eduardo Molteni Jun 30 '14 at 03:12

1 Answers1

0

Bearing in mind that it is rare to work with more than one author at a time, I would fetch only posts for a required author using the following format:

 var author = db.SingleOrDefault<Author>("WHERE id= @0", authorId);
 if (author != null)
 {
     var posts= _db.Query<Post>("WHERE Id = @0 ", author.Id).ToList();
     author.Posts.AddRange(posts);
 }
 return author;

This way I avoid the left join and fetch required author posts without any performance penalties.

In this way you can even restrict the posts returned by appending more conditions in the posts where clause in the following manner:

 var posts= _db.Query<Post>("WHERE Id = @0 AND Cond1 = @1", author.Id, cond1).ToList();

You can even limit the number of columns returned from the author by defining a model of the required columns and replacing 

var author = db.SingleOrDefault<Author>("WHERE id= @0", authorId);

with 

var author = db.SingleOrDefault<AuthorModel>("WHERE id= @0", authorId);
kagundajm
  • 1,152
  • 1
  • 15
  • 26