Form is Slow, Report is Fast

bcoleman

Hello everyone,

I'm hoping that one of you smart folks out there can help me. I have a custom table with 900,000 records in it. The primary key is a composite key consisting of Code20, Code10, Integer. The problem is that when I view this table in a form, it is excruciatingly slow (about 3 seconds just to arrow between rows on the form). It was fast until about 2 weeks ago. There was no change in the table contents except maybe a few extra thousand rows added in the normal use of the table. The slowness happened all at once and multiple users told me about it. When I create a report off of the table, it reads the rows lightning fast. I am using NAV 2009 SP1 with SQL Server 2008 R2.

Here is what I have tried:

• I rebuilt all indexes inside of SQL Server
• I optimized the table inside of NAV (File -> Database -> Information -> Tables -> Optimize)
• I removed all flow fields from the table
• I removed all Table Relations
• I created a form from scratch and added only one field from the table to it (I wanted to make sure there was no code in the existing form that I forgot to strip out)
• I removed all functions, code, and variables from the table
• I set the SourceTableView on the form to be equal to the Primary Key
• I set Debugger to be active and set "Breakpoint on Triggers" to be true to see if any code is being called when rows are changed in the form (there wasn't any)
• I removed all indexes except the primary key
• I set a filter on the first field of the primary key that narrows down the list to 10 rows... and still slow.

I am at a loss. Any ideas? (One strange thing that may help you... If I create an index that contains only the last 2 fields of the primary key, it will go between the rows very fast when the first field in the index (second field in the primary key) is blank. I can fly through those lines until I hit rows where the field contains values.)

Thanks in advance for your time reading this post and thinking about it.

ara3n

turn on sql profiler and see what takes so long.

bcoleman

ara3n,

You're a life saver. I turned on SQL Profiler and found that it was my "Record Link" table that was taking so long. I rebuilt the index for that table and voila, it's working like a charm.

Thanks a million.

ara3n

you are welcome.