Auditing Performance Tests - fieldenms/tg GitHub Wiki

Test conditions

Let's use a new entity, TestActivity, whose structure resembles that of WorkActivity, and which is audited. TestActivity has only persistent properties. There are 67 of them and all are audited.

Variations on test data

• TestData1: PK on id.
• TestData2: 1 + unique index for key members.
  • Audit entity: UNIQUE INDEX on (auditedVersion, auditedEntity).
  • Audit-prop: UNIQUE INDEX on (auditEntity, property).
• TestData3: 2 + index for AbstractAuditProp.auditEntity.
• TestData4: 2 + indices for audit-entity.
• 4.1: 4 without the index on AbstractAuditEntity.auditEntity. This was used to test a certain hypothesis, which involved a comparison with variation 4.
• TestData5: 3 + 4
• TestData6: 2 + FK on entity-typed properties of the audit-entity.
• TestData7: FK on AbstractAuditProp.auditEntity.
• TestData8: 6 + 7
• TestData9: 5 + 8
• TestData10: audit-entity with 2 partitions on auditDate:
  • Partition 1: 1071626 records.
  • Partition 2: 46408 records. non-aligned indices for audit-entity; indices for audit-prop entity.
• TestData11: TestData10, but aligned indices for audit-entity.
• TestData12:
  • Audit-entity with 2 partitions on auditDate:
    • Partition 1: 1071626 records.
    • Partition 2: 46408 records.
    • non-aligned indices.
  • Audit-prop with a partition for each property.
    • non-aligned index on (auditEntity) in audit-prop entity.
• TestData13:
  • audit-prop entity with a partition for each property;
  • non-aligned index on (auditEntity) in audit-prop entity.
• TestData14:
  • TestData5;
  • audit-entity with a partition for each year (2010-2024)

These are referred to as TestDataN with N varying.

Variations on audit-entity configurations

AuditConfig0:

• 1 audit-entity.
• Audit-entity 1 has 1,118,030 records.

AuditConfig0a:

• 1 audit-entity.
• Audit-entity 1 has ~10 million records.

AuditConfig1:

• 2 audit-entities with change history:
  • Add property prop1 : String.
• Audit-entity 2 has 1,118,030 records.
• Audit-entity 1 has 1,118,030 records.

AuditConfig2:

• 10 audit-entities with change history:
  1. Add b1 : boolean, add b2 : boolean.
  2. Add person1 : Person.
  3. Remove b2 : boolean.
  4. Add string1 : String, add date1 : Date.
  5. Add rotable1 : Rotable, person2: Person.
  6. Remove person1 : Person.
  7. Add string2 : String, date2 : Date.
  8. Add service1 : Service.
  9. Remove string1 : String.
• Each audit-entity has ~111k records.

These are referred to as AuditConfigN with N varying.

If no configuration is explicitly mentioned, then AuditConfig0 should be assumed.

Insertion tests

Test models

• Batch model: operations are performed in batches over n iterations. A batch involves running k threads, each performing one operation. E.g., if n = 100, k = 10, then each of the 10 threads saves a new entity, and a batch completes when all threads complete.

• Queue model: a server-client simulation. There is a server with a thread pool of size k. The main thread simulates concurrent users by generating n concurrent tasks and scheduling them for execution. Such groups of tasks are generated every m seconds. Values for n and m are sampled randomly.

  For example:

  1. 3 tasks arrive after 0.5 seconds.
  2. 7 tasks arrive after 2 seconds.

  The simulation ends after a certain number of iterations (i.e., generations).

Test categories

• Test A: saving a new entity
• Test B: saving a modified entity

Interpreting results

Note: 1E9 in nanoseconds = 1 second.

Interpreting results:

• Mean [x,y] represents an arithmetical mean on a subrange of values from x% to y%. The values in a range are ordered chronologically by their recorded time. This information can be used to estimate the effects of database operations that happened prior to the range start. For exampe, the first value in the range [75,100] is recorded after 75% of operations were performed.

Format of test titles: TD{n}_{m}{suffix}?

• TD{n} is the test data variant, where {n} is a variable.
• {m} identifies the variant of auditing:
  • 1: auditing is disabled.
  • 2: auditing is enabled.
  • 3: auditing is enabled without audit-props.
