Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2009-05-21 : 09:57:46
|
| HiI am using a SQL Server 2000 DTS package to transfer data from a Progress server to a Postgres one. As well as copying about a dozen tables there are two queries on the Progress data that are to be used to populate the Postgress tables.Most of the tables are pretty small, there are however two or three that hold a few hundred thousand records and one of the views generates about 3 million records.For my first attempt at this I copied all the tables to SQL server, then populated tables using the views based on the tables that I had moved to SQL server. I then exported the data to Postgres. This didn't generate too many issues and seemed reasonably successful.I then decided to cut out the storing of tables in SQL server as it seemed to make sense to copy the data directly from Progress straight into Postgres. This method, though much simpler than my original effort hit massive performance problems and I'm not sure why. It has taken nearly 3 hours to transfer 182000 records and that is just one table (not one of the views).Does anyone have any ideas why this is or what I may be able to do to obtain better performance? Am I going to have to return to using SQL server as an intermediate store? I don't really understand why there is such a difference between the two methods.Many thanks in advancesteve-----------ASCII and ye shall receive. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-21 : 10:41:27
|
| My guess is that the data providers you're using for Progress and/or PostGres do a lot of cursor operations under the covers, especially if you're using ODBC. DTS is optimized for SQL Server, so it can optimize that end of the transfer and take best advantage of the what the other data provider can do. If not, then it probably has to do a lot of extra translation.Have you tried using SSIS instead? It's a lot more provider-neutral and efficient, especially for parallel operations and large volumes. Also see if you can use other data providers, especially OLE DB if they're available. |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2009-05-22 : 04:52:20
|
| Thanks Rob that's very helpfulSadly I don't have SSIS available at the moment (and possibly not for some time yet). I'll have a look around for an ole db provider for PostGres. I understand that the provider I have for Progress is the only one available though I will check that is that case - I suspect there isn't much demand for one.thanks againsteve-----------ASCII and ye shall receive. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2009-05-22 : 09:15:38
|
| I've looked further into this and wanted to record my findings in case it should be of use to anyone elseI discovered that importing data from Progress into SQL server (2000) using our ODBC driver was not a major problem for us, it seemed reasonably efficient. We imported over 300000 rows in less than 15 minutesOur problem was that we were then using a PostGres ODBC driver to output the data to a remote PostGres server (in my initial more successful attempts I was using a PostGres server on the same box as the SQL server). It may be my lack of understanding that is the issue however for me this was an incredibly slow process - taking an hour to export around 80000 rows.We then tried the other way around ie we imported the SQL data into PostGres, this time using an OLE DB data provider (as Rob kindly suggested - in our case we happen to have an application on the PostGres server that allows us to import using this). The performance of this was much more acceptable and in fact faster than the import from Progress into SQL Server.So my final solution was to allow the PostGres server to exacute the DTS package, and then import from the SQL server. Not an ideal solution but it workssteve-----------ASCII and ye shall receive. |
|
|
|
|
|
|
|
|
Long and complicated