Query Optimization

Tennek
Tennek Member Posts: 11
edited 2010-06-21 in SQL Performance
hi all,

When I run the below query in the query analyzer and show the execution plan it is as follows:
select <- sort <- clus. index Seek.
SELECT  * FROM "testdb$Prod_ Order Routing Line" WITH (READUNCOMMITTED)  
WHERE (("Status"=3)) and "Ending Date"<'2010-06-01 00:00:00' 
ORDER BY "Ending Date" DESC,"Ending Time" DESC,"Status" DESC,"Prod_ Order No_" DESC,"Routing Reference No_" DESC,"Routing No_" DESC,"Operation No_" DESC

Why is it using the cl. index and doing resorting when there is an index with these columns ??:
Status, Ending Date, Ending Time, Prod_ Order No_, Routing Reference No_, Routing No_, Operation No

br Kenneth

Comments

  • stryk
    stryk Member Posts: 645
    Well, there could be several reasons ...
    Which NAV version do you use? Are you maintaining "statistics"?

    It could be that this operation is a "Dynamic Cursor" operation - this might cause the SQL Server to pick an index which is mostly matching the ORDER BY clause, instead of the WHERE ...
    The it depends on the "Selectivity" of the "Status" field ...

    Last but not least, this is obviously some FIND('+') command in NAV. Could this be a FINDLAST instead? FINDLAST will not create a cursor, thus the query would be fully optimized on the WHERE. Further, as the result-"set" is just one record instead of "n" this would reduce the workload.

    But the overall question should be: is the current execution a problem? Too many "Reads"? Long "CPU" or "Duration"?
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Tennek
    Tennek Member Posts: 11
    Which NAV version do you use? Are you maintaining "statistics"?
    version is 6.01(RTC) - this query is only run on a local developer enviroment..
    It could be that this operation is a "Dynamic Cursor" operation - this might cause the SQL Server to pick an index which is mostly matching the ORDER BY clause, instead of the WHERE ...
    The it depends on the "Selectivity" of the "Status" field ...
    So why is the clustered index ussed? should it not use the below index?
    Status, Ending Date, Ending Time, Prod_ Order No_, Routing Reference No_, Routing No_, Operation No

    Could you please explain how the "Selectivity" of the "Status" field has to do with the index selection?
    and is there some way of approving this if it's a "Dynamic Cursor" operation ?
    Last but not least, this is obviously some FIND('+') command in NAV.
    How do you know this comes from find(+) ? (I have trouble tracking c/al to sql in RTC)

    and the main reason for asking is the number of reads. about 20000, 300-400 times a day according to profiling.

    thank you..
  • pdj
    pdj Member Posts: 643
    Tennek wrote:
    Could you please explain how the "Selectivity" of the "Status" field has to do with the index selection?
    Because Status only have very few distinct values. Then is doesn't make much sence to pick an index starting with this field.
    Just like the Open field in Entry tables. SQL rarely picks these indexes as it only reduces the dataset by 50%.

    But the main problem is the awfull dynamic cursors from NAV6 (and NAV5 from some hotfix).
    This makes SQL pick the index based on the ORDER BY instead of the WHERE clause.
    Tennek wrote:
    How do you know this comes from find(+) ?
    Because of the DESC after each field in the ORDER BY. That means it is reading backwards.
    Regards
    Peter
  • stryk
    stryk Member Posts: 645
    Exactly.


    But just for curiosity:
    If you create theis index in SSMS ... 
    USE "testdb"
GO
CREATE INDEX test01 ON "testdb$Prod_ Order Routing Line"
("Status", "Ending Date")
    ... does this help? (If not just DROP the index)
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Tennek
    Tennek Member Posts: 11
    does this help? (If not just DROP the index)

    That was also my first thought but i did not help. Still using the clustered index.

    Based on both previous replies I added "Top 1" to the query (findlast). Then it is using the Non-clustered index !?!
    Ending Date, Ending Time, Status, Prod_ Order No_, Routing Reference No_, Routing No_, Operation No

    But anyway I think I got the picture.

    Thanks a lot for the explanations. :thumbsup:

    Br
    Kenneth

Categories