Hi,
I'm facing performance issue when inserting data from SSIS to OE DB on Linux OS. I'm looking for optimal value (& ratio among them) for network related startup parameters, like -.
--prefetchDelay
-prefetchNumRecs
-prefetchFactor
-prefetchPriority
- Mi, Mn, Ma & Mm
- Any another parameter or way to improve performance.
Just to give an idea when we are inserting to an SQL Server DB insertion rate is one million records per 7 minutes but when OE DB (on Linux) is the target then the rate is 1400 records per 7 minutes.
Any help will be appreciated.
Thanks in advance!
Regards,
Dinesh
NONE of these parameters will help you when inserting data from MSSIS to OE DB. These are mostly for 4GL clients. The prefetch stuff is only for NO-LOCK queries by 4GL clients and the -Mi/-Ma/etc params won't help because _sqlsrv2 is multi-threaded.
Your 1M inserts per 7min is probably not using 1 million distinct INSERT statements so I doubt you're comparing apples to apples.
I suspect you'll need to look at other solutions like ETL.
To improve insert performance you need to start by looking at things like:
- bi cluster size
- bi and ai block size
- number of bi buffers and ai buffers
- the number of APWs that are running
- the characteristics of your storage areas
- the capabilities of the underlying disk subsystem
Hi,
We analyzed more on this & find out that commit size is the only difference between SS & OE. for SQL Server commit size is specified over 2 million, while for OE there is no option to specify commit size. when we set commit size for SS to one then it also behaves similar to OE..
Is there any way to specify commit size or bulk insert with OE?
Thanks,
Dinesh
Commit size is controlled by the client (not the database).. this is true for SQL Server, OE, Oracle, etc.
Using SSIS has some quirks because it is expecting your ODBC driver to support certain functions to take advantage of bulk loading options. With SQL server it will use a variety of bulk load methods to speed up the loads. It doesn't have that same level of knowledge for OE.
All that being said... download ProTop to take a look at what is happening on the OE database side while the loads are running. Odds are pretty good that either your DB isn't tuned or your underlying disk is very very very slow.
With any kind of decent modern hardware you should be loading 1400 records a second (or more) depending on the width of your rows and the quality of your network connection between the hosts.
Hi Keith,
SSIS is our local laptop in Hyderabad office & Openedge m/c is at Bedford office.from Hyderbad office to bedofrd there is dedicated internet line & internet speed is also around 300 MBS. DB side only table with one primary index on Linux OS. So, I don't think network would be an issue. If you want any specific answers then I can ask our Infra team for it.
What is the parameter for commit size in OE? I'll download the Protop & send you details of what I found there.
Thanks,
Dinesh
I am pretty sure the connection is at least part of your issue (maybe a lot of it). Even with dedicated lines you are still going halfway around the world and you aren't going to get a 1ms round trip from there. Bandwidth matters very little.. latency is your number one enemy with networks... how long it takes for a packet to get from your laptop to the OE database and back.
Again... there is no database parameter for commit size in OE (or SQL Server,Oracle,Postgres,etc). It is entirely handled by the SQL or OE client.. in your case SSIS. Commit size is just a way of saying how many records do I insert before I issue a commit.
This may seem rude.. but since you are a PSC employee don't you have internal support to help you with these kind of issues?