In spring batch, slow process time because of SQL SERVER, even if its only defined in application.yml

Question

I have a simple spring batch application.

If I use H2 for the DB - add it in the .pom and clear the application.yml, the application processes 50,000 records in 8 seconds. And when I say processes I mean only counting the time a class that extends ItemProcessor is called, while most of the class that extends ItemWriter is commented out (not called).

If I want to use SQL Server, I add this to the .pom:

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>sqljdbc</artifactId>
    <version>6.0.0</version>
</dependency>

And define like this the application.yml:

spring:
  profiles:
    active: dev

---

spring:
  devtools:
    add-properties: false
  profiles: dev
  datasource:
    url: jdbc:sqlserver://***.restest.bank:***;DatabaseName=****;integratedSecurity=true
    driver_class_name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
#    hikari:
#      maximum-pool-size: 50
#      connection-test-query: SELECT 1
#      validation-timeout: 30000
#      minimum-idle: 5
#      max-lifetime: 300
  jpa:
    database: SQL_SERVER
    database-platform: org.hibernate.dialect.SQLServerDialect
    show-sql: false
    hibernate:
      ddl-auto: update
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
    properties:
      hibernate:
      default_schema: dbo
      default_catalog: ****
  batch:
    initialize-schema: always

The application works terribly slow! To process the same records, it takes 700 seconds, even if most of the class that extends ItemWriter is commented out!

Why?

Update: Now I see that even though I don't write to the DB, spring does, to the tables BATCH_JOB_*.

Could this be what slow the app down so much? Because its not reasonable that its so slow!

I am not quite sure I understand your question. Are you asking why disk access is slower than RAM access or are you asking why network access is slower than RAM access? — Turing85, Jul 19 '20 at 09:27
Neither - I DONT ACCESS THE DB - SQL SERVER OR H2. Everything is in comment. The only place that has Sql Server definition is the application.yml. — Ran, Jul 19 '20 at 09:36
That's interesting... Can you verify that you do not hit the database by removing it completely? — Turing85, Jul 19 '20 at 09:41
Almost the entire code is in comment (except a log line that prints the time) . If I remove it from the YML, it uses H2, and with it it is fast. — Ran, Jul 19 '20 at 10:15
Yes, but can you remove the database configuration completely, just to verify that the database is **not used at all**? — Turing85, Jul 19 '20 at 10:16
What configuration do you mean? The code is in comment, and if I remove it from the yml, it uses H2 and works fine. — Ran, Jul 19 '20 at 10:48

score 0 · Answer 1 · answered Jul 19 '20 at 12:55

0

Even thouhh I didn't insert anything to the DB - spring did. And it inserted into the DB to the tables BATCH_* really really slow. After using this answer to create in memory tables - its working fine now.

answered Jul 19 '20 at 12:55

Ran

657
2
13
29

In spring batch, slow process time because of SQL SERVER, even if its only defined in application.yml

1 Answers1