Re: Adding batch update support in SimpleORM
Franck Routier <[email protected]> Sat, 02 Feb 2013 15:21:52 +0100
| Newsgroups | gmane.comp.java.orm.simpleorm |
|---|---|
| Message-ID | <[email protected]> |
--------------ms000400090206040300060607
Content-Type: multipart/alternative;
boundary="------------030603050702060603070001"
--------------030603050702060603070001
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Hi Anthony,
Le 02/02/2013 01:42, anthony berglas a =E9crit :
>
> Excellent work.
>
Thanks :-)
>
> (The batching is all wrong in JDBC/Dbs. It should be just send any=20
> unrelated statements to the db, have them execute, and return a list=20
> of results for each. I have recently been working on KMIP, which=20
> almost gets that aspect right.)
>
> What sort of performane improvements did you get for Oracle and=20
> Postgresql?
First of all, let me say our banchmark tests are probably not totally=20
accurate. Running Insert, then batch insert does not give the same=20
result than in reverse order. The second test runs always a bit faster,=20
so I did put batchInsert before insert to make sure there is a real gain...
On Postgres running localy (on SSD), I get the follonwing result:
[java] [no Session] ------ simpleormBatchInsert 90 (119)=20
(microsecond/row) [Statistics Trans: 12 Flush: 10006 Finddb: 10009=20
Querydb: 0 CurTim: 1359812349318]
[java] [no Session] ------ simpleormInsert 242 (119)=20
(microsecond/row) [Statistics Trans: 13 Flush: 20006 Finddb: 20012=20
Querydb: 0 CurTim: 1359812351745]
So batching is almost 3 time faster than row by row flush on Postgres.=20
It's even kicker than raw jdbc (well, I expected so).
On Oracle, my tests run on a LAN Virtual Machine running a non optimized=20
Oracle install.
jdbc : ~1500 microsecond/row
simpleormBatchInsert : ~50 microsecond/row (!!)
simpleormInsert : ~2500 microsecond/row
Here the result is really spectacular, as network overhead really counts=20
in this situation.
I also think that batching in Oracle jdbc is much more efficient than=20
with Postgres.
I think that Postgresql network protocol only allows for one parameter=20
set to be sent at once (see=20
http://www.postgresql.org/docs/9.2/static/protocol-flow.html#PROTOCOL-FLOW-=
EXT-QUERY).=20
Some initialisation steps are saved, but sending parameters is done=20
multiple time... So basically with Postgres, PreparedStatement is=20
created once and cached but each parameter set is sent separately=20
(confirmed by running visualvm).
I'm going to make tests on a real life example on monday on a workload=20
that takes about one hour (on Postgres), 60% of the time creating=20
similar new records in the database.
Best regards,
Franck
--------------030603050702060603070001
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
<html>
<head>
<meta content=3D"text/html; charset=3DISO-8859-1"
http-equiv=3D"Content-Type">
</head>
<body text=3D"#000000" bgcolor=3D"#FFFFFF">
<div class=3D"moz-cite-prefix">Hi Anthony,<br>
<br>
Le 02/02/2013 01:42, anthony berglas a écrit :<br>
</div>
<blockquote
cite=3D"mid:[email protected]=
l.com"
type=3D"cite">
<span style=3D"display:none"> </span>
=20=20=20=20=20=20
<div id=3D"ygrp-text">
<p>Excellent work. <br>
</p>
</div>
</div>
</div>
</blockquote>
<br>
Thanks :-)<br>
<blockquote
cite=3D"mid:[email protected]=
l.com"
type=3D"cite">
<div id=3D"ygrp-mlmsg" style=3D"position:relative;">
<div id=3D"ygrp-msg" style=3D"z-index: 1;">
<div id=3D"ygrp-text">
<div><br>
</div>
<div>(The batching is all wrong in JDBC/Dbs. It should be
just send any unrelated statements to the db, have them
execute, and return a list of results for each. I have
recently been working on KMIP, which almost gets that
aspect right.)</div>
<div><br>
</div>
<div>What sort of performane improvements did you get for
Oracle and Postgresql?</div>
</div>
</div>
</div>
</blockquote>
First of all, let me say our banchmark tests are probably not
totally accurate. Running Insert, then batch insert does not give
the same result than in reverse order. The second test runs always a
bit faster, so I did put batchInsert before insert to make sure
there is a real gain...<br>
<br>
On Postgres running localy (on SSD), I get the follonwing result:<br>
<br>
[java] [no Session] ------ simpleormBatchInsert =
&nb=
sp;
90 (119) (microsecond/row) [Statistics Trans: 12 Flush: 100=
06
Finddb: 10009 Querydb: 0 CurTim: 1359812349318]<br>
[java] [no Session] ------ simpleormInsert  =
; &n=
bsp;
242 (119) (microsecond/row) [Statistics Trans: 13 Flush: 20=
006
Finddb: 20012 Querydb: 0 CurTim: 1359812351745]<br>
<br>
So batching is almost 3 time faster than row by row flush on
Postgres. It's even kicker than raw jdbc (well, I expected so).<br>
<br>
On Oracle, my tests run on a LAN Virtual Machine running a non
optimized Oracle install.<br>
<br>
jdbc : ~1500 microsecond/row<br>
simpleormBatchInsert : ~50 microsecond/row (!!)<br>
simpleormInsert : ~2500 microsecond/row<br>
<br>
Here the result is really spectacular, as network overhead really
counts in this situation.<br>
I also think that batching in Oracle jdbc is much more efficient
than with Postgres.<br>
I think that Postgresql network protocol only allows for one
parameter set to be sent at once (see
<a class=3D"moz-txt-link-freetext" href=3D"http://www.postgresql.org/do=
cs/9.2/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY">http://www.postgr=
esql.org/docs/9.2/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY</a>).
Some initialisation steps are saved, but sending parameters is done
multiple time... So basically with Postgres, PreparedStatement is
created once and cached but each parameter set is sent separately
(confirmed by running visualvm).<br>
<br>
I'm going to make tests on a real life example on monday on a
workload that takes about one hour (on Postgres), 60% of the time
creating similar new records in the database.<br>
<br>
Best regards,<br>
Franck<br>
</body>
</html>
--------------030603050702060603070001--
--------------ms000400090206040300060607
Content-Type: application/pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"
Content-Description: Signature cryptographique S/MIME
MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIINYjCC
BjQwggQcoAMCAQICAR4wDQYJKoZIhvcNAQEFBQAwfTELMAkGA1UEBhMCSUwxFjAUBgNVBAoT
DVN0YXJ0Q29tIEx0ZC4xKzApBgNVBAsTIlNlY3VyZSBEaWdpdGFsIENlcnRpZmljYXRlIFNp
Z25pbmcxKTAnBgNVBAMTIFN0YXJ0Q29tIENlcnRpZmljYXRpb24gQXV0aG9yaXR5MB4XDTA3
MTAyNDIxMDE1NVoXDTE3MTAyNDIxMDE1NVowgYwxCzAJBgNVBAYTAklMMRYwFAYDVQQKEw1T
dGFydENvbSBMdGQuMSswKQYDVQQLEyJTZWN1cmUgRGlnaXRhbCBDZXJ0aWZpY2F0ZSBTaWdu
aW5nMTgwNgYDVQQDEy9TdGFydENvbSBDbGFzcyAxIFByaW1hcnkgSW50ZXJtZWRpYXRlIENs
aWVudCBDQTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMcJg8zOLdgasSmkLhOr
lr6KMoOMpohBllVHrdRvEg/q6r8jR+EK75xCGhR8ToREoqe7zM9/UnC6TS2y9UKTpT1v7RSM
zR0t6ndl0TWBuUr/UXBhPk+Kmy7bI4yW4urC+y7P3/1/X7U8ocb8VpH/Clt+4iq7nirMcNh6
qJR+xjOhV+VHzQMALuGYn5KZmc1NbJQYclsGkDxDz2UbFqE2+6vIZoL+jb9x4Pa5gNf1TwSD
kOkikZB1xtB4ZqtXThaABSONdfmv/Z1pua3FYxnCFmdr/+N2JLKutIxMYqQOJebr/f/h5t95
m4JgrM3Y/w7YX9d7YAL9jvN4SydHsU6n65cCAwEAAaOCAa0wggGpMA8GA1UdEwEB/wQFMAMB
Af8wDgYDVR0PAQH/BAQDAgEGMB0GA1UdDgQWBBRTcu2SnODaywFcfH6WNU7y1LhRgjAfBgNV
HSMEGDAWgBROC+8apEBbpRdphzDKNGhD0EGu8jBmBggrBgEFBQcBAQRaMFgwJwYIKwYBBQUH
MAGGG2h0dHA6Ly9vY3NwLnN0YXJ0c3NsLmNvbS9jYTAtBggrBgEFBQcwAoYhaHR0cDovL3d3
dy5zdGFydHNzbC5jb20vc2ZzY2EuY3J0MFsGA1UdHwRUMFIwJ6AloCOGIWh0dHA6Ly93d3cu
c3RhcnRzc2wuY29tL3Nmc2NhLmNybDAnoCWgI4YhaHR0cDovL2NybC5zdGFydHNzbC5jb20v
c2ZzY2EuY3JsMIGABgNVHSAEeTB3MHUGCysGAQQBgbU3AQIBMGYwLgYIKwYBBQUHAgEWImh0
dHA6Ly93d3cuc3RhcnRzc2wuY29tL3BvbGljeS5wZGYwNAYIKwYBBQUHAgEWKGh0dHA6Ly93
d3cuc3RhcnRzc2wuY29tL2ludGVybWVkaWF0ZS5wZGYwDQYJKoZIhvcNAQEFBQADggIBAAqD
CH14qywGXLhjjF6uHLkjd02hcdh9hrw+VUsv+q1eeQWB21jWj3kJ96AUlPCoEGZ/ynJNScWy
6QMVQjbbMXltUfO4n4bGGdKo3awPWp61tjAFgraLJgDk+DsSvUD6EowjMTNx25GQgyYJ5RPI
zKKR9tQW8gGK+2+RHxkUCTbYFnL6kl8Ch507rUdPPipJ9CgJFws3kDS3gOS5WFMxcjO5DwKf
KSETEPrHh7p5shuuNktvsv6hxHTLhiMKX893gxdT3XLS9OKmCv87vkINQcNEcIIoFWbP9HOR
z9v3vQwR4e3ksLc2JZOAFK+ssS5XMEoznzpihEP0PLc4dCBYjbvSD7kxgDwZ+Aj8Q9PkbvE9
sIPP7ON0fz095HdThKjiVJe6vofq+n6b1NBc8XdrQvBmunwxD5nvtTW4vtN6VY7mUCmxsCie
uoBJ9OlqmsVWQvifIYf40dJPZkk9YgGTzWLpXDSfLSplbY2LL9C9U0ptvjcDjefLTvqSFc7t
w1sEhF0n/qpA2r0GpvkLRDmcSwVyPvmjFBGqUp/pNy8ZuPGQmHwFi2/14+xeSUDG2bwnsYJQ
G2EdJCB6luQ57GEnTA/yKZSTKI8dDQa8Sd3zfXb19mOgSF0bBdXbuKhEpuP9wirslFe6fQ1t
5j5R0xi72MZ8ikMu1RQZKCyDbMwazlHiMIIHJjCCBg6gAwIBAgIDBNFnMA0GCSqGSIb3DQEB
BQUAMIGMMQswCQYDVQQGEwJJTDEWMBQGA1UEChMNU3RhcnRDb20gTHRkLjErMCkGA1UECxMi
U2VjdXJlIERpZ2l0YWwgQ2VydGlmaWNhdGUgU2lnbmluZzE4MDYGA1UEAxMvU3RhcnRDb20g
Q2xhc3MgMSBQcmltYXJ5IEludGVybWVkaWF0ZSBDbGllbnQgQ0EwHhcNMTIwODI5MjAxNTIz
WhcNMTMwODMxMDg0MTExWjBnMRkwFwYDVQQNExBmQ0cxTUdlTUM1dkZVNVlKMSEwHwYDVQQD
DBhmcmFuY2sucm91dGllckBheGVnZS5jb20xJzAlBgkqhkiG9w0BCQEWGGZyYW5jay5yb3V0
aWVyQGF4ZWdlLmNvbTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAK5kIRjbP769
72PM00wgFczTGqmiU5dAY8XlSziUNq434SJZ3rFZoL0MY6hqVb/p5+u+u6TdhsVywJclPaZE
v+FgGtk74JvlX6oeSWJTOiGLTrk0TRwy7Q+80kbneFdhL+yk/ZTxxcj15IEOeWMuyG/IyePq
H4GtZMkCpsuCOL5bkq9g/H0JHyLSM0tWDbMuD4vKbSeBkzwiz4pSwuANCFMFeRr3d9iZQU0P
VzyKvu/cEUcdoDcVGMuMxX6FwgqrS5c70v/RPM8FafOrqprYfIxVQwxK9IhPgqM9kT6riQo6
OM62d2a21Y6iAeWe/1rssTKn+Stj8jH706LmVJlEGtcCAwEAAaOCA7MwggOvMAkGA1UdEwQC
MAAwCwYDVR0PBAQDAgSwMB0GA1UdJQQWMBQGCCsGAQUFBwMCBggrBgEFBQcDBDAdBgNVHQ4E
FgQUCfIkqGdMRvMTjM6TIpdcUPqdYW8wHwYDVR0jBBgwFoAUU3Ltkpzg2ssBXHx+ljVO8tS4
UYIwIwYDVR0RBBwwGoEYZnJhbmNrLnJvdXRpZXJAYXhlZ2UuY29tMIICIQYDVR0gBIICGDCC
AhQwggIQBgsrBgEEAYG1NwECAjCCAf8wLgYIKwYBBQUHAgEWImh0dHA6Ly93d3cuc3RhcnRz
c2wuY29tL3BvbGljeS5wZGYwNAYIKwYBBQUHAgEWKGh0dHA6Ly93d3cuc3RhcnRzc2wuY29t
L2ludGVybWVkaWF0ZS5wZGYwgfcGCCsGAQUFBwICMIHqMCcWIFN0YXJ0Q29tIENlcnRpZmlj
YXRpb24gQXV0aG9yaXR5MAMCAQEagb5UaGlzIGNlcnRpZmljYXRlIHdhcyBpc3N1ZWQgYWNj
b3JkaW5nIHRvIHRoZSBDbGFzcyAxIFZhbGlkYXRpb24gcmVxdWlyZW1lbnRzIG9mIHRoZSBT
dGFydENvbSBDQSBwb2xpY3ksIHJlbGlhbmNlIG9ubHkgZm9yIHRoZSBpbnRlbmRlZCBwdXJw
b3NlIGluIGNvbXBsaWFuY2Ugb2YgdGhlIHJlbHlpbmcgcGFydHkgb2JsaWdhdGlvbnMuMIGc
BggrBgEFBQcCAjCBjzAnFiBTdGFydENvbSBDZXJ0aWZpY2F0aW9uIEF1dGhvcml0eTADAgEC
GmRMaWFiaWxpdHkgYW5kIHdhcnJhbnRpZXMgYXJlIGxpbWl0ZWQhIFNlZSBzZWN0aW9uICJM
ZWdhbCBhbmQgTGltaXRhdGlvbnMiIG9mIHRoZSBTdGFydENvbSBDQSBwb2xpY3kuMDYGA1Ud
HwQvMC0wK6ApoCeGJWh0dHA6Ly9jcmwuc3RhcnRzc2wuY29tL2NydHUxLWNybC5jcmwwgY4G
CCsGAQUFBwEBBIGBMH8wOQYIKwYBBQUHMAGGLWh0dHA6Ly9vY3NwLnN0YXJ0c3NsLmNvbS9z
dWIvY2xhc3MxL2NsaWVudC9jYTBCBggrBgEFBQcwAoY2aHR0cDovL2FpYS5zdGFydHNzbC5j
b20vY2VydHMvc3ViLmNsYXNzMS5jbGllbnQuY2EuY3J0MCMGA1UdEgQcMBqGGGh0dHA6Ly93
d3cuc3RhcnRzc2wuY29tLzANBgkqhkiG9w0BAQUFAAOCAQEAGSGlokCrFniLxjc6W072LqWy
ISRKEqeC9u0pYSoQGVPAy1mNbu28ThrfAk8umOfFEDkzVH0TXH33TbsCViwsE/rCk6FC0U+Q
AMOn1Ajjf/9z7w8+zCHgmbLyABtYrmBO6AhCD7YLKYPE1VXkJvgdcHCKzVLIkvdIyKSkRn+R
lSorTXhg64lR8vQSX8C9EAY0T/hQyjFS9SDcEhsfOjkYdAiG7fVBft24Gj+3oDgk2479W47l
phG1aHhkflrDeho0h6LnGxY0+PATPuUkvDsr/RFE2VviGFH9UDxjNJmikrmTibCvS4seQFOm
ppHmJzqIcxcn5dqeBDVQYZmLhDkszjGCA90wggPZAgEBMIGUMIGMMQswCQYDVQQGEwJJTDEW
MBQGA1UEChMNU3RhcnRDb20gTHRkLjErMCkGA1UECxMiU2VjdXJlIERpZ2l0YWwgQ2VydGlm
aWNhdGUgU2lnbmluZzE4MDYGA1UEAxMvU3RhcnRDb20gQ2xhc3MgMSBQcmltYXJ5IEludGVy
bWVkaWF0ZSBDbGllbnQgQ0ECAwTRZzAJBgUrDgMCGgUAoIICHTAYBgkqhkiG9w0BCQMxCwYJ
KoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0xMzAyMDIxNDIxNTJaMCMGCSqGSIb3DQEJBDEW
BBSGHHxh+U07DjLd4FHJpi5+6ZkuljBsBgkqhkiG9w0BCQ8xXzBdMAsGCWCGSAFlAwQBKjAL
BglghkgBZQMEAQIwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3DQMCAgFA
MAcGBSsOAwIHMA0GCCqGSIb3DQMCAgEoMIGlBgkrBgEEAYI3EAQxgZcwgZQwgYwxCzAJBgNV
BAYTAklMMRYwFAYDVQQKEw1TdGFydENvbSBMdGQuMSswKQYDVQQLEyJTZWN1cmUgRGlnaXRh
bCBDZXJ0aWZpY2F0ZSBTaWduaW5nMTgwNgYDVQQDEy9TdGFydENvbSBDbGFzcyAxIFByaW1h
cnkgSW50ZXJtZWRpYXRlIENsaWVudCBDQQIDBNFnMIGnBgsqhkiG9w0BCRACCzGBl6CBlDCB
jDELMAkGA1UEBhMCSUwxFjAUBgNVBAoTDVN0YXJ0Q29tIEx0ZC4xKzApBgNVBAsTIlNlY3Vy
ZSBEaWdpdGFsIENlcnRpZmljYXRlIFNpZ25pbmcxODA2BgNVBAMTL1N0YXJ0Q29tIENsYXNz
IDEgUHJpbWFyeSBJbnRlcm1lZGlhdGUgQ2xpZW50IENBAgME0WcwDQYJKoZIhvcNAQEBBQAE
ggEAENYtLHpDh5Kh2R8LzQBy7tH+GfibxgYutkJrBrT4/IVU04i98JflMjZ2RHnIf20/0iGu
rzO+xJXYhVkO1VSWnXkO/9o0w0lJMgeMw6CRQ/O9y0NxuuTJqcY/puCE3g27Zir500+5bAcw
VsYOT9jQR5MeL9WzNIp1khllSFB4ybsbuUBE3IKdYZkwM4setq4TMGkbwIh/uuksVBzkkKSP
0p4jevcBpdwJzKmN0lwD9Kn4VhpRcoxoj+xzt8P6yQyYAOBr3ahp/SIKtEqVdp2HyFYahnge
5RyBIl7wR/BCvrg8I8BQhC2XazR1M9bp8f/9VxC9XCpK0vL1pCnXKsmYJwAAAAAAAA==
--------------ms000400090206040300060607--