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

Answers


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.


Need Your Help

ADB doesn't see my USB-connected device anymore

android eclipse adb

I recently upgraded my (Windows 7) development environment to Eclipse 3.7.1 (Indigo). I have an HTC Magic (Rogers) running Android 2.1upd8.1, a Kobo Vox running 2.3.3 and a Samsung Galaxy Tab 10.1

Objective-c, AVPlayer stops/pauses playing automatically sometimes and don't resume itself

ios objective-c iphone audio avplayer

I am trying to implement AVPlayer for play audio file from URL. It is working fine. But sometimes AVPlayer stops/pauses playing and do not resume again. If I click on play button then it works fine...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.