Skip to main content

Nonclustered Index in SQL Server

So, I've run into an issue with one of my queries because it takes about 40 seconds to run and according to the Recent Expensive Queries section of the Activity Monitor in SQL Server, there is a section of code taking up about 51% of the query. 

First, let me say that I've been writing Transact SQL for quite awhile but have never ventured into the Clustered and NonClustered areas of forming Indexes.  I do use indexes on all my tables but that's just the regular Primary Unique key. 

We've been using this query for awhile and it does work but when the result set is about 5K records it takes a little while to run.  So here's the actual query itself

SELECT 
    PartNo, 
    SerialNo, 
    HeciCode, 
    ScanDate, 
    OrderType, 
    PoNo,  
    po.IssueDate, 
    SoNo 
FROM ScanHistory sh WITH (NOLOCK) 
LEFT OUTER JOIN trkRWPOHeader po WITH (NOLOCK) 
    ON sh.PoNo = po.PONum 
WHERE len(PoNo) > 2 
    AND ScanDate > '1/1/2009' 
    AND PartNo = 'RDH102472' 
    AND sh.SerialNo NOT IN (SELECT SerialNo 
                            FROM ScanHistory WITH (NOLOCK) 
                            WHERE PONo = '' 
                                OR (OrderType = 'SO' AND SoNo = '000001') 
                            ) 
    AND sh.SerialNo NOT IN (SELECT i.SerialNo       
                            FROM skp_SkidItem i with (nolock) 
                            INNER JOIN skp_Skid sk with (nolock) 
                                ON i.SkidNo = sk.SkidNo 
                            WHERE sk.SkidStatus = 'Shipped' 
                                or i.ItemStatus = 'Shipped'
                            ) 
ORDER BY PoNo
When you look at the recommended Execution Plan it says:
Missing Index (Impact 51.4221): CREATE NONCLUSTERED INDEX [<name of missing index, sysname,>] ON dbo.ScanHistory (PartNo, ScanDate)

I have posted this issue on Stack Flow and I have had some responses but nothing really makes sense at this point.  I've tried the above suggestion using a dummy missing index but that didn't seem to work.  Anyhow, if any of you are expert Transact SQL programmers I would love to hear from you on any suggestions or just general explanations on Nonclustered Indexes.

Thanks!



Comments

Popular posts from this blog

My Twitter Followers Have Gone Down!

If you're a Twitter user you might have heard or seen on your account the number of followers go down.  For some of us, those numbers might be minimal.  I have a Twitter account @NKYSports which I use in connection with one of my High School sports website NKYSports.com.  Last week I had about 6,070 followers.  That number today is down about 100.  But I have heard users who have followers over 50K, 100K, etc. losing thousands and thousands of "so-called" followers.

So what happened?  Twitter has started cleaning accounts due to fake news scandals and data privacy.  That equates to about 1 million accounts the past quarter (2nd Q of 2018).  Twitter CEO Jack Dorsey says improving the health of the platform is critical for future growth.

Twitter has been a great tool for my website, but I also like getting updates sent directly to my phone to get the latest news, sports, weather, etc.  I know what to believe and not to believe as well!  Fake news is every where, always wil…

Love the XMLHttpRequest Object

I was reading up on some XML and AJAX stuff the other day and came across the XMLHttpRequest Object - when I come across snippets of code like the example in the link, I always try to figure a way to incorporate it into my work.  I always ask myself is this something I can use and make my software more efficient?

This was something I most definitely could use as we have a system of part numbers in our database.  We have users that select part numbers to enter Selling Opportunities so I was able to use the code to look up the part numbers more quickly, showing the part numbers, qty on hand, and some other pertinent info.
Below is an example of some code:
Page1.asp
<html> <head> <script> function showHint(str) {     if (str.length == 0) {         document.getElementById("txtHint").innerHTML = "";         return;     } else {         var xmlhttp = new XMLHttpRequest();         xmlhttp.onreadystatechange = function() {             if (this.readyState =…

2nd Quarter Review

So I must admit I've been slacking on my blog posts lately.  When the weather starts getting warmer and you start vacationing, it's a little more difficult to stay in a good routine.  These aren't necessarily bad distractions but I'm at least I'm aware of it and one of my goals for the 3rd Quarter (and 2nd Quarter next year) is to fight through those distractions and stay more focused.

Before I move on with my goal updates I wanted to share something sad that happened right before we left for vacation.  The night before we left my daughter's guinea pig died, must have died in her sleep.  We didn't realize this until about 20 minutes before having to leave for the airport.  I also didn't want to break the news to my daughter before the trip b/c I wasn't sure how she would react.  So we decided not to tell her until we returned and had someone dispose of the guinea pig later that day.

When we returned from vacation we told her the bad news, but to our…