U4-9111 - Benchmark BulkCopy changes from U4-9107

Created by Umbraco 24 Oct 2016, 12:56:34 Updated by Stephan 27 Oct 2016, 12:11:17

Relates to: U4-9107

Subtask of: U4-9085

For U4-9107 compare

  • Perfomance
  • Memory usage

Before/after this update. Using benchmark.net

Comments

Shannon Deminick 25 Oct 2016, 10:16:05

||Method||Median||StdDev||Scaled||Scaled-SD||Gen 0||Gen 1||Gen 2||Bytes Allocated/Op|| |SqlCeOneByOne | 297.4708 ms | 2.9820 ms | 1.00 | 0.00 | 24.13 | 17.70 | - | 1,319,525.91| |SqlCeTableDirect | 65.4482 ms | 2.7813 ms | 0.22 | 0.01 | 1.00 | - | - | 177,143.38| |SqlServerBulkInsertStatements | 159.3152 ms | 0.5159 ms | 0.54 | 0.00 | 7.09 | 6.30 | - | 592,128.21| |SqlServerBulkCopy | 11.6734 ms | 0.6890 ms | 0.04 | 0.00 | 5.58 | - | - | 223,283.85|


Shannon Deminick 25 Oct 2016, 10:36:11

Part of the PR for http://issues.umbraco.org/issue/U4-9107

To test:

  • I've created a new benchmark project, you'll need to modify the SQL Server conn string in BulkSqlBenchmarks.SetupSqlServer
  • Then set Umbraco.Tests.Benchmarks as the default startup project
  • Change to Release mode in VS drop down
  • Press ctrl + F5 to start the console app - which does the benchmarks

You should see a similar result to the above, the output of the benchmarks in report format go to src\Umbraco.Tests.Benchmarks\bin\Release\BenchmarkDotNet.Artifacts\results


Stephan 26 Oct 2016, 16:22:30

Ran the benchmarks, here are my numbers:

||Method||Median||StdDev||Scaled||Scaled-SD||Gen 0||Gen 1||Gen 2||Bytes Allocated/Op|| | SqlCeOneByOne | 280.4704 ms | 2.7016 ms | 1.00 | 0.00 | 23.00 | 22.00 | - | 2 233 976,48 | | SqlCeTableDirect | 60.2668 ms | 0.6761 ms | 0.22 | 0.00 | 0.93 | - | - | 220 668,03 | | SqlServerBulkInsertStatements | 358.5996 ms | 2.2186 ms | 1.28 | 0.01 | 9.00 | 6.00 | - | 1 012 331,93 | | SqlServerBulkCopy | 31.3254 ms | 2.3716 ms | 0.11 | 0.01 | 0.93 | - | - | 230 355,38 |

This is with SQL 2016 on my supra-new laptop... bit disapointing I'm not seeing better results esp. for SqlServerBulkCopy but it still is a massive improvement. Not sure how to explain SqlServerBulkInsertStatements horrible perfs.


Stephan 27 Oct 2016, 09:41:41

Have tried again today, incl. different combinations of BenchmarkDotNet config (JIT, GC, x86/x64...) and my SqlCE numbers tend to align with yours. SqlCeOneByOne "bytes allocated" keeps reporting weird and changing values, no idea why, not important.

SqlServerBulkCopy is pretty consistent/close around 30ms median (vs your 11ms).

SqlServerBulkInsertStatements remain crazy more expensive than what you report, no idea why. I guess what's important is SqlServerBulkCopy, happy with it, so closing this issue.


Shannon Deminick 27 Oct 2016, 12:07:27

Are you sure you've entered the correct connection strings for these tests?


Shannon Deminick 27 Oct 2016, 12:08:22

Also may depend on your SQL server version, what are you using?


Stephan 27 Oct 2016, 12:11:17

correct connection string yes, SQL 2016 Express


Priority: Normal

Type: Bug

State: Fixed

Assignee:

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions:

Due in version:

Sprint: Sprint 45

Story Points: 1

Cycle: