Re: Efficiency question regarding extracting an aggregate
and the system time

"Enquiring Mind" <Enquiring.Mind@nospam.btopenworld.com> wrote
in message 
news:4805dd46$1@newsgroups.borland.com...
>
> Option B: a single combined query to get both. The SQL command is:
>  select max(T.ID), current_timestamp from Table T, rdb$database
>
> Does this mean that the server creates a Caertesian join

Yes because you have not provided any relation between the two tables. 
However, there is no need to select from rdb$database at all if you are 
selecting from any other. System dat and time is not dependent on that 
table, the only reason it is used is because you need to select from *some* 
table and rdb$database is designed to have exactly one record.

All you need for your query is:

    select max(T.ID), current_timestamp from Table T

-- 
Wayne Niddery - TeamB (www.teamb.com)
Winwright, Inc. (www.winwright.ca) 

Hi,

I have a question about efficiency. I want to extract from a database the 
maximum value of a table field, and the sytem time. The options I am 
considering are:

Option A: separate queries to get the maximum table field value and the 
system time. The SQL commands are:
  1)  select max(ID) from Table
  2)  select current_timestamp from rdb$database

Option B: a single combined query to get both. The SQL command is:
  select max(T.ID), current_timestamp from Table T, rdb$database

Clearly Option A has the overhead of 2 trips to the server. However when I 
checked Option B using the Interactive SQL tool, I got the following 
message:

  Statement: select max(T.ID), current_timestamp from Table T, rdb$database
  PLAN JOIN (T NATURAL,RDB$DATABASE NATURAL)

Does this mean that the server creates a Caertesian join of T.ID and 
current_timestamp before evaluating the aggregate? (i.e a n by 2 table)

Incidentally, I first tried the following combined SQL command for Option B:
  select max(T.ID), S.current_timestamp from Table T, rdb$database S

but this gives a database error on current_timestamp . Why?

TIA,

EM

"Wayne Niddery (TeamB)" <wniddery@chaffaci.on.ca> wrote in
message 
news:48062172$1@newsgroups.borland.com...
>
> Yes because you have not provided any relation between the two tables. 
> However, there is no need to select from rdb$database at all if you are 
> selecting from any other. System dat and time is not dependent on that 
> table, the only reason it is used is because you need to select from 
> *some* table and rdb$database is designed to have exactly one record.
>
> All you need for your query is:
>
>    select max(T.ID), current_timestamp from Table T
>
Many thanks for that. With that syntax I take it that Option B is more 
efficient, because it requires just one trip to the database.

Regards,

EM