Login ProductsSalesSupportDownloadsAbout |
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 |
Barry | 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 |
Fri, May 17 2013 11:16 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |