BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: No events within the next 45 days.

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

Goto page 1, 2  Next
 
Search this topic... | Search DI: Performance and Tuning... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Author Message
Glouferu
Forum Member
Forum Member



Joined: 24 Feb 2010

Posts: 11


flag
PostPosted: Thu Jun 24, 2010 8:39 am 
Post subject: what is the better free sql database to work with DI ?

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)
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3458
Location: Massachusetts, USA


flag
PostPosted: Thu Jun 24, 2010 5:39 pm 
Post subject: Re: what is the better free sql database to work with DI ?

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.
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10591
Location: Germany


flag
PostPosted: Fri Jun 25, 2010 12:59 am 
Post subject: Re: what is the better free sql database to work with DI ?

The ODBC driver of MySQL has quite some overhead. So even if MySQL itself is fast, using it with DS might not.
_________________
Werner Daehn
Product Manager Data Services
SAP AG
�The opinions expressed in this post are my own views and not those of SAP.�
Back to top
Glouferu
Forum Member
Forum Member



Joined: 24 Feb 2010

Posts: 11


flag
PostPosted: Fri Jun 25, 2010 2:55 am 
Post subject: Re: what is the better free sql database to work with DI ?

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).
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3458
Location: Massachusetts, USA


flag
PostPosted: Fri Jun 25, 2010 6:41 am 
Post subject: Re: what is the better free sql database to work with DI ?

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.)?
Back to top
data_guy
Forum Associate
Forum Associate



Joined: 19 Aug 2006
ASUG Icon
Posts: 723
Location: Somewhere EveryWhere!


flag
PostPosted: Sat Jun 26, 2010 6:55 pm 
Post subject: Re: what is the better free sql database to work with DI ?

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 is Delicious

++++++++++++++++
Where you see risk,
I see opportunity!
Back to top
Glouferu
Forum Member
Forum Member



Joined: 24 Feb 2010

Posts: 11


flag
PostPosted: Mon Jun 28, 2010 3:55 am 
Post subject: Re: what is the better free sql database to work with DI ?

dnewton wrote:
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.)?




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.
Back to top
ErikR
Forum Enthusiast
Forum Enthusiast



Joined: 10 Jan 2007

Posts: 1111
Location: Wellington, NZ


flag
PostPosted: Mon Jun 28, 2010 6:06 am 
Post subject: Re: what is the better free sql database to work with DI ?

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.
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3458
Location: Massachusetts, USA


flag
PostPosted: Wed Jul 07, 2010 3:17 pm 
Post subject: Re: what is the better free sql database to work with DI ?

Glouferu wrote:
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).


With SQL 2008R2, an Express database can now be up to 10Gb.
Back to top
Glouferu
Forum Member
Forum Member



Joined: 24 Feb 2010

Posts: 11


flag
PostPosted: Thu Jul 08, 2010 8:21 am 
Post subject: Re: what is the better free sql database to work with DI ?

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 :
Code:
SELECT "ID", textteeeuh  FROM public."TEST";


And data integrator try to execute the following query icon_sad.gif
Code:
SELECT ID, textteeeuh FROM public.TEST



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

edit : More information here about PostgreSQL on naming identifiers
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3458
Location: Massachusetts, USA


flag
PostPosted: Sat Oct 22, 2011 7:23 pm 
Post subject: Re: what is the better free sql database to work with DI ?

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


Any new thoughts, on how to optimize this for better performance?
Back to top
FreeZey
Senior Member
Senior Member



Joined: 07 Dec 2007

Posts: 67



PostPosted: Thu Sep 01, 2016 6:48 am 
Post subject: Re: what is the better free sql database to work with DI ?

Glouferu wrote:

With MySQL, the performance sucks a lot (only ~ 1500 rows per second).

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:

    *BODS 4.0
    *BODS DataStore 5.1
    *MySQL ODBC 5.3
    *MySQL Server 5.7


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.
Back to top
ErikR
Forum Enthusiast
Forum Enthusiast



Joined: 10 Jan 2007

Posts: 1111
Location: Wellington, NZ


flag
PostPosted: Wed Oct 12, 2016 5:46 pm 
Post subject: Re: what is the better free sql database to work with DI ?

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.
Back to top
FreeZey
Senior Member
Senior Member



Joined: 07 Dec 2007

Posts: 67



PostPosted: Mon Oct 24, 2016 9:56 am 
Post subject: Re: what is the better free sql database to work with DI ?

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.
Back to top
ErikR
Forum Enthusiast
Forum Enthusiast



Joined: 10 Jan 2007

Posts: 1111
Location: Wellington, NZ


flag
PostPosted: Sun Jun 04, 2017 6:45 pm 
Post subject: Re: what is the better free sql database to work with DI ?

FreeZey wrote:
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.


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.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Page 1 of 2 All times are GMT - 5 Hours
Goto page 1, 2  Next
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0306 seconds using 17 queries. (SQL 0.0025 Parse 0.0010 Other 0.0272)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo