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 =…

Cybersecurity

I went to a Cybersecurity conference last week hosted by Data Connectors and I want to give kudos for the job well done in running the conference. 

First of all they held the conference in a very nice Westin downtown, so the facility was great!  The Vendors were very informative and the presentations were very good as well!  There was a good mix of breaks so we could talk with the Vendors and learn more about their products.  The lunch and snacks were good too!  Always a plus!

But back to the subject of Cybersecurity.  It's truly amazing all the cyber attacks made against companies, websites, home owners, smart phones, etc.  I don't remember the exact numbers, but at the time they presented them, I was amazed how high it is, and how high it's going to be.  Everything on the web is exposed and most people don't realize the bad exposure that's out there.  Yes, bigger companies are spending lots of money to protect their data but the regular "Joe" with a sma…