Hi Dominik, That's curious. jOOQ uses setBytes() behind the scenes, assuming the blobs are "reasonably" small. For large LOBs, you generally have to either bypass jOOQ or use your own custom data type binding. Though, since you already have a byte[] in memory, I don't really see why binding the byte[] should be so much slower than binding the stream. The pgjdbc driver could implement the same logic. Have you profiled it, e.g. using VisualVM? Might be a pgjdbc bug.
The solution is to use a data type binding. Starting from jOOQ 3.15, you can also attach an ad-hoc binding to your DOCUMENT_STORAGE.CONTENT column, by calling Field.convert(binding) I hope this helps, Lukas On Fri, Jul 16, 2021 at 10:23 AM '[email protected]' via jOOQ User Group <[email protected]> wrote: > > Hi, > > I have a table defined by > > CREATE TABLE document_storage ( > id INTEGER NOT NULL, > content BYTEA, > PRIMARY KEY (id) > ) > > The DB Type BYTEA is used by the generated source files as byte[]. > > The insert of 70MB content needs >40s via JOOQ records, which sems to be > way too long. > > DocumentStorageRecord storageRecord = jooq.newRecord(DOCUMENT_STORAGE); > storageRecord.setId(documentRecord.getId()); > storageRecord.setContent(content); > storageRecord.store(); > > If I use the example from the postgres jdbc docs, than the row is writen > in <2s > > ByteArrayInputStream is = new ByteArrayInputStream(content) > PreparedStatement ps = conn.prepareStatement("insert into document_storage > (id, content) values (?, ?)"); > ps.setInt(1, id); > ps.setBinaryStream(2, is, content.length); > ps.executeUpdate(); > > I've try to configure a <forcedType> BINARY for that column but the result > was the -expected- same type byte[] within the generated sources. > > What is needed to have the same performance as in the plain jdbc example ? > > Kind reagds > Dominik > How can I > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/1022010e-3f01-40af-8210-2ea4e9b2876an%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/1022010e-3f01-40af-8210-2ea4e9b2876an%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO5Xbjd8FarhtOPqe9goaDPpFNcYOJNpsayonBMtdAL6Dg%40mail.gmail.com.
