copy certain tables from Progress to MS SQL

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?

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.

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.

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.

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.

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.

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.

Any experienced 4GL person could quickly put together a CSV export. If you run it self-service on the server it should be faster than an ODBC connection over the network.

The basic code would be something like this:

output to "tableName.csv".

for each tableName no-lock:

export delimiter "," tableName.

end.

output close.

We ran an update Statistics over the weekend and no improvements. A simple sort of a table on the primary key in decending ran for 5 minutes (over ODBC) and never returns data. Here are my startup parameters. Any suggestions

Progress OpenEdge Release 10.0B build 1475 SP05 on AIX irms-x 3 5.

Physical Database Name (-db): /irms/db/irms.

Database Type (-dt): PROGRESS

Number of Database Buffers (-B): 100000

Maximum private buffers per user (-Bpmax): 64

Excess Shared Memory Size (-Mxs): 16472

Current Size of Lock Table (-L): 8192

Hash Table Entries (-hash): 25621

Current Spin Lock Tries (-spin): 6000

Number of Semaphore Sets (-semsets): 1

Database Blocksize (-blocksize): 8192

Before-Image Cluster Size: 2097152

Before-Image Block Size: 8192

Number of Before-Image Buffers (-bibufs): 5

Number of After-Image Buffers (-aibufs): 5

Maximum Number of Clients Per Server (-Ma): 10

Maximum Number of Servers (-Mn): 12

Minimum Clients Per Server (-Mi): 1

Maximum Number of Users (-n): 301

Host Name (-H): irms-x

Service Name (-S): 4000

Network Type (-N): TCP

Maximum Servers Per Broker (-Mpb): 5

Started for 4001 using TCP, pid 23166

This is an additional broker for this protocol

This broker supports SQL server groups only

1) What was the actual query that you ran?

2) What is the table definition (including indexes) for the data involved in the query?

3) What was the full UPDATE STATISTICS command that you executed?

4) Did you happen to collect any performance metrics, other than the overall run time, while the query was running?

5) Have you considered obtaining professional assistance?

6) Have you looked in the manuals for the parameters starting with SQL?

7) Can you describe the system, disks, and the layout of the database on the disks?

8) Have you had someone run the equivalent ABL query?

See #5 if you are in a hurry.

I am going to find a Progress DBA to take a look. I simply posted the startup parameters because somebody asked to see them.

I agree. Tuning and DBA are 2 different things. while somebody that can tune is probably a DBA not all DBAs can tune.

Can anybody suggest what would best place to find a tuner?

Of the top performance experts in the Progress world, one of them is contributing to this thread ... and it isn't me. White Star and Bravepoint are two frequently mentioned firms.

madams2 wrote:

Advertise on the PEG? http://www.peg.com/jobs/index.html

I'm sure Tom can suggest someone as well.

Gee ... and I thought he was referring to me. I don't see Mike wanting to pay to fly me over from Switzerland though.

Well we don't really actually know where he's posting from do we? But given today's exchange rate those of us who are based in the US must be looking awfully cheap if he's based in Europe

I am in NJ

You have our sympathy ...

Gee ... and I thought he was referring to me. I

don't see Mike wanting to pay to fly me over from

Switzerland though.

You could WebEx over....