|Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Enhancement Requests and Suggestions » View Thread|
|Messages 1 to 2 of 2 total|
|Can Group By be optimized some more?|
|Thu, May 16 2013 4:01 PM||Permanent Link|
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.
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.
MAX("end_time") AS "End_Time"
GROUP BY "company_id", "bld_num", "period"
SensorData: 250867 rows
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
|Fri, May 17 2013 11:16 AM||Permanent Link|
Tim Young [Elevate Software]
Elevate Software, Inc.
<< 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.