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…

File Size Upload Limits using Godaddy Hosting

I've been working on a new project for a friend of mine and came across an issue with trying to upload photos using the infamous freeASPUpload code.  I've used the code in the past for some past projects but don't recall having issues with limits on uploading files.

I'm posting this on my blog b/c there's nothing on the internet that discusses this (or at least I wasn't able to find any explanations), and hopefully this can be a resource to someone else that comes across the same issue.

Now I know there are other options, utilities, etc. for uploading pics/files/etc. but freeASPUpload is simple, I've used it in the past, and this project is pretty basic. 

So, if you want to change the upload file size limit you will first want to go into your Godaddy's hosting account.  Next go into the Virtual Directories option, then click on Directory Properties.  Towards the bottom there is a section called "Maximum size of entity body of an ASP request (KB)&qu…

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…