SQL Index for table and views question

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



       SELECT

             [List of 105 fields, mostly from table D]

      FROM        
            @TableVar1 t1
            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
      WHERE
                  d.CreatedDate > '1/1/2014'
            AND
                  d.KeyType = 'master'
            AND
                  d.KeyStyle = 0
            AND
                  d.Activated = 1
            AND
                  EXISTS (SELECT NULL FROM @TableVar2 WHERE [Status] = d.Processed)
            AND
                  d.IsTest = 0
            AND
                  LEN(RTRIM(LTRIM(d.Title))) > 0
            AND
                  (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,

Dave
dcadlerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

The easiest way to attack this is to look at the query plan. The estimated plan will do for now.

It will show information on indexes that SQL tried to use and didn't exist.

In creating additional indexes, I'd go carefully. Yes, ID appears to need an index. Possibly even the clustered index. (What is the current clustered index?)

CreateDate may or may not be a good candidate for its own index.

KeyType, KeyStyle, Activated, Processed may be good for a composite index.

KeyCode  possibly should be indexed.

HTH
  David
0
 
edtechdbaCommented:
Have you tried changing the LEFT JOIN's over to INNER JOIN's and compare the record counts? Also, if d2 columns are listed within the WHERE clause, and d2 is a LEFT JOIN, wouldn't that revert the join back to an INNER JOIN anyways?

I know LEFT JOIN's can really hog up processing time, especially if there are millions of rows.

If I were to LEFT JOIN a table, I would put all predicate logic within the LEFT JOIN and not within the WHERE clause.

Just a thought, I hope that helps.
0
 
David ToddSenior DBACommented:
PS Initially, loose the nolock hints for testing.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Anthony PerkinsCommented:
I have a particular stored procedure that is taking several minutes to return a recordset on a table with 10 million records.
Moe relevant is how many rows is the result set returning?
0
 
dcadlerAuthor Commented:
To Anthony Perkins - The final record set returned may only include less than 200 rows, and possibly as little as 1 or 2 rows
0
 
David ToddSenior DBACommented:
Hi

>>Number of rows ...
... especially with the self join!

Regards
  David

PS Please post a pic of the execution plan!
0
 
dcadlerAuthor Commented:
Running the execution plan displayed a missing index. I created the missing index and cut the execution time in half. Thanks for the help.
0
 
David ToddSenior DBACommented:
Hi,

I'd still then take a look at the execution plan.

Is there yet another missing index? Is there a table scan on table1 that is a large percentage of the query? Are there one or two scans?

Bookmark lookups if expensive may be reduced/eliminated by covering indexes, or adding included columns into the index - at the cost of making the index wider and more expensive to maintain.

What I'm saying is that reducing the execution time in half is good, but there may be more that can be done here.

Regards
  David
0
All Courses

From novice to tech pro — start learning today.