Rewrite SQL code SELECT block to simplify logic
I am trying to rewrite this block with simpler logic if this can be done. I am using it within a larger SELECT statement and I think IF I can simplify this block, I might be able to improve performance of my query.
proj_catg_type_id, proj_catg_id and proj_id are all PKs in their tables.
select t1.proj_catg_name from table1 t1, table2 t2, table3 t3 where t2.proj_catg_type_id = t1.proj_catg_type_id and t2.proj_catg_type_id = 213 and t3.proj_id = t2.proj_id
Without knowing the referential integrety rules and the logic behind the tables it is difficult to give a 100% correct answer. But just by looking to this statement the most simplified logic would be
select t1.proj_catg_name from table1 t1 where t1.proj_catg_type_id = 213;
select t1.proj_catg_name from table1 t1 inner join table2 t2 on t2.proj_catg_type_id=t1.proj_catg_type_id where t2.proj_catg_type_id=213 and t3.proj_id=t2.proj_i
maybe? is t3 used outside this subselect?
If t3 is a table outside the selct you showed, then this is a correlated subquery which you should not be using at all, ever! That turns your query into a row-by agonizing row cursor.
Use derived tables or joins to get the results.
You don't give me enough code to write a specific solution for your problem, but let me give you an example:
SELECT field1 , field2 , (SELECT t3.field3 FROM table2 t2 JOIN table3 t3 ON t2.id = t3.id WHERE t4.somefield = t2.somefield) FROM table1 t1 JOIn table4 t4 ON t1.id = t4.id SELECT field1 , field2 , t3.field3 FROM table1 t1 JOIn table4 t4 ON t1.id = t4.id join (SELECT field3 FROM table2 t2 JOIN table3 t3 ON t2.id = t3.id) a ON t4.somefield = t2.somefield
The first query runs one row at a time which is extremely slow. The second should give the same results but runs in a set-based fashion which is much faster. It is important to make sure the derived table has an a alias. You could also use a CTE.