Hi,
So i have two databases - one in openedge 10.2b on aix, one in SQLserver 2008 R2
Both databases contain the same data and have the same table structures.
The SQL database has no indexes.
The progress database does.
Performance on the progress database via odbc is somewhat lacking and becomes increasingly worse as queries become more complex.
For example, a simple query involving two or three tables, no grouping/ordering, returning 100 records or so will execute in approx 1 sec. This is about double the time taken to run the same query on the SQL db
However, when i increase the complexity to say 5 tables with multiple grouping and aggregation of 1million records, the odbc performance can be over 60 seconds, while the SQL db runs the query in 1-2 secs max.
I'm sure the main issue is that it's progress running SQL via ODBC so it will naturally have an overhead, however this perormance difference is not really acceptable. I am running UPDATE STATISTICS but not really seeing much beneift from this.
Any ponters on what else i could check/change to speed up query execution?
Cheers,
MrG
So i have two databases - one in openedge 10.2b on aix, one in SQLserver 2008 R2
Both databases contain the same data and have the same table structures.
The SQL database has no indexes.
The progress database does.
Performance on the progress database via odbc is somewhat lacking and becomes increasingly worse as queries become more complex.
For example, a simple query involving two or three tables, no grouping/ordering, returning 100 records or so will execute in approx 1 sec. This is about double the time taken to run the same query on the SQL db
However, when i increase the complexity to say 5 tables with multiple grouping and aggregation of 1million records, the odbc performance can be over 60 seconds, while the SQL db runs the query in 1-2 secs max.
I'm sure the main issue is that it's progress running SQL via ODBC so it will naturally have an overhead, however this perormance difference is not really acceptable. I am running UPDATE STATISTICS but not really seeing much beneift from this.
Any ponters on what else i could check/change to speed up query execution?
Cheers,
MrG