Test 1 - write numbers / strings
Create a table and write a number of records with numeric / string values.
We use the following format for the results: <product/build> - <time in milliseconds> (<number of processed records>) - <number of records processed per second>. Best number is shown in bold.
8.0.30 - 25600 ms (10k records) - 0.39k rec/s
9.0.160 - 4200 ms (500k records) - 119k rec/s
9.0.160 SQL - 3310 ms (500k records) - 151k rec/s
126.96.36.199 - 4200 ms (500k records) - 119k rec/s
First, Radian is clearly much faster at this than 8. The standard number of records we used for Radian was 500k and we had to reduce that number 50x to make the test for 8 manageable. The main reason for the performance difference is different internal data structures, which are way faster in Radian, with the difference between the object models coming second.
The performance in both builds of Radian is the same, changes to the object model in 188.8.131.52 were in other areas.
Inserting records using a script is slightly slower than doing the same using a query. This is fine for a simple script, although in general, given enough time and code, a script should outperform a query.
We don't include a SQL version of the test for 8, because the query engine in 8 lacks a function that would produce a virtual table filled with a sequence of numbers.
Test 2 - read numbers / strings
Read the table created by test 1, parse values.
8.0.30 - 2060 ms (10k records) - 4.9k rec/s
8.0.30 SQL - 41 ms (10k records) - 244k rec/s
9.0.160 - 2530 ms (500k records) - 198k rec/s
9.0.160 SQL - 2090 ms (500k records) - 239k rec/s
184.108.40.206 - 1820 ms (500k records) - 275k rec/s
This time the query engine in 8 can perform the test. Using SQL in 8 is clearly much faster than using scripts. For this particular query, the performance of the query engine in 8 is on par with that of the query engine in Radian. (In very simple scenarios like this one and / or on very small data sets, the query engine in 8 can even outperform Radian due to being less sophisticated! The scripts, however, can not.)
In 9.0.160, reading records using a script continues to be slightly slower than doing the same using a query. But in 220.127.116.11, the script outperformed the query, thanks to the implemented changes.
Test 3 - write geoms
Create a table and fill it with geometry values.
8.0.30 - 9400 ms (1k records) - 0.1k rec/s
9.0.160 - 9900 ms (500k records) - 50.5k rec/s
18.104.22.168 - 9900 ms (500k records) - 50.5k rec/s
This test behaves more or less like test 1. 8 is again the slowest. The two builds of Radian have the same performance, because changes to the object model in 22.214.171.124 were in other areas. The test prepares data for the next test.
Test 4 - read geoms
Read the table created by test 3, parse geometry values.
8.0.30 - 2930 ms (1k records) - 0.34k rec/s
9.0.160 - 3430 ms (500k records) - 146k rec/s
9.0.160 SQL - 81000 ms (500k records) - 6.2k rec/s
126.96.36.199 - 2740 ms (500k records) - 182k rec/s
8 is slowest, obviously. The standard test for Radian is 500x the size of that for 8. That's the effect of the many accumulated changes.
The query engine is significantly slower than the script at this particular task - which was the purpose of the test. Some tasks are much better done using a script - if the query analyzed geometry using a script function, the performance would have been much better.
188.8.131.52 improved the performance of the script by about 25%. The effect comes from optimizations in sequences.
Test 5 - read geoms complex
Read a separate table of geometry values, transforming each value using a sequence of spatial functions.
Tests for Radian include both a single-threaded and a multi-threaded version.
8.0.30 - 18650 ms (1k records) - 53.6 rec/s
8.0.30 SQL - 17300 ms (1k records) - 57.8 rec/s
184.108.40.206 - 17400 ms (1k records) - 57.5 rec/s
220.127.116.11 (4x) - 18100 ms (1k records) - 55.2 rec/s
9.0.160 SQL - 17500 ms (1k records) - 57.1 rec/s
9.0.160 SQL (4x) - 5475 ms (1k records) - 182.6 rec/s
18.104.22.168 - 17400 ms (1k records) - 57.5 rec/s
22.214.171.124 (4x) - 5450 ms (1k records) - 183.5 rec/s
The performance of 8 is reasonable. The number of calls into the object is small and this helps a lot. The performance of the query engine is slightly better than the performance of the script.
The performance of 126.96.36.199 is about the same as that of 8. The vast majority of the time is spent in geometry functions which have been explicitly selected to be more or less the same for 8 and Radian (for now), so the same performance from a script is expected. Reworking the script to use multiple threads does nothing. In fact, the script slows down a bit due to double locking - first in a script and then in the object model. The single-threaded query performs about the same as the single-threaded script. Reworking the query to use multiple threads improves the performance by quite a bit - as expected.
The performance of the single-threaded script in 188.8.131.52 is the same as in 184.108.40.206. But adding multiple threads now provides an immediate performance boost - about the same as for the query. The performance boost from using 4 threads for both the query and the script is about 3.2 rather than 4, because there is some overhead. The overhead could be reduced by making the portion of the code shared between threads more sophisticated (this is easier done for a script rather than for a query due to the script having more control over what gets executed and how).
Test 6 - read pixels
Read all pixels in an image.
8.0.30 - 18020 ms (88.5k pixels) - 0.5k pix/s
8.0.30 SQL - 113 ms (88.5k pixels) - 783k pix/s
9.0.160 - 580 ms (29.1m pixels) - 50,172k pix/s
9.0.160 SQL - 19770 ms (29.1m pixels) - 1,472k pix/s
220.127.116.11 - 580 ms (29.1m pixels) - 50,172k pix/s
Radian outperforms 8 by a huge margin. The query engine in 8 is much faster than a script, but its throughput is very far from what is possible in Radian.
This is one more test which is performed much better using a script rather than a query. The performance of a pure SQL solution in Radian is still 2x faster than the performance of a similar query in 8, but the query really should have used a script function in the middle, because the script is more than 30 times faster.
There is no difference between the performance of the script in 9.0.160 and in 18.104.22.168.