Extremely slow ISEMPTY, even with a good index???

pdjpdj Member Posts: 643
edited 2009-09-01 in SQL Performance
SQL2008 (build 10.0.1600) with NAV5SP1-Upd1 (build 27191)
I have a lot of ISEMPTYs that shows up in the Profiler as very expensive which I find rather strange.

Example:
SELECT TOP 1 NULL FROM "MyDatabase"."dbo"."MyCompany$Item Ledger Entry" WITH (READUNCOMMITTED) WHERE (("Prod_ Order No_"=@P1)) AND (("Prod_ Order Line No_"=@P2)) AND (("Entry Type"=@P3))

The Item Ledger Entry has an SQL Index with "Prod_ Order No_" and "Prod_ Order Line No_" first. However; it takes more than 3 seconds and six million reads each time! (And our production orders are NOT that big...)

I tried getting the EP from the "simple" statement in the query window, which just shows a simple IndexSeek and IndexLookup – each estimated as 50%. NAV is using a Dynamic cursor for normal SELECT statements, as described in another tread. I don’t think it makes much sense to make any kind of cursor for an ISEMPTY but I tried making this statement after all:
declare @P1 varchar(20),@P2 int,@P3 int
declare C CURSOR DYNAMIC FOR
SELECT TOP 1 NULL FROM "MyDatabase"."dbo"."MyCompany$Item Ledger Entry" WITH (READUNCOMMITTED) WHERE (("Prod_ Order No_"=@P1)) AND (("Prod_ Order Line No_"=@P2)) AND (("Entry Type"=@P3))
Now the EP became very complex. In the upper right corner it is still an IndexSeek and KeyLookup with the proper index, but now only estimated as 9% and 18% of the statement. There are also a lot of steps with CWT_PrimaryKey and a lot of other “strange” boxes. I have no idea what they are doing, and basically don’t care. But why the h… does it take more than 3 seconds and six million reads to execute this simple query???
Regards
Peter

Comments

  • ara3nara3n Member Posts: 9,255
    I have seen this as well.
    It is called during adjust to check for cyclic application.
    The problem is that it is called for every Entry.

    I've added code to exit if "production Order No." is blank.


    I'll have to look where that code was.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • pdjpdj Member Posts: 643
    Ok, that was one way to solve it :)
    I have several other ISEMTPY statements in other tables, but this is clearly the worst example. I thought it could be solved by some setting or update to either SQL or NAV. But I might just end up having to fix it in C/AL.
    Regards
    Peter
  • ara3nara3n Member Posts: 9,255
    edited 2009-08-31
    You can add a index filter for that Key to only have Production No that are not blank.
    This would dramatically improve the index seek.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • garakgarak Member Posts: 3,263
    Do you use indexhint or recompile?
    Do you make it right, it works too!
  • ara3nara3n Member Posts: 9,255
    I reread your post and you mentioned that the query plan does an index seek. and then lookups the record.
    You can add a index filter for that Key to only have Production No that are not blank.
    This would dramatically improve the index seek.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • pdjpdj Member Posts: 643
    garak wrote:
    Do you use indexhint or recompile?
    No.
    Regards
    Peter
  • pdjpdj Member Posts: 643
    ara3n wrote:
    I reread your post and you mentioned that the query plan does an index seek. and then lookups the record.
    You can add a index filter for that Key to only have Production No that are not blank.
    This would dramatically improve the index seek.

    You are suggesting to use the new SQL2008 feature that makes it possible to set filters on an index, correct?
    First of all I would like to avoid making these kind of changes directly in SSMS, as they are overwritten when the object table is re-imported. But if it the only way...
    Secondly I didn't think filtered indexes were a good match with the NAV way to prevent parameter sniffing. When NAV wishes to send a new SELECT statement it asks the SQL server to generate an EP without providing the filter values. Then SQL has no idea if it can use any filtered indexes or not. Then it would surprise me if it actually plan to use it. But maybe the SQL has some intelligence I don't know about... Have you tried it, and which version of NAV were you using?

    But back to my question: How can it be that SQL Server seems to use a perfect index, and still makes more than 6 million reads?
    Regards
    Peter
  • garakgarak Member Posts: 3,263
    pdj wrote:
    garak wrote:
    Do you use indexhint or recompile?
    No.


    did you test it? We had some several problems, and with the recompile this statement runs very well.
    Do you make it right, it works too!
  • pdjpdj Member Posts: 643
    Mystery solved! (I hope)

    I recently created the Index and right after I flushed the EP cache with “DBCC FREEPROCCACHE”
    I then expected that all EPs would get recompiled the next time. But I guess that several NAV clients were open at the time, and might have had a cursor using the old EP or a previously prepared SQL statement in memory. Either way I guess this has made SQL to ignore my attempt to force a complete re-compile of the EPs.
    We are restarting the SQL server in a few days, and then I’ll check if it re-appears in the Profiler.
    Regards
    Peter
Sign In or Register to comment.