search

KitsuneTech-com / Velox-Server

Platform-neutral database communication framework
3 stars 1 forks source link

Insert optimization for StatementSet #24

Open skylardarkfox opened 3 years ago

skylardarkfox commented 3 years ago

Currently looking into insert optimization. Currently, StatementSet inserts are done with batched PreparedStatements, but research indicates that using multi-row inserts can improve performance by 10x or better, up to a point (somewhere between 20-40 rows per statement). Might be able to build and batch dynamically to take advantage of this.

Originally posted by @skylardarkfox in https://github.com/KitsuneTech-com/Velox/issues/16#issuecomment-830448358

skylardarkfox commented 3 years ago

Any optimization using multi-row inserts must take into account that nested Models require an array of all inserted ids. Behavior differs between MySQL and SQL Server on their respective functions (LAST_INSERT_ID() and SCOPE_IDENTITY(), respectively), so Connection may need to be altered as well.

Edit: because nested Model inserts must be able to match the inserted id of the parent row with the inserted submodel "pseudo-column", these cannot be batch optimized and must be run individually in order to do this matching. A flag will need to be set on StatementSet to disable any such optimization in the case of nested Models. This will be implemented with a boolean property: StatementSet->optimize, default value of true.