Has anyone of you heard about this product: “SQL Procedure Management” - it’s developed by a company called PLP Consult (http://www.plpconsult.dk/) in%20in) Denmark and they have the following claim:
One specific simple test discloses that the same function run in traditional NAV Code is 2100% slower than the same T-SQL statement run directly on the server from within NAV!
I just thougt that this sounds too good to be true! So I would love to hear if anyoen of you have tried it!
Well isn’t this the same as another Danish company offering the same. Of course I would need the sentence
translated from Danglish to English, maybe they meant to say that the NAV command takes 21 times longer to runn than T-SQL?
Anyway, take a look at my last BLOG specifically addressing this issue:
Of course its possible to speed SOME transactions up in SQL, since I can write a T-SQL command that only needs to get say two or three fields. So their might be an index that SQL can use, and build a plan that is fast, where as in NAV since I always send a SELECT *, SQL may think it easier to do a Clustered Seek onthe primary key (since that is the only place that all the fields are stored). Rasheds comment to my blog was in some ways suggesting what this company is doing.
BUT the big BUT!!! If you have a routine that runs for 2 hours and you look at one part of that code that maybe takes 21 seconds and you reduce that to ONE second, then you dont speed the whoel report up by 2100%, you speed the 2 hours report up by 20 seconds, so it now only takes 1 hour 59 minutes and 40 seconds to run in stead of 2 hours.
I am quite sure that in a projec as large as yours, you will be able to make great performance improvements with a tool like this, but don’t be like the person at Hyneks session in Convergence, that got up and said how this is all silly, since if NAV just used one of these tools, then instantly Item Cost Adjust would be at least 20 times faster.
The real issue there is do you want to introduce another layer of code that needs to be managed. It is true that you can write stored procedures that are way faster than NAV code, but you will need to replicate bisuness logic in those stored procedures. Once you do, all of a sudden you have to make sure that mods in the C/AL code are also reflected in the T-SQL code. The bigger the project, the more there is to manage.
Plus, as soon as you use a tool like that, you’d lose any support from MS.
I actually downloaded information about this product from the Danish part of Partnersource!
Does that mean that the product is supported? That would be kind of big.
Well it might be an Add-On, so supported in that way.
I heard of the product. I was actually thinking of building something just like it – and then decided not to when I saw that it was already an add on. If you can get a demo version, I would like to hear how it runs.