1

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] > 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?
  • 1
    Why did you not add the index, and found out yourself that this would make the query faster? – Luuk Nov 17 '19 at 10:23
  • 1
    You also use action_id and id in the WHERE clause... Are the indexed? Can you show use the DDL of this table? – Luuk Nov 17 '19 at 10:25
  • @Luuk id is identity and pk, so it also indexed. the rest of the columns are used only for this query. – SHR Nov 17 '19 at 11:03
  • How many records are there in your table? 100'000 or 1'000'000 or only a couple of thousand records? Seeing as the column id has been defined as identity are you sure it is always 11, because an IDENTITYwould be unique. See IDENTITY property (Microsoft | SQL Server Docs). – John aka hot2use Nov 18 '19 at 10:56
  • 1
    Can u please include details asked by hot2use as well as provide the query plan which is having performance issue. – Learning_DBAdmin Nov 18 '19 at 12:10
0

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] 

   )
  • Whether the predicate is in the JOIN or the WHERE changes the meaning so can affect correctness. See here. – Michael Green Nov 19 '19 at 2:09

Not the answer you're looking for? Browse other questions tagged or ask your own question.