SQL Anywhere database - Computed columns and performance

Author

Computed columns and performance

George

2005-04-20, 9:23 am

Hi every one,

For commun use of an invoice table and fro "user friendly" usage, I'd like
to try computed clomuns.

In invoice, we've got a column called "Total FC" (free or charge, hors
taxes, ...)
We frequently need to get the full taxes total (TTC) and this information
should actually be calculated.

I saw in the doc some informations about comuted column diretly defined in a
table.
Ex: Alter table INVOICE add Total_full double COMPUTE (Total FC + taxes +
...... expression)

Does it affect performances ?
What about a select/update/delete/insert on table INVOICE without access (in
the query) the computed field ?

thanks i advance

Breck Carter [TeamSybase]

2005-04-21, 7:23 am

If a COMPUTE clause contains a scalar expression (e.g., it just
performs some arithmetic on other values from the same row) it won't
hurt performance at all. If it contains a monstrous subquery, it may
be an issue.

Breck

On 20 Apr 2005 07:20:08 -0700, "George"
<george.nospam@yahoo-nospam.fr> wrote:

>Hi every one,
>
>For commun use of an invoice table and fro "user friendly" usage, I'd like
>to try computed clomuns.
>
>In invoice, we've got a column called "Total FC" (free or charge, hors
>taxes, ...)
>We frequently need to get the full taxes total (TTC) and this information
>should actually be calculated.
>
>I saw in the doc some informations about comuted column diretly defined in a
>table.
>Ex: Alter table INVOICE add Total_full double COMPUTE (Total FC + taxes +
>..... expression)
>
>Does it affect performances ?
>What about a select/update/delete/insert on table INVOICE without access (in
>the query) the computed field ?
>
>thanks i advance
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

George

2005-04-21, 7:23 am

Yes of course but I would reformulate my answer:

When this comute column (defined in the create table) is evaluated ?

Ex: If I don't access this colulmn in a select query on Invoices table
(selcet id_fact, cutomers from invoice), will the performance be lower than
without having a computed column ?

2) is the computed column evaluated at each access ? Or just when the row is
modified ?

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> a écrit dans le
message de news: 98qe6116f7orrc7donht
vbok08igbeb6g1@4ax.com...
> If a COMPUTE clause contains a scalar expression (e.g., it just
> performs some arithmetic on other values from the same row) it won't
> hurt performance at all. If it contains a monstrous subquery, it may
> be an issue.
>
> Breck
>
> On 20 Apr 2005 07:20:08 -0700, "George"
> <george.nospam@yahoo-nospam.fr> wrote:
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Dmitri

2005-04-21, 7:23 am

George wrote:

> When this comute column (defined in the create table) is evaluated ?

As common sense dictates - that is, when data are modified.

---------------------------------------------------------------------
ASA SQL User's Guide
Working with Database Objects
Working with tables
Working with computed columns

When computed columns are recalculated

Computed columns are recalculated under the following circumstances:

Any column is deleted, added, or renamed.

The table is renamed.

Any column's data type or COMPUTE clause is modified.

A row is inserted.

A row is updated.

Computed columns are not recalculated when queried. If you use a
time-dependent expression, or one that depends on the state of the
database in some other way, then the computed column may not give a
proper result.
---------------------------------------------------------------------

Dmitri.

George

2005-04-21, 7:23 am

Thank you very much.

This wasn't in my user guide (actually in Java use part).

Last question: it says that the column is evaluated when a row is inserted
or updated. It sould be while the column is updated or inserted so the new
value of the column is unavailable in before triggers and available in after
triggers, isn't it ?

"Dmitri" <NOdimSPAM@mail15.com> a ecrit dans le message de news:
42676e69$1@forums-1-dub...
> George wrote:
>
>
> As common sense dictates - that is, when data are modified.
>
> ---------------------------------------------------------------------
> ASA SQL User's Guide
> Working with Database Objects
> Working with tables
> Working with computed columns
>
> When computed columns are recalculated
>
>
> Computed columns are recalculated under the following circumstances:
>
> Any column is deleted, added, or renamed.
>
> The table is renamed.
>
> Any column's data type or COMPUTE clause is modified.
>
> A row is inserted.
>
> A row is updated.
>
> Computed columns are not recalculated when queried. If you use a
> time-dependent expression, or one that depends on the state of the
> database in some other way, then the computed column may not give a proper
> result.
> ---------------------------------------------------------------------
>
> Dmitri.

Dmitri

2005-04-21, 7:23 am

George wrote:

> This wasn't in my user guide (actually in Java use part).

I've took an excerpt from the ASA9 user guide (you haven't mentioned
your version BTW). In ASA8 user guide, it is stuffed into the "Using
Java in the Database" chapter, however wording is exactly the same.

> Last question: it says that the column is evaluated when a row is inserted
> or updated. It sould be while the column is updated or inserted so the new
> value of the column is unavailable in before triggers and available in after
> triggers, isn't it ?

I'm too lazy to check it myself :), however I'd expect new value of the
computed column to be available in the BEFORE trigger just as any other
column's value.

Dmitri.

George

2005-04-21, 9:23 am

OK thanks

I will take a look

"Dmitri" <NOdimSPAM@mail15.com> a ecrit dans le message de news:
426781a5$1@forums-1-dub...
> George wrote:
>
>
> I've took an excerpt from the ASA9 user guide (you haven't mentioned your
> version BTW). In ASA8 user guide, it is stuffed into the "Using Java in
> the Database" chapter, however wording is exactly the same.
>
>
> I'm too lazy to check it myself :), however I'd expect new value of the
> computed column to be available in the BEFORE trigger just as any other
> column's value.
>
> Dmitri.

Martin Baur

2005-04-21, 8:23 pm

Hi Dimitri

Regarding COMPUTE ...

> ---------------------------------------------------------------------
> ASA SQL User's Guide
> Working with Database Objects
> Working with tables
> Working with computed columns
>
>
> A row is inserted.
>
> A row is updated.

Understood correctly that a COMPUTE can contain a select etc?

If so, do we have to understand that inserting a row in a table cases ALL rows of the table be reCOMPUTEd again?

I cannot believe this. Or my assumption is wrong that COMPUTE can contain a select statement.

If it still IS correct, then having a table with, say, 1 million rows causes 1 million reCOMPUTEs just becuase one row was inserted or updated?

I must be wrong I assume ...

Corrections?

Martin

Breck Carter [TeamSybase]

2005-04-22, 7:23 am

On 21 Apr 2005 18:14:21 -0700, Martin Baur <tinu@mindpower.com> wrote:

No, I don't think it's like Excel where the entire sheet is
recalculated just because you updated a single cell.

You could verify this with a trigger containing a MESSAGE TO CONSOLE
on a little test table.

Breck

>Hi Dimitri
>
>Regarding COMPUTE ...
>
>
>Understood correctly that a COMPUTE can contain a select etc?
>
>If so, do we have to understand that inserting a row in a table cases ALL rows of the table be reCOMPUTEd again?
>
>I cannot believe this. Or my assumption is wrong that COMPUTE can contain a select statement.
>
>If it still IS correct, then having a table with, say, 1 million rows causes 1 million reCOMPUTEs just becuase one row was inserted or updated?
>
>
>I must be wrong I assume ...
>
>Corrections?
>
>Martin

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Glenn Paulley

2005-04-25, 8:24 pm

The functionality of computed columns overlap, but they are not the same.
While a computed column can be defined over any expression (therefore
including a subselect), there does not exist a mechanism in the server to
cause a value in a computed column to be changed when the result of the
subquery changes. Rather, a computed column value in a given row is only
automatically updated when other columns in *its* row are modified.

Glenn

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
news:ifeh61533lt0nue
n8b5fr9cofrftrcp4hd@
4ax.com:

> On 21 Apr 2005 18:14:21 -0700, Martin Baur <tinu@mindpower.com> wrote:
>
> No, I don't think it's like Excel where the entire sheet is
> recalculated just because you updated a single cell.
>
> You could verify this with a trigger containing a MESSAGE TO CONSOLE
> on a little test table.
>
> Breck
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com RisingRoad SQL Anywhere and MobiLink
> Professional Services www.risingroad.com

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Sponsored Links