Solved

SQL Index for table and views question

Posted on 2014-03-29
8
543 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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
 

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

749 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