If I have an SQL statement like:

select max(end_time) as End_Time from SensorData   group by company_id, bld_num, period

it takes 3.5 seconds to return the 4 rows, one row per bld_num.

There is a unique index ix_Sync using the columns:
 company_id, bld_num, period, end_time, sensor_id

so it should have been able to use the index to find the max(end_time) in just ms. Right?
The table has 250k rows with only 1 unique company_id (so far) and 4 unique bld_num's and 1 unique period.
(This is all Unicode and EDB 2.12 B2)

I am assuming this is a limitation of the EDB optimizer and that's why I posted it here.

TIA

Barry

================================================================================
SQL Query (Executed by ElevateDB 2.12 Build 2)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================

SELECT ALL
MAX("end_time") AS "End_Time"
FROM "SensorData"
GROUP BY "company_id", "bld_num", "period"

Source Tables
-------------

SensorData: 250867 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows


Result set I/O statistics
-------------------------

Total rows visited: 250867

Row buffer manager

Max buffer size: 1MB Buffer size: 512B

Hits: 752591   Misses: 0   Hit ratio: 1

Reads: 0   read: 0B

Writes: 5   written: 1.38KB

Index Page buffer manager

Max buffer size: 2MB Buffer size: 8KB

Hits: 250871   Misses: 0   Hit ratio: 1

Reads: 0   read: 0B

Writes: 2   written: 16KB

Barry,

<< so it should have been able to use the index to find the max(end_time) in
just ms. Right? >>

Correct.  I'll add it to the list.

Tim Young
Elevate Software
www.elevatesoft.com