Performance? DAI View vs. server-based

[Bob Cergol]

Hi,

Given a very large and complex SQL view that will be used as a data source for reporting on a frequent basis is there any performance advantage to defining that view in the SQL database over defining it in Dynamic?

I get the impression that MS SQL Server somehow 'pre-compiles' the view and so 'knows' about it, and maybe caches information about it, so it can deliver the data more quickly from that "table" than parsing the same SQL script fresh each and every time a Dynamic AI report is run.

I guess another way of asking the question: Does SQL server have to work harder to execute the view's script fresh from Dynamic, than if Dynamic used the same view defined in the SQL database?

Thanks,
Bob

[Carsten Sørensen]

Hi Bob,

In theory a pre-compiled SQL script / stored view definition is faster than sending SQL script to be translated. However with todays computers it is not something that will make a difference that I believe you will be able to notice. If you make indexed views or other special performance optimizing arrangements on the database view you can of course benefit from that (at the same time as you will be bound to certain indexes for the benefit, have to maintain etc.). But IMO the pragmatic answer is "no" there is no real difference.

Best regards,
Carsten

[Bob Cergol]

Hi Carsten,

Thanks for the answer.

In that case I think creating the view in Dynamic is an advantage -- at least for my purposes. It will be much easier for me to maintain these views in Dynamic. I don't have to touch the "foreign" system's database in any way or even run SQL Mgt. Studio on that server. The vendor of that system frowns on writing to their database and I wouldn't think of creating any new indices. They might not even like me creating a view.

Now maybe our other programmers will wish I defined the view in the database so they can use it

Bob