SQL select Query performance is very slow with joins-CodePudding

Please correct the below query to increase performance back-end. I am using Oracle database

Query execution very slow:

SELECT A.USER_PROFILE_ID,
 B.LAST_NAME||','||B.FIRST_NAME||' - '||B.USER_PROFILE_ID AS EXPR1, 
 A.DEPARTMENT_CODE_ID, 
 C.NAME AS EXPR2,  
 A.EFFECTIVE_DATE,
 A.EFFECTIVE_STATUS, 
 A.INSRT_USER, 
 A.INSRT_TS, 
 A.MOD_USER, 
 A.MOD_TS 
FROM 'USER_PROFILE_DEPARTMENT' A,
 'USER_PROFILE' B, 'DEPARTMENT_CODE' C 
WHERE 
 A.USER_PROFILE_ID = B.USER_PROFILE_ID
AND A.DEPARTMENT_CODE_ID = C.DEPARTMENT_CODE_ID  
ORDER BY EXPR1

I couldn't find any please help

CodePudding user response：

Avoid using the WHERE clause to join tables. Instead, use the JOIN keyword to explicitly specify the relationship between the tables being joined.

For example, instead of writing:

SELECT *
FROM "USER_PROFILE_DEPARTMENT" A, "USER_PROFILE" B, "DEPARTMENT_CODE" C
WHERE A.USER_PROFILE_ID = B.USER_PROFILE_ID AND A.DEPARTMENT_CODE_ID = C.DEPARTMENT_CODE_ID

You can use:

SELECT *
FROM "USER_PROFILE_DEPARTMENT" A
INNER JOIN "USER_PROFILE" B ON A.USER_PROFILE_ID = B.USER_PROFILE_ID
INNER JOIN "DEPARTMENT_CODE" C ON A.DEPARTMENT_CODE_ID = C.DEPARTMENT_CODE_ID

Some tips when it comes to performance in SQL:

Use appropriate indexes on the columns used in the JOIN and WHERE clauses to improve the performance of the query. This can speed up the process of finding matching rows in the tables being joined.
Consider using sub-queries or derived tables to retrieve the data you need, rather than joining multiple large tables. This can improve performance by reducing the amount of data that needs to be scanned and processed.
Use the LIMIT keyword to limit the number of rows returned by the query, if you only need a small subset of the data. This can reduce the amount of data that needs to be processed and returned, which can improve performance.

If the query is running slowly despite these changes, consider using EXPLAIN or EXPLAIN ANALYZE to understand where the bottlenecks are and how to optimize the query further.

CodePudding user response：

FROM "USER_PROFILE_DEPARTMENT" A, "USER_PROFILE" B, "DEPARTMENT_CODE" C WHERE A.USER_PROFILE_ID = B.USER_PROFILE_ID AND A.DEPARTMENT_CODE_ID = C.DEPARTMENT_CODE_ID