I have just read about N+1 queries problem.
This basically boils down to this paradigm:
To do a Thing takes a finite amount of time.
To do the same Thing a thousand times takes at least a thousand times as long.
Remember what's happening each and every time you run a query:
- Your client [application] sends some SQL text and arguments to the database server,
- The database works out how best to "get at" the data you want.
This might involve parsing the query to work out how to do that or, more likely, re-using a cached execution plan,
- The database goes and gets the data.
Any indexes that it needs will probably be in memory anyway, but it may have to go and pull some data pages from disk.
- The database assembles the data, parcelling it up, ready to go back across the network,
- The database sends the data back to the client,
- The client does what it does with that data.
It will take far, far longer to do all that a thousand times over, one row at a time, than it will to do the job just once, with a single query that pulls back a thousand rows of data, all in one go.
The other, big problem here is that there is absolutely nothing that the database (or, more importantly, the poor DBA on the receiving end of the complaints) can do to tune these queries or otherwise improve things.
In such cases, the database is often as happy as a lark, being asked to find a record based on a single value in a uniquely-indexed field, over and over again, in rapid succession. In fact, Life doesn't get much better for a relational database, but the Users will be complaining like mad that the Application is "running" like a slug.
Applications operating in this [appalling] fashion simply has to be recoded.