CODEWITHSUNDEEP
sqloracle-databaseperformancequery-performance
Upendhar Singirikonda
Upendhar Singirikonda

Reputation: 179

Which Oracle query is faster

I am trying to display employee properties using C# WPF view.

I have data in '2' different oracle tables in my database:

Those tables structure at high-level is...

The user will input 'List of Employee Name' and I need to display Employee properties using data in those '2' tables.

Each employee has properties from 40-80 i.e. 40-80 rows per employee in EMPPR table. In this case, which approach is more efficient?

Approach #1 - single query data retrieval:

SELECT Pr.PropertyName, Pr.PropertyValue
FROM EMP Emp, EMPPR Pr
WHERE Emp.ID = Pr.ID
  AND Emp.Name IN (<List of Names entered>)

Approach #2 - get IDs list using one query and Get properties using that ID in the second query

Query #1:

SELECT ID
FROM EMP
WHERE Name IN (<List of Names entered>)

Query #2:

SELECT PropertyName, PropertyValue
FROM EMPPR
WHERE ID IN (<List of IDs got from Query#1>)

I need to retrieve ~10K employee details at once where each employee has 40-80 properties.

Which approach is good?

Upvotes: 0

Views: 107

Answers (2)

Deepak Kuletha
Deepak Kuletha

Reputation: 1

Use first approach of join between two tables which is far better than using where clause two times.

Upvotes: 0

O. Jones
O. Jones

Reputation: 108841

Which query is faster?

The first one, which uses a single query to fetch your results. Why? much of the elapsed time handling queries, especially ones with modestly sized rows like yours, is consumed going back and forth from the client to the database server.

Plus, the construct WHERE something IN (val, val, val, val ... ... val) can throw an error when you have too many values. So the first query is more robust.

Pro tip: Come on into the 21st century and use the new JOIN syntax.

 SELECT Pr.PropertyName, Pr.PropertyValue
   FROM EMP Emp
   JOIN EMPPR Pr ON Emp.ID = Pr.ID
  WHERE Emp.Name IN (<List of Names Inputted>)

Upvotes: 2

Related Questions