I've discovered today that we in EID are not the ones that are using the ADO trick to tune up certain queries on Dynamics NAV. 
There are cases where queries executed via C/AL can be more complex and less performant that queries directly performed via T-SQL. My personal case where I love to use this trick is for example if you have to do a SELECT DISTINCT query via C/AL: via NAV code, you have to use two at least two record variable and marks. Why not run a simple SELECT DISTINCT via T-SQL and reads the data from NAV?
This is possible by using Microsoft's ActiveX Data Objects (ADO), a set of Component Object Model (COM) objects for accessing data sources that provides a layer between programming languages and OLE DB.
The post I've linked shows exactly how to do. Basically:
- Reference the "'Microsoft ActiveX Data Objects 2.8 Library"
- Create a connection to your database by using an automation variable of the type "'Microsoft ActiveX Data Objects 2.8 Library'.Connection"
- Launch your query and retrieve your data
- Close the database connection
In this way you can use the standard ADO command inside NAV, so you can execute SQL instructions or directly stored procedures with parameters.
We have observed performance improvements on certain operations by directly using ADO from NAV, so this is a trick that you can have in mind.
Remember that, if you use SQL stored procedures inside NAV, they're not included on a backup launched directly from the NAV client...
Technorati Tag:
Dynamics NAV