Slow FIND/FILTER on 3.60 SQL under 50SP1 client

rsaritzky
Member Posts: 469
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
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
0
Comments
-
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
RegardsDo you make it right, it works too!0 -
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örgJörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool0 -
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.
RonRon0 -
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 wrong0 -
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 Tool0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions