Editing SQL in Framework Manager

[sasha73]

Hi
Is it better to let Reportnet run its Sql queries or
it is better for performance to do SQL Editing in the Framework manager
The queries have generic sorting function can that be disabled..

any ideas

Thanks

 

[Alomar2002]

Hi sasha,

I think (as always) the answer to this question depends on what you're trying to achieve. The Framework Manager model will allow QS/RS to write SQL automatically, using the joins that you've specified. It might not be the best, most efficient SQL in the world, but it should do the job (providing you've build the model correctly, of course!)

An example of where I've taken the time to write SQL in FM itself:

- A database that contains a large 'entitlements' table (security) that is joined to many other tables using OR statements in the WHERE clause. The table was also constrained by who was logged in (ie. the username)

This case required the use of fairly complex SQL and a macro to determine who was running the report. I suppose I could have written it using Report Studio, but it was much neater to stick this in a Query Subject in the model. I also think the report is faster and more maintainable in this format.

Hope this is useful!

Alomar

 

[the6campbells]

I suggest that many who don't know how to use/leverage FM will tend to fall back to hand coding SQL. Ideally, you should limit the desire to do this and where possible leverage the #macro# facility that allows a type-in SQL query to be dynamically generated.

The query engine does internally attempt to trim/refine the conceptual SQL behind the subjects that have to be included to support your queries in RS or QS. Query subject that are hand typed will not be be trimmed other than the items the query engine will project for that the type-in SQL query may attempt to return.

Another scenario where stubbing in native SQL makes sense is during a quick POC. That is, often you have existing proven SQL that produces result sets you want to render. You might initially define query subjects that the report authors work against. Initially you stub in the native-SQL so they can develop/test and subsquently you re-engineer what that QS is based on and move to model based subjects etc.