New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Improve batching performance on Oracle #469

Closed
oehme opened this Issue Aug 2, 2013 · 9 comments

Comments

Projects
None yet
3 participants
@oehme
Contributor

oehme commented Aug 2, 2013

Since Oracle does not support getColumnType(int), querydsl-sql always uses the JDBC type NULL when setting null parameters. This significantly degrades batch insert/update performance. Here's why:

Let's take this simple statement:

INSERT INTO MYTABLE (my_varchar_column) VALUES (?)

When this statement is first parametrized with a string and then parametrized with a null, Oracle will not do a batch insert. Instead it will treat it as two different statements because of the different types (varchar and null).

Now you can imagine that this gets worse the more nullable columns you have, because pretty much each insert will be different. This gets to the point where performance is as low as with no batching at all.

I have worked around this by having a special Null type which remembers the Path it came from. The handler of this type then uses the (private) method of the Configuration class to ask for the correct handler for this Path's type and then asks that handler for its JDBC type. Pretty hacky, I know...

This is again something that could probably be solved with column metadata. This way, we could ask a path for its JDBC type and use that as a parameter for the Null value.

@lukaseder

This comment has been minimized.

Show comment
Hide comment
@lukaseder

lukaseder Aug 2, 2013

That's pretty interesting. Do you happen to know a link to a public benchmark explaining this behaviour?

That's pretty interesting. Do you happen to know a link to a public benchmark explaining this behaviour?

@oehme

This comment has been minimized.

Show comment
Hide comment
@oehme

oehme Aug 2, 2013

Contributor

No, but I have used sql_trace to look at what the database did with my inserts. When using setNull(index, Types.NULL), each statement was parsed and executed one by one. When using the correct JDBC type for each column, all statements were executed in a single batch.

Contributor

oehme commented Aug 2, 2013

No, but I have used sql_trace to look at what the database did with my inserts. When using setNull(index, Types.NULL), each statement was parsed and executed one by one. When using the correct JDBC type for each column, all statements were executed in a single batch.

@lukaseder

This comment has been minimized.

Show comment
Hide comment
@lukaseder

lukaseder Aug 2, 2013

The depths of JDBC... (sorry Timo, for hi-jacking. This was quite interesting)

The depths of JDBC... (sorry Timo, for hi-jacking. This was quite interesting)

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Aug 2, 2013

Member

@lukaseder no problem ;)

Member

timowest commented Aug 2, 2013

@lukaseder no problem ;)

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Aug 2, 2013

Member

Maybe it would be cleaner to move the null handling into the type dependent Type instances. This way the NullTypes can also be dropped and the column metadata doesn't need to be inspected.

Member

timowest commented Aug 2, 2013

Maybe it would be cleaner to move the null handling into the type dependent Type instances. This way the NullTypes can also be dropped and the column metadata doesn't need to be inspected.

timowest added a commit that referenced this issue Aug 2, 2013

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Aug 2, 2013

Member

The NullTypes have now been removed and the typed null setting is used, when the type is available. Could you try again with the latest SNAPSHOT from here https://oss.sonatype.org/content/repositories/snapshots/

Member

timowest commented Aug 2, 2013

The NullTypes have now been removed and the typed null setting is used, when the type is available. Could you try again with the latest SNAPSHOT from here https://oss.sonatype.org/content/repositories/snapshots/

@oehme oehme closed this Aug 5, 2013

@oehme

This comment has been minimized.

Show comment
Hide comment
@oehme

oehme Aug 5, 2013

Contributor

Works like a charm, thank you very much =)

Contributor

oehme commented Aug 5, 2013

Works like a charm, thank you very much =)

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Aug 5, 2013

Member

I'll close it, when it's released.

Member

timowest commented Aug 5, 2013

I'll close it, when it's released.

@timowest timowest reopened this Aug 5, 2013

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Aug 27, 2013

Member

Released in 3.2.3

Member

timowest commented Aug 27, 2013

Released in 3.2.3

@timowest timowest closed this Aug 27, 2013

@timowest timowest added this to the 3.2.3 milestone Apr 13, 2014

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment