copy certain tables from Progress to MS SQL - Forum - OpenEdge RDBMS - Progress Community

copy certain tables from Progress to MS SQL

 Forum

copy certain tables from Progress to MS SQL

  • I have the following thread and it was suggested I ask the question over here

    http://www.psdn.com/library/thread.jspa?messageID=32873&#32873

    I am having database issues with Progress and the application vendor doesn't want me to add indexes to the tables so that I can run the needed reports. My solution is to move the 11 needed tables to the a MS SQL database and index how ever I want. Problem is getting that data out of Progress. Since there is no index on date, I can't select off only records by date. I am now thinking about running an export on Progress for the 11 tables to a CSV file. I am not a Progress person so in the professional opinions here, what are my best choices? Using ODBC and a DTS transacation in SQL Server, moving the data is too slow. Will an export to a CSV be fast? How do I go about setting that up?

  • As noted in the other thread....

    You get 20 minutes to count 450K records.

    I get 45 seconds to count 1.9M records.

    This is not an indexing problem. This is a problem about the setup of the database in some way. If you only ever needed to do this once, moving the data to SQL Server could be one way to avoid having to fix the problem, but if this is an on-going requirement, you really would be better off addressing the issue directly instead of continuing to think about this as an export problem. If your tables all have less than a half million records, you may well not even need any indexes to have acceptable performance ... **if you fix the problem**

    So,

    1. Update statistics

    2. Up the fetch limit on the ODBC connection

    3. Find the start up parameters for the database and tell us what they are

    4. Look at the performance on both boxes and tell us about CPU vs disk access at various stages.

    5. Tell us about the Progress version, hardware configuration, etc.

    6. Try running the same query from the server box.

    7. Try ABL equivalents of the query, if you have a development license.

    8. Tell us anything else you can about different tests.

    And we will help you fix the problem. OK?

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • My

  • -B isn't my first guess since a scan of a large table probably means disk reads anyway. But, clearly something is wrong ... we just need some clues.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • I do appreciate the help that everybody has provide and also the help that people were willing to provide. I did the easiest thing and I am having the software vendor's DBA look at the database.

  • Don't be shy about sharing the results. I am interested in what helps and there may be additional ideas from this community.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • I will be sure to share if I get any results. So far my request for an indepth analysis has provide "increase -B maybe if possible" and "run update stats". Nothing more.

    BTW, is there a prefered backup software for a Progress database?

  • The preferred backup software for Progress is that supplied by Progress. NO other software will guarantee a good backup. Many people use probkup to backup to disk and then use their system backup software to back up that image to some other medium.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • I would be happy to run the query but I am currently using access and/or MS query. Neither can run it. Let me see if I can find a copy of WinSQL

  • WinSQL won't help. That is an ABL query. You need to get into the Progress Procedure Editor to run it.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • The preferred backup software for Progress

    is that supplied by Progress. NO other software will guarantee a

    good backup.

  • There is a wealth of information right here on PSDN. Try searching for "database AND performance" or "database AND tuning" and you'll find so great documents like these:

    http://www.psdn.com/library/entry!default.jspa?externalID=1407

    http://www.psdn.com/library/entry!default.jspa?externalID=845

    Don't be at all concerned if you find stuff relating to an earlier version of OpenEdge/Progress than you're currently running. All the old performance rules are still exactly the same. Newer versions bring new features, but they tend to be most useful for systems that are reasonably well tuned already. YMMV.

  • Actually, I strongly recommend against even using regular backup utilities for off line backups. Only the Progress backup is doing a backup that is intelligent about the structure of the database. Checks are made during the backup which can provide an early alert to issues and there are options for restoring which simply don't exist with an OS type backup. probkup to disk is very fast. Then, backup the resulting file by your preferred method. Online or offline as needed.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • Is there a windows version of "Progress Procedure Editor"?

  • Yes, if you have a Windows license for any of the development products. But, one of the tests I have suggested that you run is to try both ABL and SQL versions of the query on the server box, i.e., get the network out of the equation.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com