SQL stored procedures, Replacing nav code

jannestig

HI all,

Just as a general point i would be interested in peoples opinions in replacing standard NAV code with stored procedures for large posting or replication processes.

We currently have 3 in place at the moment and quite happy with the performance gains that we have acheieved under the new 5.1 client with a 4.03 base.

There is always a trade off as usualy large batches go faster and smaller batches can actually take longer, As Nav is becoming more integrated with SQL this seems to be a logical step in the right direction.

Any thoughts of the future Risks/advantages of using Stored Procs?

Marije_Brummel

The construcition NAV is using for reports and dataports is killing performance.

We use T-SQL very frequently to replace large batch/update processes and also to import/export large amounts of data.

Just to compare: 2 weeks ago we wrote a function in SQL that took 1 minute and 40 seconds to complete a task that took 8 hours in NAV.

DenSter

Replace reports and dataports with stored procedures yes I can see a benefit. Replacing posting routines though you'd have to replicate all checks and balances and make sure that all business logic is contained within those stored procedures.

I'm not saying it is impossible, just that it will take HUGE amounts of programming, and your NAV support will be null and void when you do.

davmac1

SQL views defined as linked objects in NAV can greatly simplify and speed up reporting using Navision.

kriki

And if you want to keep reporting in NAV, you should separate the reporting in 3 steps:
1) parameter request (filters+option-form)
2) dataretrieval (get your data with the best keys you can find and save it in temptabels)
3) printing (print the data from the temptables).

This system can speed up a lot (though not as fast as doing all in SQL, but it remains in NAV).

I use ALWAYS this system if I have to read a lot of data for reporting or statistics (@Mark: ).

azerty74

Although performance wise I can see the benefits, I do not think it is a good idea to start putting business logic in the database tier.