|
Changes The query engine optimizes IN / NOT IN <table> on a single value, for cases where the table has no index on the searched field. The query engine optimizes IN / NOT IN <table> on a tuple, both for cases where the table has no index on the searched fields and for cases where it has one, by making more efficient use of it. (start note) As is usual for optimizations that start building and using indexes instead of doing full scans, the speed ups are rather dramatic. For example, here are the results for a test query similar to the one that brought this performance issue to our attention in the first place: --SQL9 VALUE @count INT32 = 1000; DROP TABLE t; CREATE TABLE t (mfd_id INT64, INDEX mfd_id_x BTREE (mfd_id), v INT32); INSERT INTO t (v) TABLE CALL ValueSequence(1, @count, 1); INSERT INTO t (v) TABLE CALL ValueSequence(1, @count, 40); DELETE FROM t WHERE mfd_id NOT IN (SELECT First(mfd_id) FROM t GROUP BY v); With @count set to 1000 records: old 1.110 sec -> new 0.029 sec. With @count set to 5000 records: old 27.411 sec -> new 0.130 sec. With @count set to 20000 records: old 482.044 sec -> new 0.544 sec. (end note) New query function: StringStart. Reduces a string to the first N characters. If the specified number of characters is negative, the entire string is returned, consistent with the behavior of StringSubstringLen. New query function: StringEnd. Reduces a string to the last N characters. If the specified number of characters is negative, the entire string is returned, consistent with the behavior of StringSubstringLen. New query functions: TileSieve / TileSievePar. Perform a sieve operation on an image channel: pixels with the same value form areas, areas smaller than the specified threshold are merged into their neighbors = get assigned the value of the neighbor they are merging into, the result is a new image. Parameters: - source image,
- source channel (0 ... total number of channels - 1),
- quantization level (setting this to 1 tells the system that pixels with the values of 1.1, 1.2 and 1.7 are similar enough to each other and should be put into the same area if they are neighbors, but a pixel with the value of 2.0 should be put into a different area),
- area threshold in pixels (areas smaller than this value will try to merge into their larger neighbors, areas equal to or larger than this value will accept their smaller neighbors),
- method used to determine which neighbor to merge into when there are multiple candidates: 1 (default) = merge into the neighbor with the biggest area, 2 = merge into the neighbor with the biggest common boundary,
- thread configuration (TileSievePar only).
New query functions: TileSieveStep / TileSieveStepPar. Perform a single step of a sieve operation on an image channel. (Performing a single step instead of running the operation until there are no more areas to merge is sometimes useful as an intermediate step in bigger computations.) New tile transform: Sieve. Performs a sieve operation on an image channel (runs until there are no more areas to merge). (A note on performance: the Sieve transform could be slower on some images than an analogous operation in QGIS / GDAL, however the operation in GDAL frequently leaves areas that are smaller than the specified threshold not merged into their bigger neighbors. It seems to us that the operation implemented in GDAL might be somewhat similar to our single-step sieve, and perhaps even smaller in scope in that the merges seem to proceed in a subset of directions instead of in all directions.) New numeric select: Unique. Selects values based on how many times they occur in the table: - duplicate - for each value, selects all records except one,
- non-unique (occurs more than once) - selects records for values that occur more than once,
- unique (occurs once) - selects records for values that occur only once.
New numeric vector select: Unique. Similar to numeric select. New binary select: Unique. Similar to numeric select. New date select: Unique. Similar to numeric select with an extra option: Ignore time (default: on). New text select: Unique. Similar to numeric select with extra options: Ignore case (default: on), Ignore whitespace at start and end (default: on). New uuid select: Unique. Similar to numeric select. New numeric transform: Random. Produces random values between specified minimum and maximum, with option: Whole numbers (default: on). New uuid transform: Random. Produces random values. New query aggregate function: Percentile. Returns the value for the specified percentile. Percentile 0 returns the minimum value, percentile 100 returns the maximum value, percentile 50 returns the median value. Works for values of any type. New numeric select: Statistic. Selects values based on how large or small they are relative to other values in the table: - maximum - selects records with the maximum value,
- minimum - selects records with the minimum value,
- median - selects records with the median value,
- top - selects the specified number of records with the largest values,
- bottom - selects the specified number of records with the smallest values,
- top percent - selects records with the values larger than the specified percentile, the bound can be included or excluded,
- bottom percent - selects records with the values smaller than the specified percentile, the bound can be included or excluded.
New numeric vector select: Statistic. Similar to numeric select. New date select: Statistic. Similar to numeric select with an extra option: Ignore time (default: on). New text select: Statistic. Similar to numeric select with extra options: Ignore case (default: on), Ignore whitespace at start and end (default: on). Layouts support min / max scale for map layers. Switching the source window in the Register pane maps already entered control points to the source control points by name, to preserve as much of the already entered data as possible. End of list.
|