Slow FIND/FILTER on 3.60 SQL under 50SP1 client

rsaritzky

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

garak

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

stryk

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

rsaritzky

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

Toddy_Boy

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.

stryk

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 ...