In this post we are looking at the basic performance of the different sub-classing options in Django that have been discussed in previous posts. The three options are listed again below.

The three options

The three options that were discussed in previous posts and are now compared from a performance point of view are:

Option 1 – A single roads table

A single table called “ROADSA” contains the freeway and tollroad attributes as well as the common road attributes. Unused attributes for freeways or tollroads are set to NULL or similar values.

Option 2 – A table for each type of road

A ‘FREEWAYB’ table and a ‘TOLLROADB’ table are defined, each of which contains both shared road attributes and some attributes specific to the type of road.

Option 3 – Shared base table

The “road” information is stored in a ‘ROADC’ table, while the information about the freeway aspect of freeways is included in a ‘FREEWAYC’ table and the tollroad-specific aspects of tollroads are stored in a table called ‘TOLLROADC’.

Performance testing

First, I need to state that the testing reported here is neither thorough nor exhaustive. Its aim is to give some hints as to “default” performance for the different sub-classing models – but no attempt has been made to optimise performance. For example, the creation of instances has been done by reading a simple CSV file and creating the instances one by one, rather than using any sort of bulk import method.

All testing is done using SQLite and no transaction control is undertaken. As a result, all operations include a “BEGIN” statement. In some cases, this will double the number of queries reported.

Creation of roads

For each of the three options, 100 roads were created in a simple loop which created an object and saved it to the database. The tests were run three times for each option. Individual times are reported along with the averages. The query times reported are taken from the Django “connection” object imported from django.db.The total times for each test are found using the Python timeit module.

For Option 1, creating a new road requires two statements: a BEGIN statement and an INSERT into the ROADA table.

For Option 2, creating a new road requires requires two statements: a BEGIN statement and an INSERT into either the FREEWAYB or TOLLROADB table.

For Option 3, creating a new road requires four statements: a BEGIN statement; an INSERT into the ROADC table; an INSERT into and an UPDATE of either the FREEWAYC or TOLLROADC table. Note that the UPDATE statement is redundant in these tests.

The results are shown in the table below and the fastest option is shown in pink. However, it is worth noting that the times taken are very similar for all three options – the variation is less than 2.5%. Furthermore, the time spent in queries is longest for the fastest option, reflecting the fact that this simplest of options requires the least work within Django.

Selection of roads

Six selection operations are performed in this series of tests, based on the dataset imported in the creation stage. Each of the six selections returns different but consistent numbers of instances and the numbers selected will be the same for each dataset.

The six selection criteria fetch instances where:

1. Name contains “ave” (ignoring case)
2. SpeedLimit < 80
3. UsageRestrictions contains “ton” (ignoring case)
4. IsTollroad = True
5. Criteria 1, 2 and 3
6. Criteria 1, 2 and 4

Note that Criteria 1 and 2 are shared attributes, Criterion 3 is a Freeway attribute and Criterion 4 is a Tollroad attribute. This mix of selections is made easier or harder by the different modelling option used.

The results are shown in the table below and the fastest option is shown in pink. However, it is worth noting in this case, that the accuracy of the times limits the number of significant figures available, but the slowest option (Option 1) takes 50% longer than the fastest one. Once again, it is interesting to note that the time spent in queries is longest for the first option.

Option 2 requires more queries because in some of the selections there is a need to select from both Freeways and Tollroads.

Editing of roads

The editing operations involved using the same selection tests as shown above and then editing each of the roads selected in each set. The edits were chosen so that they did not change whether roads would be included by subsequent selections.

The attributes edited in each group were:

1. Name
2. SpeedLimit
3. UsageRestrictions
4. eTagType
5. Name and SpeedLimit
6. Name, SpeedLimit and HasTollBooth

The results are shown in the table below and the fastest option is shown in pink. Options 1 and 3 each take about 12% longer than Option 2. The figures for the third test of Option 3 seem significantly different from the two previous tests in both overall time and query time. If this sample is ignored, the results for Option 1 would be worst again, although only by a small margin.

Note that the number of queries varies between the different options, with Option 1 requiring the most and Option 3 the fewest.

Deletion of roads

Deletion involved deleting all of the Road objects in the database. This is simplest for Option 1, where everything is stored in one table. Option 2 has simple storage in two tables and takes twice as many queries as a result. Option 3 is more complex and requires two queries to find the ids of the Freeways and the Tollroads and another two queries to delete those ids from the Freeway and Tollroad tables. The database then cleans up the Road table itself. Obviously this is considerably more work and for larger numbers of objects could become much slower than the simple statements used by other options.

These results reflect only very small numbers of objects. What happens to performance if we have many more objects? Could some options perform relatively better or worse with larger numbers of objects?

 

 

Further performance testing

