SQL Query Problem

What is the N+1 selects problem in ORM (Object-Relational Mapping)?

Let's say you have a collection of Car objects (database rows), and each Car has a collection of Wheel objects (also rows). In other words, CarWheel is a 1-to-many relationship.

Now, let's say you need to iterate through all the cars, and for each one, print out a list of the wheels. The naive O/R implementation would do the following:

SELECT * FROM Cars;

And then for each Car:

SELECT * FROM Wheel WHERE CarId = ?

In other words, you have one select for the Cars, and then N additional selects, where N is the total number of cars.

Alternatively, one could get all wheels and perform the lookups in memory:

SELECT * FROM Wheel;

This reduces the number of round-trips to the database from N+1 to 2.
Most ORM tools give you several ways to prevent N+1 selects.

Reference: Java Persistence with Hibernate, chapter 13.

Query problem-getting wrong result when a condition or a set of data included

At the very least, you should avoid the way you update the table. You should be carefull with joins on update. If you happen to have more than one value for the same row, the result is not deterministic. Better use this form:

update table1
set table1.grade = (SELECT TOP 1 table3.grade FROM table3
WHERE table3.value < table1.max
ORDER BY table3.value DESC)

SQL query problem on restore from application

sorry guys i solved the problem i feel like a stupid person now,
anyway the problem was that i reversed 2 variable

String Filepath = DropDownList1.Text;
string Database = DropDownList3.Text;

now the solution work, sorry guys again.

Having problem with sql query in Opencart database

You should use JOIN.

UPDATE oc_order oc
INNER JOIN
(
SELECT date_added ,order_id
FROM oc_order_history
WHERE order_status_id = 5
) as oh on oc.order_id=oh.order_id
set oc.date_added=oh.date_added ;

I supposed that order_id is the key for JOIN condition.
Test it and let me know if it helps.
It would be better if you gave some examples data to get a right answer.



Related Topics



Leave a reply



Submit