Solved

SQL Index for table and views question

Posted on 2014-03-29
8
521 Views
Last Modified: 2014-03-30
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
0
Comment
Question by:dcadler
8 Comments
 
LVL 9

Expert Comment

by:edtechdba
ID: 39964266
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
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 39964267
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39964268
PS Initially, loose the nolock hints for testing.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39964280
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:dcadler
ID: 39964551
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39964559
Hi

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

Regards
  David

PS Please post a pic of the execution plan!
0
 

Author Closing Comment

by:dcadler
ID: 39964566
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39965321
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now