SQL View In A Join

PaddyMullaney · 2005-08-01

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 · 2005-08-01

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 · 2005-08-01

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 · 2005-08-02

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.

Howdy, Stranger!

Quick Links

Categories

SQL View In A Join

Comments