Slow FIND/FILTER on 3.60 SQL under 50SP1 client

rsaritzkyrsaritzky Member Posts: 469
edited 2013-11-21 in SQL Performance
Hi:

We have a few fairly large tables - Vendor Ledger Entry (1.2 million records) and Job Ledger Entry (3.1 million records).

When using a non-primary key (e.g. "Document No, Document Type, Vendor No." for Vend Ledg Entry), FINDs and FILTERs under the 5.0SP1 SQL client are very slow - e.g. 20 seconds to find one document number. Searches on the primary key (e.g. Entry No.) are fine.

I am new to 5.0/6.0 - I worked as a developer in the 3.6/4.0 world for several years, but am coming back to NAV after 3 years of absence.

Is this index-search problem a known issue with 5.0SP1 and later?

Thanks

Ron
Ron

Comments

  • garakgarak Member Posts: 3,263
    Which SQL Server version do you use and what for traceflags do you have enabled also have u enabled / disabled the indexhint / recompile?

    here some links that you should read.

    viewtopic.php?f=34&t=31079
    viewtopic.php?f=23&t=31509
    viewtopic.php?f=34&t=13154

    Regards
    Do you make it right, it works too!
  • strykstryk Member Posts: 645
    Hi Ron,

    well, there could be seveal reasons for this. At first you should monitor this process using the SQL Profiler. There you would see, which query exactly was sent to the server and how it was processed.
    High numbers of "Reads" (e.g. > 1000) or long "CPU" and "Duration" (e.g. > 50 msec) woould indicate that SQL has done something stupid like scanning an index.

    Once you know all this - please post the query here - you could try to solve that problem, e.g. by creating an optimized index, or by applying a RECOMPILE (or INDEX) hint.

    Regards,
    Jörg
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • rsaritzkyrsaritzky Member Posts: 469
    Thanks to everyone for their suggestions. I am still learning tools like SQL Profiler and the new features of NAV SQL like index hints. I will read, study and test and post my results.

    Ron
    Ron
  • Toddy_BoyToddy_Boy Member Posts: 230
    Hi Jork

    I have seen on the SQL Profiler that a particular query duration is very high, I think SQL Server has gotten a key very confused and I think this RECOMPILE / INDEX HINT may resolve one of the long running query problems I have.

    So can you elaborate on this a little please i.e. where do I specify this RECOMPILE?
    Once you know all this - please post the query here - you could try to solve that problem, e.g. by creating an optimized index, or by applying a RECOMPILE (or INDEX) hint.
    Life is for enjoying ... if you find yourself frowning you're doing something wrong
  • strykstryk Member Posts: 645
    Toddy,
    you are replying to a posting which is 4 years old; if you have any query issues I suggest you open another thread, explaning your probem in detail.
    Before you should use RECOMPILE hints or stuff there's plenty more/other things to check and to do. Poor query performance could have MANY reasons ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
Sign In or Register to comment.