what is the better free sql database to work with DI ?

dataservices performance
1

Hello

Working with BO XI R2, I want a free SQL database to obtain the best performance with Data Integrator.

The production environment works with SQL Server 2005/2008.
The tests showed that the data are transferred to ~ 16,000 rows per second.

With MySQL, the performance sucks a lot (only ~ 1500 rows per second).
In this case, MySQL is 10 times less efficient than SQL Server.

In practice, I think the mysql ODBC connector is a bottleneck in Data Integrator.

So, what is the better free sql database to work with Data Integrator ?

In advance
Thank you.

P.S.: (sorry for the bad english)

Glouferu :fr: (BOB member since 2010-02-24)

2

Would the free/light versions of SQL Server or Oracle – like SQL Server Express – work, in your scenario?

Still, they’re limited to small, low-powered development environments.

Chances are, if MySQL is that slow, it’s because the database needs tuning, or the hardware isn’t sufficient. MySQL can itself be quite fast.

dnewton :us: (BOB member since 2004-01-30)

3

The ODBC driver of MySQL has quite some overhead. So even if MySQL itself is fast, using it with DS might not.

Werner Daehn :de: (BOB member since 2004-12-17)

4

Indeed, ODBC driver of MySQL is not efficient.

Now, i trying to bypass the limitations of sql server express… because 4giga by database is not enough (but i do not like this solution).

Glouferu :fr: (BOB member since 2010-02-24)

5

Are you using the latest MySQL 5.x ODBC driver, or the older 3.51 version?

Have you tried adjusting some of the connection settings at the ODBC level (such as “no cache”, “forward-only cursors”, etc.)?

dnewton :us: (BOB member since 2004-01-30)

6

I have a question regarding ODBC drivers settings.

We are using IBM Informix Dynamic Server Version 11.50.FC5W4X3 as a primary Source, we have install Native client for the same and we are using ODBC connection to select particular DB.

Is there some setting at ODBC level, which can give you the best performance. So far, we went with default settings, but we have around like 600GB of data to be transfer to Oracle 10g DB.

Any suggestions!

We are using BODI 11.7.3 on Windows Server 2003.

Thanks,

data_guy :us: (BOB member since 2006-08-19)

7

Hi,

I currently use the latest ODBC connector (5.x).
I have obviously tried to change the options (disabled trace,
looking for bulk copy option, etc. …).

Best Regards.

Glouferu :fr: (BOB member since 2010-02-24)

8

I’ve never tried PostgreSQL but perhaps that works better than MySQL? We’ve dabbled with MySQL but found the performance not good enough when compared to Oracle 10g.

ErikR :new_zealand: (BOB member since 2007-01-10)

9

With SQL 2008R2, an Express database can now be up to 10Gb.

dnewton :us: (BOB member since 2004-01-30)

10

Yes, i know… but 10giga it still not enough lol

I trying Postgreql database, but i have little problem with Data Source.
Data Integrator can find Postgresql tables but the queries are not following the correct syntax.

Example :

With Gui Postgresql Tools, we answer database such as :

SELECT "ID", textteeeuh  FROM public."TEST";

And data integrator try to execute the following query :frowning:

SELECT ID, textteeeuh FROM public.TEST

So data integrator need to add double quotes. Anybody got an idea ? :stuck_out_tongue:

edit : More information here about PostgreSQL on naming identifiers

Glouferu :fr: (BOB member since 2010-02-24)

11

Any new thoughts, on how to optimize this for better performance?

dnewton :us: (BOB member since 2004-01-30)

12

Luxury! I’m only able to achieve this dazzling 1000 rows per second performance if MySQL and BODS co-hosted.

Separate onsite server 100 rows a second
Separate cloud server 40 rows a second!

More details:
[list]
*BODS 4.0
*BODS DataStore 5.1
*MySQL ODBC 5.3
*MySQL Server 5.7
[/list]

Does anyone have any tuning advice for MySQL with BODS 4.0? I think later versions of BODS has a native driver but at the moment I’m having to use ODBC and it’s responding really badly. I don’t mind it being half as fast as Oracle but one twentieth is just ridiculous.

FreeZey (BOB member since 2007-12-07)

13

SAP HANA Express may be of help? It’s free up to 32GB of RAM and being a columnar store database, you can actually cram quite a bit of data into a 32GB HANA instance, especially with low cardinality data.

ErikR :new_zealand: (BOB member since 2007-01-10)

14

Sadly swapping to another technology isn’t an option. We’ve now made some progress into improving performance but it’s a long winded work around.

FreeZey (BOB member since 2007-12-07)

15

I only noticed that you are using BODS 4.0… that has been obsoleted and out of support for a long, long time. If your company is paying annual maintenance, you really ought to keep your DS environment up to date. That may also help with your problem?

E.g. BODS 4.2 SP08 supports MySQL 5.6 which may yield better performance than the old versions you are using now? SP09 has just been released but since SAP hasn’t updated the PAM yet, I cannot comment on any additional MySQL support.

ErikR :new_zealand: (BOB member since 2007-01-10)

16

I know almost anything about MySQL, but I do know that MySQL has by design the ability of changing the storage engine.

So you can try another engine instead of innodb if this apply in your case. And, of course, let us know the results.

Regards,

Andrés

aidelia :argentina: (BOB member since 2006-02-02)