dimanche 3 avril 2016

What is the performance impact of separate SQL statements instead of a JOIN?

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