SQL Question - Views

[PointsWell]

Background

I have a Query which runs on a BOG. Each of the BOs in the BOG have shortcuts on them. This makes the query run like a dog. The reason for this is that AIM brings every item into context and checks every path for every shortcut.

Solution

Create an SQL view to generate only the data that I need (I don't need everything being brought into context). So I have

Code: Select all

EXEC_SQL `SELECT
    CO1000.ID AS ID,
    CO1000.BASTIMESTAMP AS BASTIMESTAMP,
    CO1000.BASVERSION AS BASVERSION,
    CO1000.obTenant_REN AS obTenant_REN,
    CO1000.obTenant_RID AS obTenant_RID,
    CO1000.obTenant_RMA AS obTenant_RMA,
    CO1000.NameIndex AS NameIndex,
    1000 AS ContactType
FROM
    CO1000
UNION
SELECT
    CO1001.ID AS ID,
    CO1001.BASTIMESTAMP AS BASTIMESTAMP,
    CO1001.BASVERSION AS BASVERSION,
    CO1001.obTenant_REN AS obTenant_REN,
    CO1001.obTenant_RID AS obTenant_RID,
    CO1001.obTenant_RMA AS obTenant_RMA,
    CO1001.NameIndex AS NameIndex,
    1001 AS ContactType
FROM
    CO1000 CO1001
UNION
...
order by
    NameIndex; `  RETURN COAllView

Results now shows lightning fast.

Problem
How do I get the SQL to dynamically use the correct value for LoggedInRegularUser.obTenant.ID?

I can create the view in the database and leave the SQL code separate from AIM, but I've learned that this is a route to problems later - i.e. when you forget that you have SQL in the database and you start getting errors because you changed something, so I'd rather if possible have the SQL all contained within AIM.

[BLOMASKY]

I know you want to keep the SQL all in your Aware app, but let me suggest that is a mistake.

1st, you mention that if fields change your SQL might break, but if fields change your aware program will compile fine but break when the user trys to run the SP.

2nd, Since it is not a stored procedure, it will not be pre-compiled / optimized so it will run slower

It is pretty hard to break a SP when you change your aware data structure unless you rename a field. Adding new ones will not break the SP.


Just my 0.02 cents worth


Bruce

[PointsWell]

I know you want to keep the SQL all in your Aware app, but let me suggest that is a mistake.

1st, you mention that if fields change your SQL might break, but if fields change your aware program will compile fine but break when the user trys to run the SP.

Yeah, I've been down the route of breaking an SP before. Because the SP is outside AIM it places an extra hurdle into the debug process - out of sight out of mind.

2nd, Since it is not a stored procedure, it will not be pre-compiled / optimized so it will run slower

I'm less concerned with this as the view is very simple

After I posted this I realised that I could just wrap the EXEC_SQL inside an EXEC_STRING and place the LIRU.obTenant.ID into that.

So I guess this is solved unless someone has a better suggestion.