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
Solved

SQL Index for table and views question

Posted on 2014-03-29
8
539 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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…

809 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