How can Inner join be optimize? I have a table where there are in some cases twin records.

In the table, I have the following columns:


id (identity column) 
action_id 
session_id
row_id
timestamp
...and more records.


I need to find a twin record for a specific id.

A twin record for action_id=11 is when action_id=12 and session_id, row_id and timestamp are the same.

Table Defintion

Assuming my table created as follows. (no additional index)

CREATE TABLE table_name (
      [id]         BIGINT IDENTITY(1,1) PRIMARY KEY,
      [session_id] BIGINT   default @@SPID,
      [row_id]     BIGINT   default 0,
      [timestamp]  DATETIME default CURRENT_TIMESTAMP,
      [action_id] SMALLINT NOT NULL,
      [value1]     NVARCHAR(max) NULL, 
      [value2]     NVARCHAR(max) NULL,
      [value3]     NVARCHAR(max) NULL) 


Select Statement

My current query is:

SELECT d.* 
FROM [table_name] d INNER JOIN [table_name] i 
   ON d.[session_id] = i.[session_id] 
   AND d.[row_id] = i.[row_id] 
   AND d.[timestamp] = i.[timestamp] 
WHERE  d.[action_id] = 12 
   AND i.[action_id] = 11 
   AND i.[id] = X -- where X is the input


Questions

I want the query to be as fast as it can, so my questions are:


Is there a difference if I move the where condition in the joins? 

SELECT d.* 
    FROM [table_name] d INNER JOIN [table_name] i 
       ON d.[session_id] = i.[session_id] 
       AND d.[row_id] = i.[row_id] 
       AND d.[timestamp] = i.[timestamp] 
       AND d.[action_id] = 12 
       AND i.[action_id] = 11 
    WHERE  i.[id] = X -- where X is the input

Should I add an index for the timestamp, row_id and session_id columns?
What will happen first, JOIN the tables, or filter results by WHERE?   
is the following query is better?

SELECT d.* FROM (
   (SELECT * FROM [table_name] WHERE [id] = X AND action_id = 11) i 
   INNER JOIN
   (SELECT * FROM [table_name] WHERE [id] &gt; X AND action_id = 12) d
       ON  d.[session_id] = i.[session_id] 
       AND d.[row_id] = i.[row_id] 
       AND d.[timestamp] = i.[timestamp]
 ) 

What else can I do to make it run faster?
Is there a difference if I put the where condition at the join
  section?


In your case, NO. Optimizer is smart enough to understand.

Where Clause further put filter on what is produce by JOIN.

INNER JOIN :Here WHERE clause do not have much to to put filter.It has smaller resultset to apply filter.  In fact, in case of, INNER JOIN, it do not matter where you put condition in the JOIN or WHERE. Optimizer is smart enough to understand.There is no performance difference .

OUTER JOIN: Here WHERE clause  have much larger resultset to apply filter.
Since Where clause have much larger resultset to filter out, it make sense to Filter as much as possible in JOIN ON condition,so that smaller resultset is produce.

If where condition is applied in Right table ,then such LEFT JOIN is equivalent to INNER JOIN

Though in most cases of OUTER JOIN, optimizer is smart enough to understand and convert the WHERE to ON internally.

It depend upon example to example, specially in complex query involving large volume of data.It is always wise to check the query plan and decide WHERE condition accordingly.

You should use predicate column in ON ,which logically relate the joining table.

If it is filter condition then use it in Where clause.


  Should I add an index for the timestamp, row_id, session_id?


Yes. Create composite Non clustered index.Choice of Clustered index([id]) is perfect.

Create NonClustered Index NCI_Table_Action_Row_Session_Timestamp 
on table_name(action_id,row_id,session_id,Timestamp)
GO


Notice the order of columns. The narrow column should be in LEFT most.
There is no point in Covering index as nvarchar(max) are not use in covering index.


  What else can I do to make it run faster?


I think instead of INNER use EXISTS.Don't use *

SELECT d.[id]     ,
      [session_id] ,
      [row_id]     ,
      [timestamp]  ,
      [action_id] ,
      [value1]     , 
      [value2]     ,
      [value3]       
FROM [table_name] d 
where(select 1 from [table_name] i 
   where  i.[id] = X -- where X is the input
  AND i.[action_id] = 11 ---- where 11 is the input
  AND  d.[action_id] = i.[action_id]+1 --12 ---- if 12 is  input then it is more good
  AND d.[session_id] = i.[session_id] 
   AND d.[row_id] = i.[row_id] 
   AND d.[timestamp] = i.[timestamp] 

   )