The aim of this performance testing is to compare the different sub-classing options when larger numbers of rows are involved. Testing was based on having 500,000 roads stored in the database for each of the three options – to see whether there was any discernible difference in behaviour. After inserting the three different types of roads, the SQLite database being used had 2 million rows in it.

Once again, I need to state that the testing reported here is neither thorough nor exhaustive. Its aim is to give some hints as to “default” performance for the different sub-classing models – but little attempt has been made to optimise performance.

All testing is done using SQLite and no transaction control is undertaken (except where noted, for example, in one case of road creation detailed below). As a result, all operations include a “BEGIN” statement. In some cases, this will double the number of queries reported.

In the tables below, there are occasional numbers in red text. These are numbers which are unexpectedly high for some reason.

Creation of roads

To import the large numbers of rows required, a CSV file with about 5,000 roads was imported 100 times into the SQLite database. As a result, 100 copies of each road exist for each option, but they are distinguished by different unique keys.

The Django bulk_create() function was used to insert the rows for Option 1 (RoadA) and Option 2 (RoadB) roads, but bulk_create() does not work for leaf models in multi-table inheritance, so for Option 3 (RoadC) the inserts were put inside a “with transaction.atomic()”  block to minimise transaction handling and maximise performance.

For each of the three options, 5,000 roads were created using bulk_create() (or in a single transaction for RoadC objects). The query times were taken from the Django “connection” object imported from django.db. The total times for each test are found using the Python timeit module.

The results for importing 5,000 roads for each option are shown in the table below and the fastest option is shown in pink. However, it is worth noting that the times for the two options that used the bulk_create() method are very similar – the variation is less than 1%. The third option is much slower, but performing all of the inserts in a single transaction is still vastly faster than the default auto commit method reported in the last newsletter.

One other point of interest is that, for each option, the difference between the average time for the first five lots of 5,000 roads (when the database is mostly empty) is only about 10% shorter than the time taken for inserting the last five lots of 5,000 roads – when there are almost 500,000 roads in the database. Even on a relatively cheap laptop, half a million rows is no longer very many.

Quite frankly, the performance for this sort of bulk insert is nothing short of amazing. Using the method reported in the last newsletter, the average time to create 5,000 roads is:

• 607 seconds for Option 1,
• 612 seconds for Option 2 and
• 622 seconds for Option 3.

All very similar to each other, but very slow. In this example, the bulk_create() used for options 1 and 2 takes less than 0.1% of the time of the default create()/save() method. For option 3, performing the operations in one transaction takes only 0.5% of the time using the default auto commit method.

Selection of roads

Six selection operations were performed in this series of tests, based on the dataset imported in the creation stage. Each of the six selections returned different but consistent numbers of instances, and the numbers selected were the same for each dataset.

The six selection criteria fetch instances where:

1. Name contains “ave” (ignoring case)
2. SpeedLimit < 80
3. UsageRestrictions contains “ton” (ignoring case)
4. IsTollroad = True
5. Criteria 1, 2 and 3
6. Maintainer contains “own” plus Criteria 2 and 4

Note that Criteria 1 and 2 are shared attributes, Criterion 3 is a Freeway attribute and Criterion 4 is a Tollroad attribute. This mix of selections is made easier or harder by the different modelling option used.

To make sure that times are significant enough to be compared, the tests were run ten times each – so almost 11,000,000 rows were fetched in the times quoted. The results are shown in the table below and the fastest option is shown in pink.

Once again, the performance is quite stunning. True, nothing is being done with the rows that have been fetched, but almost 11 million rows are being fetched in the reported time for each option, resulting in a fetch rate of:

• 84,000 rows per second for Option 1,
• 127,000 rows per second for Option 2 and
• 122,000 rows per second for Option 3.

With such large numbers of rows to fetch, it is no surprise that the memory used by the test program reaches about 1GB.

Editing of roads

The editing operations involved using the same selection tests as shown above (though running them only once), and then editing 200 of the roads selected in each set. The edits were chosen so that they did not change whether roads would be included by subsequent selections.

The attributes edited in each group were:

1. Name
2. SpeedLimit
3. UsageRestrictions
4. eTagType
5. Name and SpeedLimit
6. Name, SpeedLimit and HasTollBooth

The results are shown in the table below and the fastest option is shown in pink. If one wished to separate the select/fetch and editing components of this test, the figures in the table above can be divided by 10 to give an indication of the time taken for selection and fetching. The remainder of the time given in the table below is taken by editing.

In summary, each test fetched about 1,100,000 objects and edited 1,200 of them in the time reported. The default transaction handling means that each edit takes place in its own transaction. If “with transaction.atomic()” is wrapped around the entire process, the editing process takes about 1 second.