Sql-server – Performance of inner join query for certain condition

sql-server-2008

I have one question about inner join query in SQL Server 2008. Is there any situation where NOT to use inner joins.
I have one scenario please tell me that here inner join is fruitful or not.

I have 2 tables

Table1 Columns: T1Id, Code and Name1, (Here Code is unique column)
Table2 Columns: T2Id, T1id(reference from Table 1), Name2, Type

Now there can be millions of records in both table. Now user pass the Code and I have to search the Name2 3 times in same procedure for diff condition.
I can do it with 2 scenario.

Scenario 1

I can store T1Id in temp variable

Declare @Id Int
Select @Id=Top 1 T1Id from Table1 Where Code='ABC'

Step 2: I will write all my queries

Select Name2 From Table2 Where T1id=@Id and Type='A'
Select Name2 From Table2 Where T1id=@Id and Type='B'
Select Name2 From Table2 Where T1id=@Id and Type='C'

Scenario 2:

Select Name2 
from Table2 Inner join Table1 on Table1.T1Id = Table2.T1Id
Where Table2.Type='A' and Table1.Code='ABC'

Select Name2 
From Table2 Inner join Table1 on Table1.T1Id = Table2.T1Id
Where Table2.Type='B' and Table1.Code='ABC'

Select Name2 
From Table2 Inner join Table1 on Table1.T1Id = Table2.T1Id
Where Table2.Type='C' and Table1.Code='ABC'

Please tell me which one is good to use. Both tables can have more than millions records in future.

Best Answer

Run one query, make sure you have good indexes

Select Name2 
From Table2 Inner join Table1 on Table1.T1ITable2.T1Id
Where Table2.Type IN ('A', 'B', 'C') and Table1.Code='ABC'

A graphical query plan will show where you need indexes

A million rows isn't much and well within capability of SQL Server

Related Solutions

Sql-server – How to query only for committed data while avoid a deadlock

Rather than theorising as to what could potentially be responsible, qualify exactly what is by capturing the SQL Server Profiler Deadlock Graph Event.

How To Track Down Deadlocks in SQL Server using Profiler

Once you know what the problem is that you are trying to solve, you can then proceed to actually solve it.

Sql-server – Query Optimizing Request

I am not sure your logic behind those joins but some part of the query can be optimised as; (for ex; WHEN t.total_ordered < t.total_received THEN 18 of your first CASE does not make sense at all as ELSE part is also equal to18`)

EDIT: Sub query brought back in with some optimisations. Syntax looks alright now.

UPDATE c2 SET stage_id = 
         CASE 
           WHEN t.required_quantity  <= t.total_received THEN 27
         --WHEN t.total_ordered < t.total_received THEN 18 (NOT NEED THIS BIT)
           ELSE 18
         END


FROM component c2 INNER JOIN 
     (SELECT c.component_id,
            CASE mp.component_name_id WHEN 2 THEN l.quantity*2
                 ELSE l.quantity
            END AS required_quantity,
            SUM(received) AS total_received,
                    --(NOT NEED THIS BIT AND OTHER LIST OF COLUMNS)
                    --SUM(ordered) AS total_ordered
     FROM line l 
        LEFT JOIN component c ON c.line_id = l.line_id
        LEFT JOIN materials_progress mp ON l.job_id = mp.job_id AND 
              l.line_no = mp.line_no AND c.component_name_id = 
                                                   mp.component_name_id 
     WHERE 
            mp.job_id IS NOT NULL  
            AND (ISNULL(mp.cancelled,0) = 0) --(OR CONDITION REMOVED)
            AND (ISNULL(mp.manual_override,0) = 0) --(OR CONDITION REMOVED)
            AND c.stage_id = 18
     GROUP BY  mp.job_id,  mp.line_no,  mp.component, 
                l.quantity, mp.component_name_id, component_id) AS t

ON c2.component_id = t.component_id --(INNER JOIN)

Related Question