calculated field in remote data

I need a calculated field in a remote view. What are the costs/benefits of
defining that field in the back end vs. in my SQL statement or remote view?
Is one method preferred over the other?

Bernhard
Thu Nov 02 04:22:05 CST 2006

Hi Paul,

> I need a calculated field in a remote view. What are the costs/benefits of
> defining that field in the back end vs. in my SQL statement or remote view?
> Is one method preferred over the other?

I think, there is no general advise what is the best method.

A calculated field can be only a constant or a very complex aggregation, maybe
the result used in other parts of the SQL command (GROUP BY, WHERE ...).
Some calculations may (only) be done with the help of stored procedures.
Different back ends may behave different with the same SQL command.

If you want fast results, I think there is no other way than testing different
approaches.

There may also be other aspects, like maintainability of your code.

You have different places where you can put your calculation:
Define a view on the back end server and use it in your remote view.
Right in the remote view, note: its SQL command is allways executed at the back
end server.
Or you use a remote view without the calculated field. Then define a local view
based on the remote view and add the calculation there.
Another place for the calculation may be in the place where you need the
results, i.e. not in any view at all.

Regards
Bernhard Sander

Paul
Fri Nov 03 00:56:55 CST 2006

Thanks for your response.

It turns out that I have to provide that same calculated field to another
application via a view on the back end, so I thought it best to just define
that field in the table. Now it's easily available everywhere.





"Bernhard Sander" <fuchs@no.spam> wrote in message
news:uR%23P$im$GHA.996@TK2MSFTNGP02.phx.gbl...
> Hi Paul,
>
>> I need a calculated field in a remote view. What are the costs/benefits
>> of defining that field in the back end vs. in my SQL statement or remote
>> view? Is one method preferred over the other?
>
> I think, there is no general advise what is the best method.
>
> A calculated field can be only a constant or a very complex aggregation,
> maybe the result used in other parts of the SQL command (GROUP BY, WHERE
> ...).
> Some calculations may (only) be done with the help of stored procedures.
> Different back ends may behave different with the same SQL command.
>
> If you want fast results, I think there is no other way than testing
> different approaches.
>
> There may also be other aspects, like maintainability of your code.
>
> You have different places where you can put your calculation:
> Define a view on the back end server and use it in your remote view.
> Right in the remote view, note: its SQL command is allways executed at the
> back end server.
> Or you use a remote view without the calculated field. Then define a local
> view based on the remote view and add the calculation there.
> Another place for the calculation may be in the place where you need the
> results, i.e. not in any view at all.
>
> Regards
> Bernhard Sander