Best practices for better performance

[mhamedbenjmaa]

hello all

just wondering , I moving from SSIS to hop

in ssis 5 million rows from Sybase, to SQL Server on fastload (truncate / insert ) using jtds driver

I changed this variable on the hop-gui.bat HOP_OPTIONS=-Xmx42048m

I changed the commit to 50000

in SSIS the load is 2min 11, in hop its 3min 11 ,its not much but i have 50 tables ....:/

just asking if there anything else i can do ; I know hop but this is first real prod implementation so, i want to impress the audience

[dmainou]

https://hop.apache.org/manual/latest/workflow/actions/mssqlbulkload.html

[hansva]

Also, why use the JTDs driver unless it is a very old sqlServer

[mhamedbenjmaa]

Jtds is for sybase

[dmainou]

There is a jdbc driver... use it

[mhamedbenjmaa]

which one? internet says that offcial sybase SAP jdbc driver is not online you need to ask SAP

[mhamedbenjmaa]

btw the driver proposed by hop is jtds , and if you put the real jconn4.jar from sybase , its not detected by hop , its that a bug ? com.sybase.jdbc4.jdbc.SybDriver is not seen as driver

[mhamedbenjmaa]

and this is wierd since if you ask dbeaver to download the sybase driver it will propose both jtds and jconn4 ... so the official driver must be out somewere

[hansva]

We can only jump on the information you provide...

In the initial discussion you mentioned:

from Sybase, to SQL Server on fastload (truncate / insert ) using jtds driver
This makes us assume JTDs is used on both sides... Using the JTDs driver for sybase is perfectly fine, I think that is still the recommended approach.

Working on performance is a special art in its own, there is no single switch that will make or break performance.

Firstly, how did you execute it, is it running on a similar server to where SSIS is running? Or are we comparing a server instance running inside the datacenter next to the database vs your local desktop?

Did you run via hop gui or CLI (the GUI adds a bit of overhead)?

What is the actual bottleneck? It's nowhere mentioned in your post. What's the slowest part? Where are your rows waiting? What does your pipeline look like?

If it is the output to SQL Server, I still have bulk loader code around here somewhere; maybe I should add that transform.

Lastly, comparing on performance alone is not a good idea. My graduation essay (a long time ago) was a comparison between SSIS and Kettle. Result... Kettle could not beat a tool specifically built to only work for SQL Server on database performance (SHOCKER). Who would have thought that an integration tool designed for SQL Server would have excellent performance against SQL Server?

But it was more flexible, simpler to use, and cheaper, so overall it beat SSIS.

[mhamedbenjmaa]

actually its just an input -> output nothing in between
now from what read i have one question and one suggestion

1. is it considered a bug if jconn4.jar is not recognized by hop a official driver for Sybase
2. what about we create a small doc the describe how make hop production ready
   a. upgrade your memory usage 2gig is by definition not enough for any production
   b. prefer using cli instead of gui
   c. config run ? any tweaks ? best practices ?
   d. how to set the audit ? best practices ?
   e. anything else ...?
   f. things that you MUST avoid ....

etc.... i dont know dose it sound like a good idea ? anyone using hop in production can contribute with his findings ......no ?

[hansva]

No, it's not a bug; our SybaseDatabaseMeta expects the JTDs driver. It could be a feature or improvement ticket.

We have a Best Practices section: https://hop.apache.org//manual/latest/best-practices/index.html
and an Installation and configuration section: https://hop.apache.org//manual/latest/installation-configuration.html

Contributions to improve the documentation, adding guides, adding samples, ... are all very welcome.
As I stated before in a ticket developers/experienced users are usually not the best people to write documentation because what is just natural for us could be really hard for new users, we just don't experience things through the eyes of a beginner anymore.

One more thing you could do if you really want to squeeze performance: we have an experimental feature...
You can turn on HOP_BATCHING_ROWSET in the system variables this is an experimental feature to reduce backpressure on rows passing between transforms.