I have inherited a SQL Database from a previous employee. I have a particular stored procedure that is taking several minutes to return a recordset on a table with 10 million records. The stored procedure the following structure;
@TableVar1 is a table variable with a set of IDs parsed from a string passed into the stored procedure
@TableVar2 is a table variable holding a set of processing types parsed from a string passed into the stored procedure
Table1 is the main table with 10 million records
Table2 is a table of users with 4K records
Table3 is a support table with only about 100 records
[List of 105 fields, mostly from table D]
INNER JOIN dbo.Table1 d WITH (NOLOCK) On t1.ID = d.ID
INNER JOIN dbo.Table2 u WITH (NOLOCK) ON u.ID = d.ID
LEFT JOIN dbo.Table3 s WITH (NOLOCK) ON d.KeyCode = s.ParentKeyCode AND
s.KType = 'substitution'
LEFT JOIN dbo.Table1 d2 WITH (NOLOCK) ON s.ChildKeyCode = d2.KeyCode
d.CreatedDate > '1/1/2014'
d.KeyType = 'master'
d.KeyStyle = 0
d.Activated = 1
EXISTS (SELECT NULL FROM @TableVar2 WHERE [Status] = d.Processed)
d.IsTest = 0
LEN(RTRIM(LTRIM(d.Title))) > 0
(d2.Type <> 'Retired_Key' OR d2.Type IS NULL)
I have these questions;
1. The primary table, Table1 aliased as "d", does not have a specific index for all of the fields listed in the where predicates. If I create a non-clustered index in Table1 that includes all of these fields, will performance improve?
2. Note that Table1 is inner joined as alias "d" and then left joined under a different alias "d2". Is this considered good practice?
3. Should I make sure that I have indexes for all joined table fields?
Thanks in advance,