Solved

SQL Index for table and views question

Posted on 2014-03-29
8
547 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

691 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