• {suffix}? is an optional suffix:
  • _vt: a test is using virtual threads.
  • _vt_c: a test is using virtual threads and a concurrent version of the meta-property factory (see https://github.com/fieldenms/tg/commit/a5214f2dcb02e6d02fa141ddf4695f29d0b3ea80).

Test A, Batch model

The following table summarises the results of tests in category A. Times are given in seconds.

Title	Threads	Tasks	Warmup Iters	Iters	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
TD2_1	10	10	30	100	0.28688	0.06790	0.20001	0.56952	0.22879	0.23922	0.26035	0.31787	0.38722	0.31509	0.31509	0.31509	0.28688
TD2_1	25	25	30	100	0.73867	0.16826	0.37097	1.73597	0.57359	0.61944	0.68875	0.83172	0.98813	0.59423	0.59423	0.59423	0.73867
TD2_1	50	50	10	40	1.14478	0.23861	0.44465	1.88860	0.83061	0.96533	1.14153	1.32059	1.45462	0.95782	0.95782	0.95782	1.14478
TD2_1	100	100	10	30	1.60036	0.53333	0.42990	3.21784	0.85204	1.12232	1.63244	2.02664	2.23987	0.61623	0.61623	0.61623	1.60036
TD2_1	1	1	30	100	0.16112	0.03921	0.11085	0.26826	0.12114	0.13161	0.14282	0.19376	0.22759	0.16055	0.16055	0.16055	0.16112
TD2_1_vt_c	10	10	30	100	0.32619	0.07905	0.16858	0.57899	0.23803	0.26296	0.30933	0.38002	0.44432	0.34726	0.34726	0.34726	0.32619
TD2_2	10	10	30	100	2.36215	0.22345	1.41063	3.17053	2.10375	2.21166	2.37907	2.51399	2.60570	2.11482	2.11482	2.11482	2.36215
TD1_2	10	10	30	100	2.34165	0.22923	1.51283	2.88804	2.03568	2.16540	2.40063	2.51221	2.59121	2.35229	2.35229	2.35229	2.34165
TD2_2	25	25	20	80	3.61950	0.26193	1.72217	4.18076	3.37387	3.51762	3.64727	3.76655	3.88753	3.20549	3.20549	3.20549	3.61950
TD2_2	50	50	10	40	5.32332	1.43829	2.29621	9.81939	3.93199	4.16907	4.66365	6.81549	7.29864	4.04481	4.04481	4.04481	5.32332
TD2_2	100	100	10	30	7.95757	3.12549	2.18205	13.16977	3.97068	4.30568	8.07762	11.46172	12.10003	4.10329	4.10329	4.10329	7.95757
TD2_2	1	1	30	100	0.50852	0.10945	0.40022	0.81107	0.41373	0.42971	0.45203	0.57366	0.70481	0.48073	0.48073	0.48073	0.50852
TD2_2_vt	10	10	30	100	2.47672	0.41191	0.96604	3.26253	1.92406	2.22002	2.54456	2.79492	2.94500	2.93718	2.93718	2.93718	2.47672
TD2_2_vt	50	50	15	40	4.97231	3.44457	0.89725	14.15482	1.80197	2.21589	3.70587	6.76283	11.17395	3.89528	3.89528	3.89528	4.97231
TD2_2_vt	25	25	20	80	4.23331	1.69327	0.86676	7.63850	1.96726	2.81508	4.22246	5.69017	6.52687	4.97275	4.97275	4.97275	4.23331
TD2_2_vt_c	10	10	30	100	2.49965	0.40549	0.94536	3.19572	1.94852	2.24538	2.57026	2.81630	2.97127	2.59161	2.59161	2.59161	2.49965
TD2_3	10	10	30	100	0.81429	0.18245	0.57962	1.57477	0.65514	0.69057	0.74268	0.89488	1.10182	0.72717	0.72717	0.72717	0.81429
TD2_3	25	25	20	80	2.05373	0.26340	1.36312	3.25692	1.77710	1.87048	2.00373	2.18410	2.40306	2.60058	2.60058	2.60058	2.05373
TD2_3	50	50	10	40	3.43335	0.89213	1.93899	5.78285	2.35051	2.58625	3.37910	4.17824	4.57786	3.29293	3.29293	3.29293	3.43335
TD2_3	100	100	10	30	5.46097	2.03747	1.87716	9.30651	2.50117	3.35214	5.50278	7.28700	7.90574	2.22647	2.22647	2.22647	5.46097
TD2_3	1	1	30	100	0.36391	0.10063	0.26340	0.61026	0.28282	0.29233	0.31825	0.43407	0.55913	0.33696	0.33696	0.33696	0.36391

Test TD1_2. With 100 threads some threads have to wait longer. The measured times are evenly distributed between threads in the range [2s, 13s].

TestData1. This test data variation does not include an index for key members of audit types, which is expected to speed up insertions as there is no index to maintain. However, the current implementation of PersistentEntitySaver guards against saving a duplicate entity by issuing a query that searches by the key of the entity being saved. Absence of an index for key members thus leads to worse performance. Therefore, the mentioned guard code in PersistentEntitySaver was temporarily removed.

Test TD1_2 shows that with this variation throughput is a bit higher than in TD2_2, but not significantly.

Virtual threads.

• CPU is doing less work.
• Measured times are very similar to those measured without virtual threads.

Variations without audit-props. Tests TD*_3 show that auditing of changed properties has a significant effect on performance, as the workload is larger.

Test A, Queue Model

The following table summarises the results of tests in category A. Times are given in seconds.

Columns:

• Duration -- time to perform a save operation.
• Response -- time to process a task; starts when a task arrives, and ends when the coresponding save operation completes.
• Delay -- time period between the arrival of one task group and the next; in milliseconds.

Title	Threads	Warmup Iters	Iters	Delay	Group Size	Duration Mean	Duration Std	Duration Min	Duration Max	Duration 10th pctl	Duration 25th pctl	Duration 50th pctl	Duration 75th pctl	Duration 90th pctl	Duration Mean [0,25]	Duration Mean [25,50]	Duration Mean [50,75]	Duration Mean [75,100]	Response Mean	Response Std	Response Min	Response Max	Response 10th pctl	Response 25th pctl	Response 50th pctl	Response 75th pctl	Response 90th pctl	Response Mean [0,25]	Response Mean [25,50]	Response Mean [50,75]	Response Mean [75,100]
TD2_1	10	300	50	random(500, 5000)	random(1, 10)	0.22245	0.06143	0.12386	0.45864	0.15308	0.17874	0.21689	0.25219	0.29321	0.19323	0.19323	0.19323	0.22245	0.24557	0.06566	0.13010	0.47749	0.16776	0.19990	0.23791	0.27448	0.32205	0.20122	0.20122	0.20122	0.24557
TD2_1	25	300	50	random(500, 5000)	random(1, 25)	0.66120	0.16543	0.16672	1.29241	0.44457	0.53565	0.66593	0.77449	0.86811	0.92109	0.92109	0.92109	0.66120	0.77783	0.20153	0.18197	1.31856	0.48657	0.62959	0.80512	0.92970	1.02085	0.92937	0.92937	0.92937	0.77783
TD2_1	10	300	50	random(500, 5000)	random(1, 25)	0.42914	0.13061	0.15249	0.93229	0.26929	0.32470	0.43496	0.49905	0.59434	0.54683	0.54683	0.54683	0.42914	0.67529	0.22107	0.16000	1.38066	0.44457	0.50355	0.61305	0.82764	1.00185	0.56960	0.56960	0.56960	0.67529
TD2_2	10	300	50	random(500, 5000)	random(1, 10)	1.88440	0.40005	0.45344	2.70279	1.31100	1.68413	1.98646	2.14418	2.31905	2.12373	2.12373	2.12373	1.88440	1.99386	0.46462	0.46073	3.33262	1.46550	1.74854	2.04744	2.23037	2.44543	2.12716	2.12716	2.12716	1.99386
TD2_2	10	300	50	random(500, 5000)	random(1, 25)	2.09594	0.47421	0.84967	4.37532	1.48202	1.75287	2.11979	2.41381	2.65481	2.84475	2.84475	2.84475	2.09594	6.06466	3.76538	1.26641	15.99844	2.23294	2.70287	4.73117	9.13869	12.04477	2.85323	2.85323	2.85323	6.06466
TD2_2	4	300	50	random(500, 5000)	random(1, 25)	1.21128	0.22337	0.65083	2.03800	0.93413	1.06602	1.20309	1.34439	1.50039	1.49183	1.49183	1.49183	1.21128	44.73766	25.98703	1.32049	89.95063	10.91760	22.58453	39.67883	70.53757	82.36500	1.49926	1.49926	1.49926	44.73766
TD2_2	8	300	50	random(500, 5000)	random(1, 25)	1.83544	0.31807	0.97293	2.66672	1.44165	1.59609	1.81607	2.08172	2.25196	0.97293	0.97293	0.97293	1.83544	9.06915	5.48382	0.97556	22.93415	2.63081	4.96301	7.69721	12.75751	17.97806	0.97556	0.97556	0.97556	9.06915

With auditing enabled duration time is 5-10 times larger, response time is 10 times larger.

---

Below is another table containing results of performance tests peformed on a different date than those in the table above. The two tables should be viewed independently, their contents should not be compared.

Title	Threads	Warmup Iters	Iters	Delay	Group Size	Duration Mean	Duration Std	Duration Min	Duration Max	Duration 10th pctl	Duration 25th pctl	Duration 50th pctl	Duration 75th pctl	Duration 90th pctl	Duration Mean [0,25]	Duration Mean [25,50]	Duration Mean [50,75]	Duration Mean [75,100]	Response Mean	Response Std	Response Min	Response Max	Response 10th pctl	Response 25th pctl	Response 50th pctl	Response 75th pctl	Response 90th pctl	Response Mean [0,25]	Response Mean [25,50]	Response Mean [50,75]	Response Mean [75,100]
TD2_1	25	300	50	random(500, 5000)	random(1, 25)	0.41443	0.15398	0.13295	0.92271	0.21465	0.28878	0.42168	0.51676	0.60475	0.57616	0.57616	0.57616	0.41443	0.47025	0.17404	0.14517	0.99374	0.24630	0.32592	0.48289	0.58564	0.68407	0.58496	0.58496	0.58496	0.47025
TD2_2	25	300	50	random(500, 5000)	random(1, 25)	1.91310	0.68265	0.43501	3.34162	0.93182	1.43091	1.85755	2.57080	2.77023	1.34630	1.34630	1.34630	1.91310	2.09461	0.76708	0.44173	4.40445	0.97894	1.51892	2.04786	2.72568	3.01338	1.35675	1.35675	1.35675	2.09461
TD3_2	25	300	50	random(500, 5000)	random(1, 25)	2.01663	0.63461	0.52473	3.62835	1.02860	1.54072	2.17950	2.49150	2.70922	2.34737	2.34737	2.34737	2.01663	2.21341	0.76424	0.53142	4.93130	1.08658	1.66090	2.33974	2.63847	3.03559	2.35880	2.35880	2.35880	2.21341
TD41_2	25	300	50	random(500, 5000)	random(1, 25)	2.28484	0.88349	0.38756	4.38407	1.06168	1.55508	2.40170	2.98838	3.40053	0.85788	0.85788	0.85788	2.28484	2.71752	1.18123	0.38957	7.86732	1.08583	1.72809	2.83449	3.45088	4.16601	0.85994	0.85994	0.85994	2.71752
TD4_2	25	300	50	random(500, 5000)	random(1, 25)	2.09603	0.68250	0.43460	3.79040	1.19129	1.56180	2.13529	2.65858	2.93415	2.53214	2.53214	2.53214	2.09603	2.42586	0.88742	0.44900	6.31004	1.26631	1.74869	2.46637	2.98985	3.54995	2.54299	2.54299	2.54299	2.42586
TD5_2	25	300	50	random(500, 5000)	random(1, 25)	1.99472	0.65685	0.46507	4.84908	1.08003	1.50303	2.04433	2.43371	2.75323	2.03524	2.03524	2.03524	1.99472	2.19400	0.80099	0.47269	5.03370	1.14301	1.65556	2.19279	2.60445	3.27640	2.03834	2.03834	2.03834	2.19400
TD6_2	25	300	50	random(500, 5000)	random(1, 25)	1.85433	0.55401	0.42814	2.94196	1.03667	1.47993	1.95208	2.19146	2.60441	2.28251	2.28251	2.28251	1.85433	1.96692	0.60182	0.43533	3.46068	1.07631	1.56610	2.03751	2.31270	2.79917	2.29016	2.29016	2.29016	1.96692
TD7_2	25	300	50	random(500, 5000)	random(1, 25)	2.00165	0.65993	0.47691	3.67193	1.04696	1.52027	2.02787	2.54816	2.86524	0.55713	0.55713	0.55713	2.00165	2.16114	0.76931	0.48356	4.46787	1.07116	1.59833	2.15832	2.71775	3.21068	0.56932	0.56932	0.56932	2.16114
TD9_2	25	300	50	random(500, 5000)	random(1, 25)	2.11021	0.68284	0.43815	4.62987	1.17165	1.66020	2.13156	2.56238	3.00604	1.89079	1.89079	1.89079	2.11021	2.60402	1.14086	0.44524	7.77706	1.23651	1.88714	2.49552	3.13353	3.94732	1.90114	1.90114	1.90114	2.60402
TD10_2	25	300	50	random(500, 5000)	random(1, 25)	1.97325	0.60379	0.44291	3.13974	1.13632	1.47864	2.04304	2.47729	2.75245	2.13765	2.13765	2.13765	1.97325	2.11674	0.61392	0.44898	3.21201	1.22399	1.64216	2.17461	2.66256	2.87280	2.14017	2.14017	2.14017	2.11674
TD12_2	25	300	50	random(500, 5000)	random(1, 25)	1.90106	0.71306	0.44055	3.38301	0.96863	1.26036	1.89072	2.52243	2.81731	3.29192	3.29192	3.29192	1.90106	2.04752	0.78403	0.44748	4.53650	1.00037	1.34646	2.06364	2.74412	3.00817	3.29913	3.29913	3.29913	2.04752

• Enabling auditing increases duration by a factor of 5.
• Performance of TestData3 is equivalent to that of TestData2.

Miscellaneous:

Title	Threads	Warmup Iters	Iters	Delay	Group Size	Duration Mean	Duration Std	Duration Min	Duration Max	Duration 10th pctl	Duration 25th pctl	Duration 50th pctl	Duration 75th pctl	Duration 90th pctl	Duration Mean [0,25]	Duration Mean [25,50]	Duration Mean [50,75]	Duration Mean [75,100]	Response Mean	Response Std	Response Min	Response Max	Response 10th pctl	Response 25th pctl	Response 50th pctl	Response 75th pctl	Response 90th pctl	Response Mean [0,25]	Response Mean [25,50]	Response Mean [50,75]	Response Mean [75,100]
TD2_1	25	300	50	random(500, 5000)	random(1, 25)	0.41443	0.15398	0.13295	0.92271	0.21465	0.28878	0.42168	0.51676	0.60475	0.57616	0.57616	0.57616	0.41443	0.47025	0.17404	0.14517	0.99374	0.24630	0.32592	0.48289	0.58564	0.68407	0.58496	0.58496	0.58496	0.47025
TD2_2	10	300	50	random(500, 5000)	random(1, 10)	0.99655	0.27129	0.35376	1.71700	0.60858	0.85308	1.02007	1.16612	1.33236	1.71700	1.71700	1.71700	0.99655	1.02157	0.28340	0.35562	1.72596	0.61095	0.86870	1.03669	1.19727	1.37129	1.72596	1.72596	1.72596	1.02157
TD2_2	50	300	50	random(500, 5000)	random(1, 50)	4.81148	1.47248	0.45411	8.77738	2.61142	3.85745	4.86124	5.79147	6.65562	2.40341	2.40341	2.40341	4.81148	13.46868	7.32214	0.46153	29.25869	2.77173	6.22160	15.28299	19.61109	21.79947	2.41181	2.41181	2.41181	13.46868
TD2_2	1	100	200	random(500, 5000)	random(1, 1)	0.50370	0.09033	0.41386	0.76917	0.43817	0.44707	0.46527	0.51296	0.64976	0.60345	0.60345	0.60345	0.50370	0.51166	0.09053	0.41684	0.77618	0.44549	0.45504	0.47336	0.52061	0.65707	0.61129	0.61129	0.61129	0.51166
TD41_2	10	300	50	random(500, 5000)	random(1, 10)	1.09173	0.40210	0.36390	1.98150	0.58790	0.73259	1.09350	1.33109	1.68478	0.46021	0.46021	0.46021	1.09173	1.11283	0.40618	0.36581	1.99977	0.60436	0.75044	1.10939	1.37142	1.70833	0.46242	0.46242	0.46242	1.11283
TD4_2	10	300	50	random(500, 5000)	random(1, 10)	0.95484	0.30179	0.37919	2.15183	0.56430	0.71951	0.93805	1.16928	1.30489	0.45453	0.45453	0.45453	0.95484	0.97277	0.30994	0.38095	2.15551	0.56812	0.74069	0.95137	1.19298	1.32636	0.45635	0.45635	0.45635	0.97277
TD4_2	1	100	200	random(500, 5000)	random(1, 1)	0.50905	0.08825	0.41432	0.76518	0.44033	0.45124	0.46439	0.55265	0.64619	0.52420	0.52420	0.52420	0.50905	0.51680	0.08879	0.41686	0.77875	0.44743	0.45908	0.47286	0.56068	0.65431	0.53483	0.53483	0.53483	0.51680
TD5_2	50	300	50	random(500, 5000)	random(1, 25)	2.06247	1.06431	0.46379	6.61832	1.03743	1.48971	1.86747	2.32781	3.15838	0.78325	0.78325	0.78325	2.06247	2.12976	1.11385	0.47723	6.83157	1.04901	1.53187	1.92210	2.41921	3.19127	0.78713	0.78713	0.78713	2.12976
TD5_2	50	300	50	random(500, 5000)	random(1, 50)	5.13454	1.38406	1.37346	9.16762	3.23145	4.45829	5.07837	5.91884	7.01361	1.49213	1.49213	1.49213	5.13454	13.12611	6.88219	1.39676	27.75254	3.65286	6.14092	14.49635	19.11821	21.69444	1.50018	1.50018	1.50018	13.12611

Effect of table partitioning with 10 million records

Test A, TestData5, AuditConfig0a:

Title	Threads	Warmup Iters	Iters	Delay	Group Size	Duration Mean	Duration Standard deviation	Duration Min	Duration Max	Duration 10th percentile	Duration 25th percentile	Duration 50th percentile	Duration 75th percentile	Duration 90th percentile	Duration Mean [0,25]	Duration Mean [25,50]	Duration Mean [50,75]	Duration Mean [75,100]	Response Mean	Response Standard deviation	Response Min	Response Max	Response 10th percentile	Response 25th percentile	Response 50th percentile	Response 75th percentile	Response 90th percentile	Response Mean [0,25]	Response Mean [25,50]	Response Mean [50,75]	Response Mean [75,100]
TD5_2	25	300	50	random(500, 5000)	random(1, 25)	2.17271	0.81949	0.45830	3.79627	1.09352	1.46798	2.15436	2.92291	3.23622	1.26202	1.26202	1.26202	2.17271	2.35690	0.85879	0.46490	4.38794	1.20489	1.63935	2.34224	3.08095	3.49746	1.27160	1.27160	1.27160	2.35690

Test A, TestData14, AuditConfig0a:

Title	Threads	Warmup Iters	Iters	Delay	Group Size	Duration Mean	Duration Standard deviation	Duration Min	Duration Max	Duration 10th percentile	Duration 25th percentile	Duration 50th percentile	Duration 75th percentile	Duration 90th percentile	Duration Mean [0,25]	Duration Mean [25,50]	Duration Mean [50,75]	Duration Mean [75,100]	Response Mean	Response Standard deviation	Response Min	Response Max	Response 10th percentile	Response 25th percentile	Response 50th percentile	Response 75th percentile	Response 90th percentile	Response Mean [0,25]	Response Mean [25,50]	Response Mean [50,75]	Response Mean [75,100]
TD14_2	25	300	50	random(500, 5000)	random(1, 25)	2.25476	0.71224	0.46624	3.97000	1.23954	1.73767	2.31898	2.71802	3.16061	2.73501	2.73501	2.73501	2.25476	2.41668	0.84321	0.47390	6.34417	1.35266	1.82868	2.45150	2.93248	3.40554	2.74620	2.74620	2.74620	2.41668

Test results show that table partitioning does not improve performance of inserts even with 10m records.

---

Indices for audit data. It was observed that indices for columns in audit tables have insignificant effect on performance. It was hypothesised that this could be explained by too little variability in the contents of entities being saved. To test this, the size of an entity pool for each entity-typed property was increased from 100 to 1000 (such pools are used to randomly select values for entity-typed properties). It was observed that this did not have a significant effect. Therefore, the hypothesis was rejected.

Foreign Key constraints for audit data. It was observed that FKs affect performance less than indices.

Table partitioning. No significant effect on performance is observed.

Test B, Queue Model

Meaning of columns:

• Mods -- average number of modified properties for a save operation.

Title	Threads	Warmup Iters	Iters	Delay	Group Size	Mods	Duration Mean	Duration Std	Duration Min	Duration Max	Duration 10th pctl	Duration 25th pctl	Duration 50th pctl	Duration 75th pctl	Duration 90th pctl	Duration Mean [0,25]	Duration Mean [25,50]	Duration Mean [50,75]	Duration Mean [75,100]	Response Mean	Response Std	Response Min	Response Max	Response 10th pctl	Response 25th pctl	Response 50th pctl	Response 75th pctl	Response 90th pctl	Response Mean [0,25]	Response Mean [25,50]	Response Mean [50,75]	Response Mean [75,100]
TD2_1	25	300	50	random(500, 5000)	random(1, 25)	2	0.19542	0.07769	0.04718	0.63357	0.10027	0.14740	0.19155	0.22684	0.30262	0.26928	0.26928	0.26928	0.19542	0.20397	0.07790	0.06468	0.63389	0.11514	0.15849	0.19708	0.23373	0.31122	0.27139	0.27139	0.27139	0.20397
TD2_2	25	300	50	random(500, 5000)	random(1, 25)	2	0.51252	0.19105	0.09275	1.06921	0.27195	0.38137	0.49300	0.63289	0.77870	0.71103	0.71103	0.71103	0.51252	0.52069	0.19534	0.09547	1.07712	0.27438	0.38994	0.50196	0.64041	0.80719	0.71210	0.71210	0.71210	0.52069
TD2_2	25	300	50	random(500, 5000)	random(1, 25)	4	0.63192	0.25168	0.15095	1.72776	0.30463	0.44114	0.63209	0.78789	0.95915	0.55946	0.55946	0.55946	0.63192	0.64794	0.25780	0.15151	1.72935	0.30621	0.45444	0.64053	0.81259	0.98278	0.56114	0.56114	0.56114	0.64794
TD2_2	25	300	50	random(500, 5000)	random(1, 25)	8	0.77122	0.29024	0.17842	1.59877	0.38527	0.53417	0.76540	0.97632	1.15011	0.87391	0.87391	0.87391	0.77122	0.79561	0.29398	0.21085	1.60533	0.41578	0.55634	0.78595	1.00739	1.19588	0.87651	0.87651	0.87651	0.79561
TD2_3	25	300	50	random(500, 5000)	random(1, 25)	2	0.61162	0.20262	0.08127	1.35962	0.36140	0.47085	0.60470	0.74731	0.87533	0.34042	0.34042	0.34042	0.61162	0.62000	0.20351	0.08148	1.36066	0.36817	0.47925	0.61225	0.75982	0.88660	0.34171	0.34171	0.34171	0.62000
TD5_2	25	300	50	random(500, 5000)	random(1, 25)	2	0.66253	0.26927	0.07745	1.66642	0.31133	0.47329	0.66656	0.83394	0.98772	0.30307	0.30307	0.30307	0.66253	0.68160	0.28324	0.07848	1.70556	0.31981	0.48296	0.67692	0.85100	1.04868	0.30431	0.30431	0.30431	0.68160
TD6_2	25	300	50	random(500, 5000)	random(1, 25)	2	0.66494	0.26133	0.05319	1.33691	0.31571	0.47129	0.66489	0.83864	1.02091	1.09396	1.09396	1.09396	0.66494	0.68995	0.28710	0.05424	1.64538	0.32236	0.47712	0.67253	0.88316	1.09132	1.09480	1.09480	1.09480	0.68995
TD8_2	25	300	50	random(500, 5000)	random(1, 25)	2	0.62027	0.23145	0.13868	1.37268	0.33015	0.44706	0.60547	0.79090	0.93881	0.67272	0.67272	0.67272	0.62027	0.62895	0.23130	0.13949	1.37586	0.33633	0.45845	0.61798	0.80226	0.94379	0.67401	0.67401	0.67401	0.62895
TD9_2	25	300	50	random(500, 5000)	random(1, 25)	2	0.71211	0.25782	0.09382	1.57428	0.35755	0.54246	0.71262	0.88674	1.05135	0.48514	0.48514	0.48514	0.71211	0.72137	0.26118	0.09464	1.57675	0.36342	0.54668	0.72182	0.90464	1.06481	0.48621	0.48621	0.48621	0.72137
TD10_2	25	300	50	random(500, 5000)	random(1, 25)	2	0.72594	0.33382	0.10136	1.64948	0.32221	0.44754	0.71118	0.93286	1.20394	0.81929	0.81929	0.81929	0.72594	0.73224	0.33419	0.10209	1.65069	0.32606	0.45108	0.71766	0.94258	1.20721	0.82121	0.82121	0.82121	0.73224
TD12_2	25	300	50	random(500, 5000)	random(1, 25)	2	0.73690	0.30506	0.07257	1.72641	0.29954	0.48570	0.75467	0.96293	1.11247	0.86829	0.86829	0.86829	0.73690	0.74543	0.30699	0.07387	1.72930	0.30732	0.49196	0.76644	0.97489	1.12278	0.86873	0.86873	0.86873	0.74543

• Expectedly, auditing a modification event is faster than auditing a save event, as the number of created audit-prop records is significantly lower. It is around 2-4 times faster.
• Indices and FKs do not have a significant effect on performance.
• Table partitioning does not have a significant effect on performance.

Search tests

• Test A: find all audit records for an entity using only the latest audit-entity version.
• Test B: property history: find all audit records that changed a given property in a given entity.
• Test C: using a synthetic audit-entity, find all audit records for a given audited entity.
• Test D: find all audit records for an entity in a specific time period.

Test A

Find all audit records for an entity.

Columns:

• TestActivity - ID of an audited instance.
• Audits - number of audit records for the audited instance.

Measurement times are given in seconds.

Test A, TestData5, AuditConfig0, 2025-01-15:

TestActivity	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
110947998	5004	13.05209	0.14674	12.80852	13.27023	12.81573	12.93570	13.05323	13.19847	13.26416	12.80852	12.80852	12.80852	13.05209
110927212	1004	3.08603	0.04258	3.03086	3.15436	3.03099	3.03653	3.09005	3.11296	3.15257	3.10511	3.10511	3.10511	3.08603
111065032	504	1.70640	0.06856	1.62263	1.81961	1.62368	1.64547	1.70680	1.74507	1.81920	1.81961	1.81961	1.81961	1.70640
111108897	104	0.41606	0.01825	0.39469	0.44599	0.39474	0.39807	0.41406	0.43576	0.44503	0.43555	0.43555	0.43555	0.41606
111013325	54	0.24215	0.01736	0.22382	0.26981	0.22384	0.22763	0.23320	0.25895	0.26883	0.22881	0.22881	0.22881	0.24215
111167014	4	0.04711	0.0028	0.04441	0.05236	0.04445	0.04514	0.04642	0.04896	0.05209	0.04968	0.04968	0.04968	0.04711

Profiling statistics (percent of total execution time):

• EntityFetcher.getEntitiesOnPage() : 59%
  • EntityFetcher.instantiateFromContainers() : 49.6%
  • EntityFetcher.getContainers() : 9.4%

---

Test A, TestData5, AuditConfig1, 2025-01-15 (from the latest audit-entity only):

TestActivity	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
110947998	5004	12.83515	0.18116	12.60839	13.14404	12.61464	12.67323	12.81280	13.03446	13.13363	13.14404	13.14404	13.14404	12.83515
110927212	1004	2.94063	0.05942	2.88975	3.08470	2.88987	2.89321	2.93192	2.96656	3.07363	3.08470	3.08470	3.08470	2.94063
111065032	504	1.63224	0.04085	1.59277	1.71386	1.59374	1.60349	1.62297	1.64798	1.71227	1.69787	1.69787	1.69787	1.63224
111108897	104	0.40854	0.01634	0.39276	0.43806	0.39307	0.39753	0.40088	0.42749	0.43717	0.42918	0.42918	0.42918	0.40854
111013325	54	0.23196	0.01490	0.21826	0.26888	0.21858	0.22401	0.22693	0.23555	0.26655	0.23221	0.23221	0.23221	0.23196
111167014	4	0.04498	0.003	0.04139	0.05066	0.04148	0.04259	0.04453	0.04683	0.05047	0.04880	0.04880	0.04880	0.04498

---

Test A, TestData5, AuditConfig2, 2025-01-15 (from the latest audit-entity only):

TestActivity	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
110947998	5004	12.72398	0.12975	12.56427	12.99764	12.56538	12.63338	12.70428	12.80460	12.98290	12.56427	12.56427	12.56427	12.72398
110927212	1004	2.92826	0.07405	2.83316	3.05549	2.83433	2.87023	2.91936	2.97179	3.05447	3.04530	3.04530	3.04530	2.92826
111065032	504	1.60041	0.02346	1.55866	1.63846	1.55953	1.58805	1.60362	1.61519	1.63630	1.60141	1.60141	1.60141	1.60041
111108897	104	0.40471	0.01403	0.38659	0.43429	0.38700	0.39186	0.40644	0.41211	0.43216	0.40561	0.40561	0.40561	0.40471
111013325	54	0.23524	0.01905	0.22042	0.27049	0.22045	0.22113	0.22432	0.25703	0.26924	0.25792	0.25792	0.25792	0.23524
111167014	4	0.04912	0.0075	0.04541	0.07045	0.04544	0.04577	0.04685	0.04797	0.06824	0.04785	0.04785	0.04785	0.04912

---

Test A, TestData10, AuditConfig0, 2025-01-15:

TestActivity	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
110947998	5004	12.34572	0.46244	12.01246	13.60014	12.01834	12.07757	12.20992	12.36068	13.48921	13.60014	13.60014	13.60014	12.34572
110927212	1004	3.31960	0.21796	3.05435	3.69068	3.05500	3.11403	3.28208	3.53837	3.67669	3.22013	3.22013	3.22013	3.31960
111065032	504	1.80507	0.12772	1.58831	1.98985	1.59289	1.68453	1.85851	1.88080	1.98178	1.85522	1.85522	1.85522	1.80507
111108897	104	0.42140	0.06983	0.36611	0.54676	0.36655	0.37309	0.38245	0.50187	0.54366	0.37054	0.37054	0.37054	0.42140
111013325	54	0.23638	0.02990	0.20823	0.30275	0.20853	0.21604	0.22438	0.25354	0.29960	0.24765	0.24765	0.24765	0.23638
111167014	4	0.04931	0.0090	0.04265	0.06971	0.04273	0.04359	0.04631	0.05196	0.06891	0.04577	0.04577	0.04577	0.04931

---

Test A, TestData11, AuditConfig0, 2025-01-15:

TestActivity	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
110947998	5004	12.68783	0.18118	12.50435	13.09849	12.50499	12.51299	12.68630	12.75955	13.07038	12.51083	12.51083	12.51083	12.68783
110927212	1004	2.93720	0.14583	2.81464	3.33464	2.81938	2.86750	2.88958	2.94708	3.29750	3.33464	3.33464	3.33464	2.93720
111065032	504	1.58533	0.03437	1.54020	1.65003	1.54085	1.55188	1.58926	1.60485	1.64660	1.61569	1.61569	1.61569	1.58533
111108897	104	0.38525	0.0089	0.37194	0.40460	0.37273	0.38013	0.38383	0.38975	0.40354	0.38500	0.38500	0.38500	0.38525
111013325	54	0.21790	0.0087	0.20570	0.23510	0.20614	0.21182	0.21675	0.22515	0.23428	0.22456	0.22456	0.22456	0.21790
111167014	4	0.04309	0.0019	0.04162	0.04816	0.04162	0.04164	0.04263	0.04359	0.04777	0.04424	0.04424	0.04424	0.04309

---

It was observed that having an index for auditedEntity - the property that is searched on - improves performance but the effect is insignificant compared to the total execution time - both with and without an index, execution of an SQL query takes less than 0.1 seconds.

With test data variants 10, 11 & 12, results differ insignificantly. It is hypothesised that 1m records is not enough to observe effects of table partitioning.

Test B

Property history: find all audit records that changed a given property in a given entity.

Columns:

• TestActivity - ID of an audited instance.
• Audits - number of audit records for the audited instance.
• Changes - number of entries in the property history (i.e., number of audit records that modified the property).

Test B, TestData5, AuditConfig0:

TestActivity	Audits	Changes	Mean	Std Dev	Min	Max	10th Pctl	25th Pctl	50th Pctl	75th Pctl	90th Pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
110947998	5004	169	0.7957	0.2555	0.6490	1.5007	0.6494	0.6550	0.7156	0.7941	1.4356	1.5007	1.5007	1.5007	0.7957
110927212	1004	30	0.1712	0.0506	0.1440	0.3129	0.1442	0.1465	0.1581	0.1664	0.2987	0.3129	0.3129	0.3129	0.1712
111065032	504	13	0.0957	0.0423	0.0763	0.2150	0.0763	0.0791	0.0817	0.0876	0.2033	0.2150	0.2150	0.2150	0.0957
111108897	104	3	0.0520	0.0281	0.0400	0.1306	0.0400	0.0404	0.0413	0.0479	0.1233	0.1306	0.1306	0.1306	0.0520

Columns:

• TestActivity - ID of an audited instance.
• Audits - number of audit records for the audited instance.
• Changes - number of entries in the property history (i.e., number of audit records that modified the property).

An arbitrary audited property was chosen, as property modifications had been evenly distributed during the generation of audit data for testing.

It was observed that by default no index is generated for properties typed with PropertyDescriptor. It was then hypothesised that having an index for AbstractAuditProp.property may improve performance for searching property history. An experiment showed that performance indeed improves, but insignificantly, even for audited entities with a lot of audit records (e.g., 5k audit records, 10k audit-prop records).

TestData13:

TestActivity	Audits	Changes	Mean	Std Dev	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
110947998	5004	169	0.808978	0.154522	0.667377	1.205225	0.668359	0.711426	0.778385	0.841490	1.169460	1.205225	1.205225	1.205225	0.808978
110927212	1004	30	0.174773	0.012058	0.159202	0.201935	0.159697	0.166876	0.173322	0.179671	0.200262	0.201935	0.201935	0.201935	0.174773
111065032	504	13	0.090232	0.007451	0.080934	0.104998	0.080968	0.084405	0.089210	0.095343	0.104212	0.104998	0.104998	0.104998	0.090232
111108897	104	3	0.045044	0.002819	0.040394	0.050274	0.040523	0.043620	0.044892	0.046541	0.050052	0.044266	0.044266	0.044266	0.045044

Measured times are the same as without table partitioning, even though the search query was optimised to leverage partition elimination. The non-aligned index on auditEntity in the audit-prop entity has a significant effect on performance (its absence leads to a slowdown of 2.5x).

With test data variants 10, 11 & 12, results differ insignificantly. It is hypothesised that 1m records is not enough to observe effects of table partitioning.

Test C

Using a synthetic audit-entity, find all audit records for a given audited entity.

Test C, TestData5, AuditConfig0, 2025-01-15:

TestActivity	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
110947998	5004	12.72398	0.12975	12.56427	12.99764	12.56538	12.63338	12.70428	12.80460	12.98290	12.56427	12.56427	12.56427	12.72398
110927212	1004	2.92826	0.07405	2.83316	3.05549	2.83433	2.87023	2.91936	2.97179	3.05447	3.04530	3.04530	3.04530	2.92826
111065032	504	1.60041	0.02346	1.55866	1.63846	1.55953	1.58805	1.60362	1.61519	1.63630	1.60141	1.60141	1.60141	1.60041
111108897	104	0.40471	0.01403	0.38659	0.43429	0.38700	0.39186	0.40644	0.41211	0.43216	0.40561	0.40561	0.40561	0.40471
111013325	54	0.23524	0.01905	0.22042	0.27049	0.22045	0.22113	0.22432	0.25703	0.26924	0.25792	0.25792	0.25792	0.23524
111167014	4	0.04912	0.0075	0.04541	0.07045	0.04544	0.04577	0.04685	0.04797	0.06824	0.04785	0.04785	0.04785	0.04912

Profiling statistics (percent of total execution time):

• ua.com.fielden.platform.entity.query.EntityFetcher.getEntitiesOnPage() : 62.8%
  • ua.com.fielden.platform.entity.query.EntityFetcher.instantiateFromContainers() : 53.2%
  • ua.com.fielden.platform.entity.query.EntityFetcher.getContainers() : 9.6%

---

Test C, TestData5, AuditConfig1, 2025-01-15:

TestActivity	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
110947998	10008	24.42352	0.47120	23.53045	25.12009	23.56374	23.99667	24.55000	24.69352	25.08072	24.55913	24.55913	24.55913	24.42352
110927212	2008	5.53504	0.13779	5.31433	5.78735	5.32695	5.45988	5.49608	5.66167	5.77716	5.78735	5.78735	5.78735	5.53504
111065032	1008	2.97290	0.11010	2.81762	3.22509	2.82561	2.91079	2.94159	3.03349	3.20628	2.99435	2.99435	2.99435	2.97290
111108897	208	0.77300	0.05174	0.70583	0.86893	0.70839	0.73736	0.75623	0.82631	0.86553	0.75236	0.75236	0.75236	0.77300
111013325	108	0.46174	0.05884	0.39460	0.57149	0.39485	0.41804	0.44197	0.51522	0.56729	0.42503	0.42503	0.42503	0.46174
111167014	8	0.06752	0.0030	0.06314	0.07460	0.06338	0.06608	0.06671	0.06855	0.07410	0.07460	0.07460	0.07460	0.06752

Expectedly, execution time is increased by a factor of 2, as there is 2 times more data in AuditConfig1 than in AuditConfig0.

---

Test C, TestData5, AuditConfig2, 2025-01-15:

TestActivity	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
110947998	5004	15.50312	0.17040	15.27006	15.82552	15.27113	15.36292	15.49537	15.62461	15.80802	15.39030	15.39030	15.39030	15.50312
110927212	1003	3.56198	0.19150	3.40682	4.08712	3.40982	3.47836	3.51358	3.55960	4.03660	4.08712	4.08712	4.08712	3.56198
111065032	501	1.89772	0.03167	1.84710	1.93822	1.84881	1.86426	1.90124	1.92871	1.93732	1.92855	1.92855	1.92855	1.89772
111108897	104	0.47161	0.01773	0.45769	0.51688	0.45774	0.45923	0.46689	0.47452	0.51365	0.48455	0.48455	0.48455	0.47161
111013325	54	0.27918	0.01173	0.26224	0.29434	0.26261	0.26800	0.27901	0.29159	0.29409	0.28335	0.28335	0.28335	0.27918
111167014	4	0.06305	0.00246	0.05938	0.06705	0.05953	0.06147	0.06272	0.06459	0.06704	0.06094	0.06094	0.06094	0.06305

This is slower than Test A under equivalent conditions, since in Test A data is retrieved only from the latest audit-entity version, but in Test C -- all audit-entity versions are used.

Test D

Find all audit records for an entity in a specific time period. The result is ordered by auditDate descending.

Columns:

• Audits - limit on the result set size.

Time periods were chosen in such a way as to cover various cases of partition elimination (access to the leftmost/righmost/mid partition).

Test D, TestData5, AuditConfig0:

n	Start	End	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
1	1910-01-01	2030-01-01	100	2.73833	0.11944	2.52477	2.94664	2.53900	2.67739	2.71580	2.82606	2.94050	2.66708	2.66708	2.66708	2.73833
2	2024-01-01	2024-12-31	100	2.75232	0.16524	2.53280	3.02661	2.54053	2.61520	2.68881	2.90831	3.01627	2.67246	2.67246	2.67246	2.75232
3	2024-06-01	2024-12-31	100	2.56375	0.05127	2.44266	2.61337	2.45099	2.54560	2.56837	2.60833	2.61301	2.44266	2.44266	2.44266	2.56375
4	2024-06-01	2024-07-01	100	2.81315	0.36129	2.52682	3.58424	2.52903	2.55247	2.63314	3.06435	3.55292	2.59474	2.59474	2.59474	2.81315
5	2010-01-01	2010-12-31	100	3.27141	0.14737	3.15408	3.65456	3.15679	3.18153	3.23820	3.28602	3.62617	3.23778	3.23778	3.23778	3.27141
6	2010-06-01	2010-12-31	100	3.36121	0.34785	3.13777	4.14543	3.14093	3.17981	3.20557	3.41472	4.11752	4.14543	4.14543	4.14543	3.36121
7	2010-06-01	2010-07-01	0	2.69954	0.02792	2.64367	2.73188	2.64645	2.68383	2.70125	2.72392	2.73167	2.71519	2.71519	2.71519	2.69954
8	2017-01-01	2017-12-31	100	2.87512	0.05773	2.79521	2.98996	2.79802	2.83784	2.86165	2.90650	2.98524	2.89437	2.89437	2.89437	2.87512
9	2017-06-01	2017-12-31	100	2.86047	0.03224	2.80730	2.90380	2.80860	2.82303	2.87669	2.88051	2.90149	2.88067	2.88067	2.88067	2.86047
10	2017-06-01	2017-07-01	100	3.03557	0.29995	2.80531	3.74466	2.80924	2.85423	2.90943	3.11770	3.70824	3.03010	3.03010	3.03010	3.03557
11	2016-01-01	2017-12-31	100	2.84872	0.02303	2.81532	2.88211	2.81572	2.82489	2.84994	2.86921	2.88131	2.87410	2.87410	2.87410	2.84872
12	2016-06-01	2017-06-01	100	2.98183	0.18049	2.84880	3.33909	2.85002	2.86312	2.88573	3.18002	3.32411	3.17691	3.17691	3.17691	2.98183
13	2013-06-01	2019-06-01	100	2.88368	0.19535	2.72277	3.27822	2.72294	2.73608	2.79557	3.06912	3.26147	2.79319	2.79319	2.79319	2.88368

Test D, TestData5a, AuditConfig0:

n	Start	End	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
1	1910-01-01	2030-01-01	100	0.49465	0.01597	0.47762	0.52798	0.47763	0.48091	0.49257	0.50720	0.52607	0.50888	0.50888	0.50888	0.49465
2	2024-01-01	2024-12-31	100	0.51157	0.04852	0.47028	0.63669	0.47123	0.48506	0.49104	0.53126	0.62620	0.47028	0.47028	0.47028	0.51157
3	2024-06-01	2024-12-31	100	0.50325	0.04234	0.47730	0.61519	0.47743	0.47924	0.48743	0.50637	0.60677	0.53093	0.53093	0.53093	0.50325
4	2024-06-01	2024-07-01	100	0.48383	0.01063	0.46805	0.49740	0.46850	0.47379	0.48453	0.49473	0.49717	0.48996	0.48996	0.48996	0.48383
5	2010-01-01	2010-12-31	100	0.56754	0.12732	0.46590	0.80589	0.46740	0.48172	0.49531	0.67021	0.80259	0.48084	0.48084	0.48084	0.56754
6	2010-06-01	2010-12-31	100	0.55709	0.07664	0.47510	0.71918	0.47590	0.49569	0.54178	0.60200	0.71163	0.71918	0.71918	0.71918	0.55709
7	2010-06-01	2010-07-01	0	0.00641	0.00085	0.00523	0.00792	0.00524	0.00584	0.00629	0.00700	0.00784	0.00611	0.00611	0.00611	0.00641
8	2017-01-01	2017-12-31	100	0.51366	0.05583	0.45937	0.60843	0.45959	0.46709	0.48491	0.56813	0.60459	0.57001	0.57001	0.57001	0.51366
9	2017-06-01	2017-12-31	100	0.47652	0.01600	0.45405	0.51234	0.45496	0.46760	0.47339	0.48673	0.50989	0.45405	0.45405	0.45405	0.47652
10	2017-06-01	2017-07-01	100	0.45215	0.01882	0.42824	0.49508	0.42861	0.43861	0.45211	0.46035	0.49192	0.45147	0.45147	0.45147	0.45215
11	2016-01-01	2017-12-31	100	0.47196	0.01048	0.45848	0.48904	0.45875	0.46227	0.47120	0.48073	0.48880	0.46918	0.46918	0.46918	0.47196
12	2016-06-01	2017-06-01	100	0.45449	0.00957	0.43704	0.46961	0.43779	0.44772	0.45582	0.46059	0.46903	0.46389	0.46389	0.46389	0.45449
13	2013-06-01	2019-06-01	100	0.43213	0.02616	0.41490	0.49844	0.41508	0.41690	0.42067	0.43871	0.49406	0.49844	0.49844	0.49844	0.43213

Test D, TestData14, AuditConfig0:

n	Start	End	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
1	1910-01-01	2030-01-01	100	0.51097	0.04816	0.47658	0.63611	0.47687	0.48287	0.48842	0.52573	0.62546	0.52442	0.52442	0.52442	0.51097
2	2024-01-01	2024-12-31	100	0.48141	0.00787	0.47196	0.50086	0.47234	0.47609	0.48008	0.48456	0.49926	0.47581	0.47581	0.47581	0.48141
3	2024-06-01	2024-12-31	100	0.48132	0.00956	0.46759	0.49862	0.46802	0.47460	0.47972	0.48808	0.49816	0.47709	0.47709	0.47709	0.48132
4	2024-06-01	2024-07-01	100	0.48839	0.03060	0.46419	0.56114	0.46426	0.47039	0.47596	0.49948	0.55727	0.56114	0.56114	0.56114	0.48839
5	2010-01-01	2010-12-31	100	0.46783	0.00657	0.45638	0.47546	0.45667	0.46176	0.47165	0.47235	0.47517	0.45638	0.45638	0.45638	0.46783
6	2010-06-01	2010-12-31	100	0.46971	0.01848	0.45214	0.51729	0.45251	0.46023	0.46493	0.47308	0.51366	0.46497	0.46497	0.46497	0.46971
7	2010-06-01	2010-07-01	0	0.00521	0.00046	0.00480	0.00641	0.00481	0.00495	0.00508	0.00534	0.00632	0.00496	0.00496	0.00496	0.00521
8	2017-01-01	2017-12-31	100	0.47601	0.01437	0.46193	0.51070	0.46199	0.46553	0.47348	0.48018	0.50832	0.47793	0.47793	0.47793	0.47601
9	2017-06-01	2017-12-31	100	0.46920	0.01076	0.45597	0.48639	0.45619	0.45920	0.46674	0.47918	0.48627	0.46508	0.46508	0.46508	0.46920
10	2017-06-01	2017-07-01	100	0.43908	0.01686	0.41583	0.47694	0.41710	0.42993	0.43534	0.44498	0.47484	0.45596	0.45596	0.45596	0.43908
11	2016-01-01	2017-12-31	100	0.47061	0.01641	0.45132	0.50904	0.45157	0.46011	0.46641	0.47800	0.50600	0.47866	0.47866	0.47866	0.47061
12	2016-06-01	2017-06-01	100	0.43494	0.01077	0.41745	0.45984	0.41848	0.42886	0.43458	0.43716	0.45790	0.42780	0.42780	0.42780	0.43494
13	2013-06-01	2019-06-01	100	0.42781	0.00914	0.41338	0.44106	0.41382	0.41922	0.42826	0.43545	0.44050	0.43545	0.43545	0.43545	0.42781

---

Test D, TestData5, AuditConfig0a:

Start	End	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
2024-01-01	2024-12-31	100	3.13285	0.63979	2.48781	4.06103	2.49721	2.59026	2.83657	3.73241	4.05897	3.02222	3.02222	3.02222	3.13285
2010-01-01	2010-12-31	100	1.98861	0.04712	1.90982	2.04656	1.91061	1.94864	2.00233	2.02370	2.04553	2.04656	2.04656	2.04656	1.98861
2017-01-01	2017-12-31	100	3.48278	0.26821	3.19193	4.05025	3.19406	3.26147	3.47419	3.62407	4.01786	4.05025	4.05025	4.05025	3.48278
2016-01-01	2017-12-31	100	5.05532	0.74835	4.69480	7.17605	4.69637	4.78695	4.83946	4.88715	6.95005	7.17605	7.17605	7.17605	5.05532
2013-06-01	2019-06-01	100	20.00285	2.73541	13.41915	23.41706	13.95966	18.87262	20.24669	21.95338	23.31302	13.41915	13.41915	13.41915	20.00285

Test D, TestData5a, AuditConfig0a:

Start	End	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
2024-01-01	2024-12-31	100	0.48104	0.03639	0.44932	0.56199	0.45014	0.45820	0.46466	0.50848	0.55791	0.52120	0.52120	0.52120	0.48104
2010-01-01	2010-12-31	100	0.23596	0.02398	0.21509	0.29092	0.21525	0.22090	0.22452	0.25191	0.28781	0.22416	0.22416	0.22416	0.23596
2017-01-01	2017-12-31	100	0.37703	0.02263	0.35158	0.42309	0.35165	0.35444	0.37850	0.38874	0.42070	0.38231	0.38231	0.38231	0.37703
2016-01-01	2017-12-31	100	0.35307	0.01580	0.33779	0.38681	0.33822	0.34246	0.34666	0.36357	0.38544	0.34822	0.34822	0.34822	0.35307
2013-06-01	2019-06-01	100	0.25407	0.01706	0.22728	0.28009	0.22863	0.24086	0.25197	0.27155	0.27975	0.25658	0.25658	0.25658	0.25407

Test D, TestData14, AuditConfig0a:

Start	End	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
2024-01-01	2024-12-31	100	0.43229	0.01601	0.41565	0.47042	0.41568	0.42174	0.42866	0.43924	0.46780	0.43758	0.43758	0.43758	0.43229
2010-01-01	2010-12-31	100	0.23386	0.00905	0.22389	0.25323	0.22408	0.22665	0.23116	0.24037	0.25213	0.23977	0.23977	0.23977	0.23386
2017-01-01	2017-12-31	100	0.35122	0.01175	0.33836	0.37301	0.33856	0.34117	0.34828	0.36096	0.37235	0.37301	0.37301	0.37301	0.35122
2016-01-01	2017-12-31	100	0.34772	0.01317	0.33447	0.37317	0.33466	0.33728	0.34357	0.35737	0.37252	0.34058	0.34058	0.34058	0.34772
2013-06-01	2019-06-01	100	0.24443	0.00552	0.23621	0.25174	0.23635	0.24065	0.24371	0.25108	0.25169	0.24166	0.24166	0.24166	0.24443

Searching on auditDate is expectedly faster when there is an index on this property.

With table partitioning (TestData14), execution is as fast as without it but with an index (TestData5a), even when mid partitions are accessed. It was hypothesised that the test query was biased towards the auditDate index as it limited the result set to 100 rows, enabling the database engine to use the index efficiently. This hypothesis originates from https://dba.stackexchange.com/a/45954:

At absolute best you might get benefits from an index if you are accessing 20% of a table's rows, but it's more likely that 1-5% is an effective limit.

To test the hypothesis, test D2 was conducted.

Test D1

Instead of retrieving the latest audit-entity version, retrieve the synthetic audit-entity.

Test D1, TestData5, AuditConfig0:

n	Start	End	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
1	1910-01-01	2030-01-01	100	2.70806	0.10858	2.53856	2.93394	2.54536	2.62415	2.71216	2.74842	2.92064	2.60657	2.60657	2.60657	2.70806
2	2024-01-01	2024-12-31	100	2.87830	0.37845	2.43325	3.34176	2.43833	2.56254	2.73748	3.29700	3.34139	2.82838	2.82838	2.82838	2.87830
3	2024-06-01	2024-12-31	100	3.05670	0.30062	2.60715	3.42324	2.60890	2.80856	3.08668	3.37350	3.42136	3.36319	3.36319	3.36319	3.05670
4	2024-06-01	2024-07-01	100	2.73893	0.10725	2.60830	2.96247	2.61132	2.66049	2.72220	2.80595	2.94886	2.67889	2.67889	2.67889	2.73893
5	2010-01-01	2010-12-31	100	3.35068	0.19990	3.15397	3.66368	3.15552	3.19392	3.25676	3.58159	3.66344	3.15397	3.15397	3.15397	3.35068
6	2010-06-01	2010-12-31	100	3.25391	0.11202	3.11950	3.47328	3.12462	3.17649	3.21435	3.35302	3.46583	3.39881	3.39881	3.39881	3.25391
7	2010-06-01	2010-07-01	0	2.91921	0.14121	2.73484	3.18436	2.73562	2.76234	2.94990	2.99948	3.16810	2.73484	2.73484	2.73484	2.91921
8	2017-01-01	2017-12-31	100	2.91097	0.13601	2.81421	3.19636	2.81448	2.81768	2.84195	3.03837	3.18351	3.06793	3.06793	3.06793	2.91097
9	2017-06-01	2017-12-31	100	2.99605	0.23380	2.81049	3.57919	2.81276	2.83872	2.89566	3.11235	3.53391	2.90876	2.90876	2.90876	2.99605
10	2017-06-01	2017-07-01	100	2.95393	0.11391	2.78631	3.19294	2.79127	2.86355	2.95733	3.00431	3.17769	2.97300	2.97300	2.97300	2.95393
11	2016-01-01	2017-12-31	100	3.07161	0.48347	2.78437	4.26111	2.78912	2.83198	2.86238	3.08949	4.19693	4.26111	4.26111	4.26111	3.07161
12	2016-06-01	2017-06-01	100	2.83994	0.02351	2.80426	2.88308	2.80520	2.82475	2.83589	2.85650	2.88109	2.82843	2.82843	2.82843	2.83994
13	2013-06-01	2019-06-01	100	2.99019	0.25537	2.71732	3.35563	2.71751	2.74886	2.90830	3.28567	3.35180	2.75873	2.75873	2.75873	2.99019

Test D1, TestData5a, AuditConfig0:

n	Start	End	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
1	1910-01-01	2030-01-01	100	0.50630	0.01520	0.49007	0.54160	0.49040	0.49705	0.50137	0.51641	0.53930	0.49335	0.49335	0.49335	0.50630
2	2024-01-01	2024-12-31	100	0.49403	0.00883	0.48366	0.51100	0.48389	0.48643	0.49225	0.50073	0.51029	0.48589	0.48589	0.48589	0.49403
3	2024-06-01	2024-12-31	100	0.49336	0.01875	0.47011	0.53774	0.47040	0.48363	0.49031	0.50037	0.53441	0.53774	0.53774	0.53774	0.49336
4	2024-06-01	2024-07-01	100	0.49096	0.00924	0.47294	0.50644	0.47400	0.48702	0.49008	0.49848	0.50583	0.50644	0.50644	0.50644	0.49096
5	2010-01-01	2010-12-31	100	0.52114	0.04583	0.47098	0.59714	0.47155	0.47982	0.51063	0.56706	0.59583	0.50000	0.50000	0.50000	0.52114
6	2010-06-01	2010-12-31	100	0.48575	0.02121	0.46540	0.54085	0.46620	0.47358	0.47932	0.49091	0.53589	0.47427	0.47427	0.47427	0.48575
7	2010-06-01	2010-07-01	0	0.00601	0.00068	0.00488	0.00721	0.00489	0.00566	0.00606	0.00634	0.00712	0.00640	0.00640	0.00640	0.00601
8	2017-01-01	2017-12-31	100	0.48836	0.01802	0.46823	0.53289	0.46896	0.47705	0.48547	0.49426	0.52934	0.48212	0.48212	0.48212	0.48836
9	2017-06-01	2017-12-31	100	0.49922	0.03711	0.47512	0.60144	0.47529	0.48197	0.48817	0.49770	0.59211	0.47681	0.47681	0.47681	0.49922
10	2017-06-01	2017-07-01	100	0.45317	0.02557	0.43233	0.51181	0.43253	0.43781	0.44127	0.46944	0.50872	0.43896	0.43896	0.43896	0.45317
11	2016-01-01	2017-12-31	100	0.49816	0.03659	0.47573	0.59527	0.47593	0.47778	0.48489	0.49988	0.58794	0.47780	0.47780	0.47780	0.49816
12	2016-06-01	2017-06-01	100	0.46287	0.03050	0.44042	0.52672	0.44066	0.44389	0.45042	0.47385	0.52521	0.46123	0.46123	0.46123	0.46287
13	2013-06-01	2019-06-01	100	0.43931	0.01771	0.42113	0.47817	0.42147	0.42506	0.43371	0.44946	0.47579	0.42113	0.42113	0.42113	0.43931

Test D1, TestData14, AuditConfig0:

n	Start	End	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
1	1910-01-01	2030-01-01	100	0.51553	0.02628	0.49139	0.56195	0.49170	0.49708	0.50429	0.53383	0.56178	0.51754	0.51754	0.51754	0.51553
2	2024-01-01	2024-12-31	100	0.53007	0.06593	0.47341	0.67405	0.47496	0.49379	0.50297	0.55102	0.66934	0.52572	0.52572	0.52572	0.53007
3	2024-06-01	2024-12-31	100	0.49934	0.03265	0.46566	0.57319	0.46594	0.47103	0.49367	0.51364	0.56852	0.46848	0.46848	0.46848	0.49934
4	2024-06-01	2024-07-01	100	0.50847	0.03148	0.48599	0.58294	0.48611	0.48904	0.49698	0.51814	0.57919	0.49373	0.49373	0.49373	0.50847
5	2010-01-01	2010-12-31	100	0.48597	0.01257	0.46345	0.50259	0.46436	0.47595	0.48682	0.49743	0.50224	0.48826	0.48826	0.48826	0.48597
6	2010-06-01	2010-12-31	100	0.47832	0.01225	0.46138	0.50385	0.46156	0.46950	0.47743	0.48477	0.50204	0.48572	0.48572	0.48572	0.47832
7	2010-06-01	2010-07-01	0	0.00565	0.00039	0.00508	0.00609	0.00509	0.00523	0.00587	0.00596	0.00608	0.00510	0.00510	0.00510	0.00565
8	2017-01-01	2017-12-31	100	0.49488	0.02550	0.46436	0.55512	0.46564	0.47872	0.48922	0.50570	0.55101	0.55512	0.55512	0.55512	0.49488
9	2017-06-01	2017-12-31	100	0.47825	0.01672	0.45771	0.51327	0.45859	0.46676	0.47218	0.49124	0.51156	0.48961	0.48961	0.48961	0.47825
10	2017-06-01	2017-07-01	100	0.44286	0.02459	0.41359	0.48651	0.41385	0.42480	0.43745	0.46291	0.48550	0.43155	0.43155	0.43155	0.44286
11	2016-01-01	2017-12-31	100	0.48305	0.01408	0.46569	0.51501	0.46617	0.47112	0.48130	0.48841	0.51292	0.48225	0.48225	0.48225	0.48305
12	2016-06-01	2017-06-01	100	0.47275	0.04571	0.43938	0.59173	0.43971	0.44515	0.45671	0.48147	0.58245	0.47178	0.47178	0.47178	0.47275
13	2013-06-01	2019-06-01	100	0.45147	0.02013	0.42290	0.49596	0.42357	0.43843	0.44967	0.45886	0.49301	0.45427	0.45427	0.45427	0.45147

Results of Test D1 show no significant difference from those of Test D.

Test D2

The query used in this test does not limit the result set size. It uses paging to retrieve results and counts the total number of them, effectively accessing all data from the table.

Test D2, TestData5a, AuditConfig0:

Start	End	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
2013-06-01	2019-06-01	2803	13.07255	0.80707	12.29237	15.03477	12.31329	12.68478	12.76372	13.34847	14.91665	12.75585	12.75585	12.75585	13.07255
2017-01-01	2017-12-31	465	2.26965	0.11505	2.13746	2.48522	2.13884	2.17530	2.24214	2.36755	2.47540	2.48522	2.48522	2.48522	2.26965

Test D2, TestData14, AuditConfig0:

Start	End	Audits	Mean	Std	Min	Max	10th pctl	25th pctl	50th pctl	75th pctl	90th pctl	Mean [0,25]	Mean [25,50]	Mean [50,75]	Mean [75,100]
2013-06-01	2019-06-01	2803	12.71182	0.21507	12.50013	13.01757	12.50230	12.52388	12.59646	12.94365	13.01382	12.98006	12.98006	12.98006	12.71182
2017-01-01	2017-12-31	465	2.04328	0.09335	1.91365	2.18574	1.91826	1.97719	2.01266	2.12388	2.18505	2.18574	2.18574	2.18574	2.04328

According to the results of this test, the hypothesis is rejected, as both test data variations produce equivalent results.

Conclusion

Saving audited entities

• Table partitioning does not have a significant effect on throughput.
• Existence of indices and FKs does not have a significant effect on throughput.

It is hypothesised that 1 million audit records is not enough to raise concerns about the effects of indices, FKs and table partitioning on performance.

Modifying audited entities

• Expectedly, auditing a modification event is faster than auditing a save event, as the number of created audit-prop records is significantly lower. It is around 2-4 times faster.
• Indices and FKs do not have a significant effect on performance.
• Table partitioning does not have a significant effect on performance.

Searching audit data

Searching audit data is faster with table partitioning when search queries leverage partition elimination. Specifically, when auditDate is used in filtering conditions.

On the other hand, the same performance improvement can be achieved without table partitioning by using an index on auditDate.

In theory, table partitioning is more efficient when accessing data that would require multiple steps to reach using an index. The conducted tests did not confirm this, and it is hypothesised that the reason is not enough test data.

---

Further thoughts on table partitioning.