What is the fastest way to avoid n + 1 questions and why?

Question

I want to add some useful methods to avoid a lot of n + 1 issues in an outdated application.

The overall picture is as follows:

select a.* /* over 10 columns */
from [table-A] a
where /* something */

Received in the ClassArecord instance collection

Then lazy retrieved helper instances:

select b.* /* over 10 columns */
from [sub-table-B] b
where b.ParentId = @ClassA_ID

As a result, there is a problem with choosing n + 1. Basically, this is not a serious problem, since only a few copies ClassAare extracted on the page with a rare hit, but in more and more places this n + 1 problem causes the pages to become too slow as the application scales,

, ClassA ClassB .

, :

1) ClassA, , ClassB :

select b.*
from [sub-table-B] b
where b.ParentId in ( /* list of parent IDs */ )

, SQL (- ).

2) ClassB :

select b.*
from [sub-table-B] b
    inner join [table-A] a
        on b.ParentId = a.[ID]
where /* something */

, [table-A] .

3) ClassA:

select a.*, b.*
from [table-A] a
    left outer join [sub-table-B] b 
        on a.[ID] = b.ParentId
where /* something */

, [table-A] - , .

, 3 :

• 2 ,
• 2 ,
• 1 ,

, . , ? , ? ?

, Linq, EF NHibernate?

4- , , 3?

Answer 1

, EF L2S - db.

db roundtrips , db roundtrips .

, , A, clien.

db .

, . . 1- , . , .

Answer 2

-, " " , , .

, (ADSL-?), , . , - , [table-A] , .

Answer 3

(Oracle , ) , .

Some ORMs, such as Django , will allow you to create a custom query and return only partial results that you need to display on the page. This is a good approach - if you see that the access point to the database is optimized, but otherwise leave the ORM to fulfill your bets.

Remember that the equipment is cheap (two days of consultants work the same as updating the server), regardless of what your financial manager says.