ODBC Queries VERY slow

D

dnova

New Member
Hello,

I am working with Progress 9.1D and accessing from Merant 3.60 ODBC driver from C#, things go quite quickly most of the time, but I often find specific queries (both single table and 2 table join) can take horrifically long times to run. I decided to take out this post after waiting 20 minutes for one. Unfortunately I cannot change much about this database as it is the core of a deployed accounting system, but I would like to know where I can get some info on optimization. I am using a System DSN read-uncommited and filtering on a primary Key in the first of these tables. I hear many people say how they love progress, but I have experienced rather poor performance and would like to know what I can do to change this. The server's utilization never goes very high yet I wait and wait for query results. These tables only have a few million records at most and only a few thousand I care about for results. Also, I am using a read only forward only cursor (OdbcDataReader). Any advice or leads to good resources please, this lag is KILLING me.

Cheers,
-Dab
 
Hi,

Is there a way to test your query on the progress UI. What kinda timing do you get when you get all the million rows and when you filter out 1000s of rows. (Quote:These tables only have a few million records at most and only a few thousand I care about for results). change you conditions and see how it performs based on what indexes you have on those tables.

HTH
 
Hello, thanks for the reply! I run the query from SQL Explorer (the Progress one that is) and it can take as little as 30 second or as long as 10 minutes to run. I get the same speed from my code. Now there are about 100 people using the database.

I am mainly working on two tables right now, parent child sort of, one has a primary key (the parent) the child has only indexes and visio tells me they are I3, I2, I1 for each of them (I am not sure what this means). I am using { NO REORDER } on the join and it seems to improve speed, so does using more than 1 index. I am filtering on a field in the child table, so this first query should return quite quick, but it doesn't. I am using a Forward Only Read Only Cursor (OdbcDataReader) and my System DSN is Read-Uncommited. This query should really be quite forward from a SQL standpoint, my where clause specifies the logical entity I want (a field in the child table). I guess I really need an understanding of what is different about Progress and SQL and why are my SQL queries SO slow.

Also, is this 3.60 Merant driver an STA COM component, which I fear, meaning will I need to multi-thread in order to have concurrent connections. I use this ODBC connection for a nightly process and it runs lightning fast on other tables, they must be better indexed.

Is there a resource with more info on how to make this run faster? Should I not use a cursor and just rip my records off in a memory resident data table (it's a .NET object). This object will store millions of records so I could just read in the whole table and filter in memory / in .NET. Any help would be greatly appreciated.

Cheers,
-Dan
P.S. Platform is windows 2000 Server and .NET 1.1
 
Hi Dan,

you say you are developing in C#, do you know how to call produres wrote in progress from your .net application?
 
You must log in or register to reply here.
Back
Top