SQL View In A Join

PaddyMullaney

I have a particularly long running filter I am using in Navision. It is taking so long because it filters on the basis of the ledger entries, so Navision has to perform a check on all ledger entries and then filter the parent line. On SQL this is very long running as it builds a cursor and queries every line in the table.

I can very quickly provide the result set from the ledgers using a SQL Query and place it in a View containing the required Primary Keys for the lines.

I have then to use the internal Navision code to filter these, and I am trying to work out the quickest way to do this. Using the Mark functionality there seems to be a lag, and the amount of records means it is too long to create a SetFilter statement.

Has any one any ideas how this could be done?

Paddy

kine

I do not know exactly how you can speed up filtering on View, but may be that helps set order on the view on the fields, you are filtering...

davmac1

If you are running the SQL Server option, you can create a SQL Server view, then define a Navision table that matches the View name (use Navision naming logic) - Navision will then allow you to attach the Navision table to the view, which for reporting purposes will act like a single Navision table.

PaddyMullaney

The problem with using a view is that I already have a form set up with all the functionality and this set of data is just one sub set.

I was trying to get the filters on this form in order to keep all the the functionality in one place, but because they are taking so long to set I am having to re-think.