Strange Filter Problem :?

suvidha

Hi,
We r using Nav 3.7 SQL DB.
Here is a report with dataitem as G/L entry with the request filter field Document No.
The G/L entry dataitem is not sorted on any key.

When the user enters Document No.: Doc001..Doc005 The report prints 5 Docs immediately.
Similarly when user enters Document No.: Doc001|Doc002|Doc003|Doc004|Doc005 The report prints 5 Documents fast...

Everything was fine...

Now we took backup of this DB and Restored it in other place..
No changes done on this DB.
But same report here with filter Document No.: Doc001|Doc002|Doc003|Doc004|Doc005 works equally fine whereas Document No.: Doc001..Doc005 makes us wait for hours ](*,)
plzzz help..... :roll:

JedrzejT

Did you restore database in native database option?

suvidha

No.... SQL only...!!!

JedrzejT

sorry i don't have idea then..

kriki

If you did a NAV-backup, do an index rebuild. (you can also do this through File=>Database=>Information=>Tables=>select all records and do Optimize.

suvidha

We restored SQL Backup.

Additional Info:
The Scan Density of all indexes in G/L Entry table is well above 70%.
The Size of the Database is above 150 GB.

kriki

Try an optimize anyway. It is possible the statistics are out of date. It is also possible it has a wrong execution plan.

If this does not work, do a trace to see what happens on SQL.

vsalot

Which version of sql are you using? both the places sql version is same or different?

I am having the simillar kind of problem If I use 1..2 then it is working fine but if i use 1|2 it is not working at all.
It is taking too much of time. Database size is 400Gb. Recently converted to SQL DB from 3.7 Native to 5.1Sql. In Native DB, it is working very fast.

Is there any solution?

DenSter

suvidha wrote:

We r using Nav 3.7 SQL DB.
Here is a report with dataitem as G/L entry with the request filter field Document No.
The G/L entry dataitem is not sorted on any key.

Sort the G/L Entry dataitem on the "Document No,Posting Date" key and see if that speeds things up.