Search performance improvement suggestion

[storni]

Hi,

I would like to suggest to change SearchResult entity primary key from

	@GeneratedValue(strategy = GenerationType.AUTO, generator="SEQ_SEARCH_RES")
	@SequenceGenerator(name="SEQ_SEARCH_RES", sequenceName="SEQ_SEARCH_RES")
	@Id
	@Column(name = "PID")
	private Long myId;

to

	@GeneratedValue(generator = "UUID")
	@GenericGenerator(name = "UUID", strategy = "org.hibernate.id.UUIDGenerator")
	@Id
	@Column(name = "PID")
	private String myId;

By this way, when inserting many SearchResult instances for every term (specially when searching by identifiers in an attribute that contain a list of identifiers) insert statements can be packed into few statements according the batch size.
Unfortunately, insert statements cannot be packed if entity primary key cannot be determined before executing the statement because hibernate will not use batch.

Therefore, once this change is applied, rewrite statements can be enabled using this sample configuration (spring-boot style):

spring.datasource.url=jdbc:mysql:https://fhir-server-mysql/fhir?rewriteBatchedStatements=true
# batch_size -> how many statements will be grouped
spring.jpa.properties.hibernate.jdbc.batch_size=20
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true

In order to inspect how statements are grouped, just change the JDBC url as follows:

spring.datasource.url=jdbc:mysql:https://fhir-server-mysql/fhir?rewriteBatchedStatements=true&profileSQL=true&logger=com.mysql.jdbc.log.Slf4JLogger

Sample slf4j config:

<configuration scan="true" scanPeriod="30 seconds">
    <!-- main config -->
    <logger name="com.mysql" level="DEBUG">
        <appender-ref ref="STDOUT" />
    </logger>
</configuration>

Sample output in log (just two statements grouped here):

2018-02-26 19:23:37.252  INFO 5480 [main] --- MySQL                                   : QUERY created: Mon Feb 26 19:23:37 ART 2018 duration: 1 connection: 282 statement: 1054 resultset: 0 message: insert into hfj_search_result (search_order, resource_pid, search_pid, pid) values (20, 26, 12, 'b5493738-63d5-42a4-ae1f-b50066db3046'),(21, 27, 12, '55b61992-93c8-41fe-9bd3-5187732df1d1')

[storni]

Related issues:
#860
#536

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.