I'm thinking of setting up a slew of computed columns along the lines of TRIM(BOTH ' ' FROM COALESCE(_Forename,'')+' '+COALESE(_Surname,'')) so that people can pick them in my query generator. My alternative is to define the sql as a UDF in the query generator.

Which I use will depend on when a computed column is computed.

Is it

a) when that column is accessed when a row containing it is accessed

or

b) whenever the row is accessed?

Roy Lambert

Sorry Roy, this is not actually an answer to your question, but a different model for doing the same thing.

I use Views to pull data together from disparate tables into a form which the user actually wants to see (i.e. FirstName+ ' ' + LastName) this is quick and effective and allows things like StaffType and Team to exist as Integers in the base "staff" table, but to be looked up in the View from reference tables.

These Views make my users lives much easier when they are generating their own reports, as they almost always access the View rather than the base table.

This also avoids lots of duplication in the base-table (i.e. the presence of a StaffType INT and a StaffType VARCHAR/Computed) which is confusing and messy. I like the separation between the Table and View as well, as it makes users clearer about how the relationships in the table are managed (they can see the SQL for the View in EDBMgr if they want to)

Views _seem_ efficient ... though I haven't really tested it! They seem to be cached by EDB & not to be re-run every time they are accessed (though it would be great to know more about how this is actually done in EDB to really optimize it better)

Adam

Adam


>Sorry Roy, this is not actually an answer to your question, but a different model for doing the same thing.

Nowt in this post to be sorry about!

>I use Views to pull data together from disparate tables into a form which the user actually wants to see (i.e. FirstName+ ' ' + LastName) this is quick and effective and allows things like StaffType and Team to exist as Integers in the base "staff" table, but to be looked up in the View from reference tables.

With the ones I'm thinking of I don't think I'm in danger of confusing people. In general these will be used in tabular report generation. The query generator specifies which columns and which tables are available and the users can select the columns for the SELECT, the WHERE and the SORT clauses

>These Views make my users lives much easier when they are generating their own reports, as they almost always access the View rather than the base table.
>
>This also avoids lots of duplication in the base-table (i.e. the presence of a StaffType INT and a StaffType VARCHAR/Computed) which is confusing and messy. I like the separation between the Table and View as well, as it makes users clearer about how the relationships in the table are managed (they can see the SQL for the View in EDBMgr if they want to)

The last part is bloody dangerous. Unless your users are very well trained allowing them into EDBManager is just plain scary.

>Views _seem_ efficient ... though I haven't really tested it! They seem to be cached by EDB & not to be re-run every time they are accessed (though it would be great to know more about how this is actually done in EDB to really optimize it better)

I think Tim has posted about this but I can't remember what was said, wether Views get opened when the databases opened or only at first viewing and then updated when needed. Checking I think its when they're first opened.

If you want to find out how efficient they are just test the SQL. I just did a small test


CREATE VIEW "TEST" AS
SELECT
*
FROM
Contacts  
JOIN Career ON Career._fkContacts = Contacts._ID
JOIN Companies ON Companies._ID = Career._fkCompanies


then selected from that

SELECT
_Name,
_Forename,
_Surname
FROM TEST
WHERE _Surname = 'Smith'

ignoring EDBmanager's lies (0.078 seconds) it takes c10 seconds. This may just be EDBManager forcing a refresh of the view I don't know. My worst case would (from memory) join 7 tables

Roy Lambert

Roy,

<< a) when that column is accessed when a row containing it is accessed >>

This.  Plus, the computed column is only re-computed if a) it contains
user-defined functions and/or b) any of the columns that it references
changes.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< ignoring EDBmanager's lies (0.078 seconds) it takes c10 seconds. >>

It's not a lie.  The *query execution* takes 0.078 seconds - the opening of
the view probably takes longer, but isn't included in the query execution
time.  The reason for this is the second and subsequent opens of the view
will not consume any time at all, unless the tables involved in the view
have changed.  In general, table opens are not included in the query
execution times for this very reason - including them makes it impossible to
accurately compare actual execution times between first and subsequent
executions.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


That sounds very usable.

Roy Lambert

Tim

>It's not a lie. The *query execution* takes 0.078 seconds - the opening of
>the view probably takes longer, but isn't included in the query execution
>time. The reason for this is the second and subsequent opens of the view
>will not consume any time at all, unless the tables involved in the view
>have changed. In general, table opens are not included in the query
>execution times for this very reason - including them makes it impossible to
>accurately compare actual execution times between first and subsequent
>executions.

In that case EDBManager is not behaving as a normal app would. Using the example above each time I click execute (without unpreparing) the query its taking c10secs.

The messages read:

Result set closed

then about 10 secs later

The statement was executed successfully, 151 rows were affected in 0.078 seconds

Roy Lambert

Roy,

<< In that case EDBManager is not behaving as a normal app would. Using the
example above each time I click execute (without unpreparing) the query its
taking c10secs. >>

This has to do with the way that the SQL is extracted from the editor and
assigned to the TEDBQuery.SQL property.  When the text is assigned directly
to the TStrings (SQL property), there isn't an opportunity to intercept the
assignment and make sure that things actually changed.

I'll see if I can fix this so that it works consistently.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>This has to do with the way that the SQL is extracted from the editor and
>assigned to the TEDBQuery.SQL property. When the text is assigned directly
>to the TStrings (SQL property), there isn't an opportunity to intercept the
>assignment and make sure that things actually changed.
>
>I'll see if I can fix this so that it works consistently.

As long as it doesn't do it in the real world its not to important. But that raises the question in my mind of when does the view have to be refreshed?

Roy Lambert

Roy,

<< As long as it doesn't do it in the real world its not to important. >>

You'll see the behavior if you use a mix of SQL.Text:='..... '  combined
with SQL.Assign(....

<< But that raises the question in my mind of when does the view have to be
refreshed? >>

When does it have to be refreshed by the user/developer, or when does it
have to be refreshed by EDB ?

--
Tim Young
Elevate Software
www.elevatesoft.com