Given the following schema:
CREATE TABLE IF NOT EXISTS [Person]
(
[Id] INTEGER PRIMARY KEY,
[Name] TEXT NOT NULL,
[Age] INTEGER NULL,
[JobId] INTEGER NULL
);
CREATE TABLE IF NOT EXISTS [Job]
(
[Id] INTEGER PRIMARY KEY,
[Name] TEXT NOT NULL,
[Salary] DECIMAL NULL,
[IndustryId] INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS [Industry]
(
[Id] INTEGER PRIMARY KEY,
[Name] TEXT NOT NULL
);
I need to build an object graph and map it to an object (this question is not about ORMs so let's focus on the SQL only).
I can see two options, one is to identify the relationship between the objects and generate the JOIN statement:
SELECT
Person.Id, Person.Name, Person.Age, Person.JobId,
Job.Id, Job.Name, Job.Salary, Job.IndustryId,
Industry.Id, Industry.Name
FROM Person
LEFT JOIN Job ON Person.Id = Job.Id
LEFT JOIN Industry ON Job.IndustryId = Industry.Id
WHERE Person.Id IN (1, 2);
And the other option is to generate separate SQL statements for each of the objects in my graph and run them as one multi-statement then map the final result into an object:
SELECT Id, Name, Age, JobId FROM Person
WHERE Id IN (1, 2);
SELECT Id, Name, Salary, IndustryId FROM Job
WHERE Id IN (SELECT JobId FROM Person WHERE Id IN (1, 2));
SELECT Id, Name FROM Industry
WHERE Id IN (SELECT IndustryId FROM Job
WHERE Id IN (SELECT JobId FROM Person WHERE Id IN (1, 2)));
Focusing on the performance impact, I want to know if there is a difference between the two when it comes to the DBMS executing these queries.
Any help is much appreciated.
Aucun commentaire:
Enregistrer un commentaire