June 22, 2020 at 3:13 pm
How to improve performance while loading 200,000 records data from sql server to postgres using ssis package
It's taking 2 hours time for loading records 200,000 from sql server to postgres
source sql server table: emp and fields :30 its mixed int,varchar,decimal daatypes
target postgres table : emp and fields :30 its mixed int,varchar,decimal daatypes
for performanc steps i followed like below:
default buffer size i have increase 10 mb to 30 mb
default rows: 10,000 rows to 30,000 rows
select required filed in the source select statement using oledb source
destination i have using odbc destination for configure the postgres table
no data types changes while loading sql server to postgres we have maintain exact sql server and postgres dbs. its one to one loading (sql server to postgres no business logic)
in ssis using: oledb source for sql server tables using select statement and select required columns odbc destination for postgres tables and select destination table and created ado.net connection for dsn configure.
we can do bulkinsert task using ssis ,but its support only text files to sql server tables.
same thing can we do sql server to postgres tables loading using bulk insert task.
can you please tell any solution to reduce loading time from sql server to postgres loading using ssis package.
June 22, 2020 at 3:30 pm
I do "love" a cross post.
Like I mentioned before, SSIS isn't likely to be your bottleneck here; either the INSERT or SELECT are, or the bandwidth between the two servers. 200,000 rows is trivial for a Bulk Operations and should complete in a matter of seconds at most unless there's some serious blockers.
Is the SELECT statement fast on the PostgreSQL server? If not, there's your candidate to start.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 22, 2020 at 8:39 pm
I agree with Thom. I want to add another point that MIGHT be a cause for slowness - memory. If the package is a simple data flow task that just moves data from the source to the destination, memory is unlikely (but not impossible) to be a bottleneck.
SSIS operates in a different memory space than SQL Server, so if the VM/physical machine running your SSIS package has very little memory available when the package starts, it is going to be doing a lot of paging to disk which is a slow operation. If this is a simple SELECT and INSERT, you likely are not having memory issues (not impossible, but a lot less likley). But if you are doing anything on the data prior to inserting (sorting, lookups, etc), you MAY have memory issues.
I agree with Thom's approach to troubleshooting this - test the SSIS package doing JUST the select and see how long it takes. If the SELECT is slow, then that is where you'd start. You may benefit from some indexes or possibly the SELECT query can be rewritten to get better performance (personally, I'd try to rewrite before throwing indexes at it as they have a cost too) or you may just have a LOT of data per row - VARCHAR(MAX) for example can hold 2 GB of data. If the select completes in a short period of time, then the insert is the bottleneck. Assuming you have no steps between the two.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply