dcsimg
Results 1 to 15 of 15

Thread: [RESOLVED] SQL - Need help with an inefficient query

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    428

    Resolved [RESOLVED] SQL - Need help with an inefficient query

    Hi,
    I have the following query that takes 4 seconds to run. I am not sure if that is what it supposed to do but it seems a bit long.
    Is the a way to get the result in a more efficient way? And how?

    Code:
    Select Distinct INITS from PhoneBookDMS 
    Where INITS not in (Select Distinct Emp_id from CM_DMS)
    Order By INITS ASC
    CM_DMS: 50k rows, 12 columns
    PhoneBookDMS: 600 rows, 6 column

    Thanks.

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,502

    Re: SQL - Need help with an inefficient query

    Probably the most efficient way of doing this is by using Except:-

    Code:
    (Select Distinct INITS
    From PhoneBookDMS 
    Except
    Select Emp_ID From CM_DMS)
    Order By INITS
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    428

    Re: SQL - Need help with an inefficient query

    Thanks. Still 4 seconds

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,502

    Re: SQL - Need help with an inefficient query

    OK, at that point it really needs the execution plan examined.

    What is the data type on the INITS column in each table? In particular, are they the same? If not you could have some implicit casting going on and that can be resource hungry.
    Is there an index on INITS in both PhoneBookDMS and CM_DMS? If not you could consider adding them (although indexes aren't "free" so maybe check with a DBA or similar first)
    If there are indexes, are they being used. Have a look at the execution plan and see if there index seeks in there - or are you seeing index and/or table scans.

    Other than that, it'd really need someone sat in front of it going through the plan in detail to take it further.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,124

    Re: SQL - Need help with an inefficient query

    Didn't we have something like this a few days ago?
    I think i recommended a LEFT JOIN with ISNULL on the righthand-side

    EDIT: Found it
    http://www.vbforums.com/showthread.p...aster-solution
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    428

    Re: SQL - Need help with an inefficient query

    Quote Originally Posted by FunkyDexter View Post
    OK, at that point it really needs the execution plan examined.

    What is the data type on the INITS column in each table? In particular, are they the same? If not you could have some implicit casting going on and that can be resource hungry.
    Is there an index on INITS in both PhoneBookDMS and CM_DMS? If not you could consider adding them (although indexes aren't "free" so maybe check with a DBA or similar first)
    If there are indexes, are they being used. Have a look at the execution plan and see if there index seeks in there - or are you seeing index and/or table scans.

    Other than that, it'd really need someone sat in front of it going through the plan in detail to take it further.
    They are both varchar(5) and there is no indices One of the tables has ID as primary key (if you meant that) but not the other one. I am not sure where I can see the execution plan, I don't admin access to the database.

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    428

    Re: SQL - Need help with an inefficient query

    Quote Originally Posted by Zvoni View Post
    Didn't we have something like this a few days ago?
    I think i recommended a LEFT JOIN with ISNULL on the righthand-side

    EDIT: Found it
    http://www.vbforums.com/showthread.p...aster-solution
    Could you give me some more detail? So yo mean a join where the result is the difference of the two tables?
    Thanks.

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,502

    Re: SQL - Need help with an inefficient query

    Could you give me some more detail?
    He's suggesting this approach:-
    Code:
    Select distinct PB.INITS
    From PhoneBookDMS PB
    Left Join CM_DMS C
    	on PB.INITS = C.Emp_ID
    Where C.Emp_ID is null
    You left join to the secondary table and then check for nulls on the right hand side. Because a left join returns nulls on the right where a match is not found, this gives you all the records on left where there is no match on the right. It's a good approach and probably more widely used than mine. I prefer mine because it's intent is more explicit but tastes may vary. I think an except will perform better but I wouldn't take it for granted and you should do a real world comparison if performance is crucial here.


    there is no indices One of the tables has ID as primary key
    Ok, well the primary key will have an index backing it up so that's good. Which is the table that has that? Find that table in the object explorer and click the + to expand it's tree. Then expand the indexes node. You'll see the index there - take a note of it's name. You should also do the same for the other table to double check whether there's an index or not.

    To see the execution plan, in management studio, drop down the Query menu. One of the options is "Include Actual Execution Plan". Select that and re-run your query. The execution plan will be displayed on a tab in the results pane (at the bottom of the screen by default). You're hoping to see at least an index seek on the primary key in there. The other table will either seek on an index if there is one (possible sorting first) or it will scan if there is no index or if engine determines that the sort would take longer than using an unsorted joining algorithm.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    428

    Re: SQL - Need help with an inefficient query

    Hi FunkyDexter,
    Thanks for the explanation.

    For CM_DMS the index is called "PK__CM_DMS_A__3214EC277015AD25" but unfortunately there is no index for the Phonebook. I might be able to add that, just need to ask the guy making that table for me via Alteryx to add the index to the table.
    I could not find "Query" menu on my SSMS. Maybe because I am using the free version?

    Name:  2019-06-19_09-19-00.jpg
Views: 53
Size:  13.2 KB

    I just checked your translation of Zvoni's suggestion and it runs in a few milliseconds and gives me the same result that my query needed 4 seconds to produce. I think I take that Thanks again for providing the information and help to solve this issue for me

    Edit: my bad, I can see the Query menu now Just needed to click on the query space and the menu items showed up. I ran that execution plan and it tells me that it is missing index:

    Name:  2019-06-19_09-31-55.jpg
Views: 59
Size:  31.9 KB

    Name:  2019-06-19_09-30-25.jpg
Views: 53
Size:  9.7 KB
    Last edited by Grand; Jun 19th, 2019 at 02:32 AM.

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,502

    Re: [RESOLVED] SQL - Need help with an inefficient query

    That execution plan is very odd. It's table scanning CM_DMS 3 times despite it only being accessed once in your query (that was from the query from post 1 right?). That looks like there was some kind of self join going on. Something was going very wrong there and I'm not surprised you were getting long query times.

    Just to be clear, what column(s) made up the primary key in CM_DMS? Was it Emp_ID? Looking at that plan I'm guessing it wasn't.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    428

    Re: [RESOLVED] SQL - Need help with an inefficient query

    Let me start with this: I am really sorry

    The info I gave you about the index was from another table; really sorry. After reading your last comment, I went back and actually this time looked that the right table which is "CM_DMS" and can see that there is no index at all. So, none of the tables have indices. I am not sure if that explains the strange behaviour of the query?
    I have to stop getting my medicine form street doctors.

  12. #12
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,502

    Re: [RESOLVED] SQL - Need help with an inefficient query

    I am really sorry
    We've all done it.

    Given what you're saying I'm not surprised you were getting slow performance. What's more confusing to me at this point is why the Left Join approach dropped the query time so dramatically. with no indexes I would have expected that perform poorly as well. If you're just happy that that's fixed it then Pat yourself on the back and quietly take the credit but if you're interested in going further I'd suggest the following:-

    1. At the very least get primary keys set up on both tables. This will automatically add underlying indexes and will allow the engine to do look ups efficiently.
    2. Other than that you should consider indexes on the two columns (EMP_ID and INITS used in the join). Indexes aren't "free" so you should run this past your DBA/Database designer first, but they're at least likely to be good candidates for indexing.
    3. Out of curiosity, could you post the execution plan you get from the Left Join approach?
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    428

    Re: [RESOLVED] SQL - Need help with an inefficient query

    Thanks.
    Here is the EP for left join:

    Name:  2019-06-19_13-15-39.jpg
Views: 79
Size:  38.7 KB

    I will consider indexes for those tables. This is due to that fact that I have some other queries that run even slower but those are not noticeable to general users; but why waste time if it can be saved.

  14. #14
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,502

    Re: [RESOLVED] SQL - Need help with an inefficient query

    Yeah, that's what I would have expected to see and the bulk of the cost is still in a table scan across CM_DMS. I'm curious as to why it's able to resolve that scan so much quicker in this approach. Oh well, without sitting in front of it it'll be a needle in a haystack to find so don't worry too much about it if you're getting a decent response from the left join.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    428

    Re: [RESOLVED] SQL - Need help with an inefficient query

    Thanks for looking int to it. I am happy so far with it and am getting the result I needed much faster.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width