Extremly slow using Progress and SQL-Database

JorgenKatz

New Member
Stamford has made a product called CCM that has been developed for 10 years (current version: OE 11.6).
Data resides in two SQL databases (Microsoft) and one DB2 database.
The application works mainly against the database "CCM-SQL".
The front-end (.html) code is written in progress code / webspeed and the back-end code is written in progress.

Calls to "CCM-SQL" are made via Schema Holder.
The Schema Holder uses an ODBC source (ODBC Data Source, 64 bit) to connect to "CCM-SQL".

Users and developers perceive the application as very slow. (The application has never been fast)
This applies to both front-end and back-end programs.

Developers have modified the progress code according to proposals from Progress, it does not make much difference.
docs.progress.com/bundle/openedge-microsoft-sql-dataserver-117/page/Writing-queries-for-performance.html

Technicians have modified parameters in progress and SQL, nor has it made any differences.

Is there anyone who works in a similar set and who can share experiences?
Or someone who has something that can help us and help our customer?
(At Stamfor there is good experience of Progress, we have developed in Progress for over 20 years.)
 

JorgenKatz

New Member
Thanks Tom!
My colleagues and I do not think that the code is the bottleneck.
Well, the code can of course be improved a bit, which we did. But it has not added much.
I enclose an simple example of the difference between SQL and Progress Question.
SQL is 16 times faster than Progress.

(We have not run profiler on any program. Yet :) )


Code:
//the table to be filled with the result of SQL-query
DEF TEMP-TABLE tt_item_price
    FIELD ITEM_price_obj LIKE ITEM_price.ITEM_price_obj
    FIELD agreement_obj LIKE agreement.agreement_obj
    FIELD ITEM_obj LIKE ITEM.ITEM_obj.

DEFINE VARIABLE ii       AS INTEGER     NO-UNDO.
DEFINE VARIABLE cSQL     AS CHARACTER   NO-UNDO.
DEFINE VARIABLE ttHandle AS HANDLE      NO-UNDO.
DEFINE VARIABLE dd       AS DATETIME    NO-UNDO.


ttHandle = TEMP-TABLE tt_item_price:HANDLE.
//SQL-query, similar to progress query
csql =
"select item_price_obj, agreement.agreement_obj, item.item_obj
from item_price
join item on item.item_obj = item_price.item_obj
join agreement ON agreement.agreement_obj = ITEM_price.agreement_obj
where item_price.item_price_fromdate = '2021-04-01 00:00:00.000' 
and item_price.item_price_status_code <> '9'
and item.item_status_code <> '9'
and agreement.agreement_status_code <> '9' "
 .


//runs the SQL-query
ETIME(YES).
DO TRANSACTION:
    RUN STORED-PROC ccmsql.send-sql-statement LOAD-RESULT-INTO ttHandle (cSQL) .
END.

//loops the result of the query
FOR EACH tt_item_price NO-LOCK.
    ii = ii + 1.
END.
MESSAGE ii STRING(INT(ETIME / 1000),"HH:MM:SS") VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
//ii = 143.382, time = 2 seconds


dd = datetime(DATE("20210401")).
ii = 0.
ETIME(YES).
FOR EACH agreement FIELDS(agreement_obj) WHERE agreement.agreement_status_code <> '9' NO-LOCK,

    EACH ITEM_price FIELDS (item_price_obj item_obj)
                    WHERE item_price.agreement_obj          = agreement.agreement_obj AND
                          item_price.item_price_fromdate    = dd AND
                          item_price.item_price_status_code <> '9' NO-LOCK,

    FIRST ITEM FIELDS (item_obj )WHERE item.item_obj          = item_price.item_obj AND
                                       item.item_status_code <> '9' NO-LOCK.
    ii = ii + 1.
END.
MESSAGE ii STRING(INT(ETIME / 1000),"HH:MM:SS") VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
//ii = 143.382, time = 37 seconds
//agreement has 10889 records in the database
//item_price has 19103933 records in the database
//item_price has 1213806 records in the database


//index in the tables agreement, item_price, item_obj
/*
Table: agreement

Flags Index Name St Area Cnt Field Name
----- ----------------------- ------- --- ----------------------
agreement_change_date_i N/A 1 + agreement_change_dat

agreement_id_idx N/A 1 + agreement_id

agreement_locked_shelf_ N/A 1 + agreement_locked_she

agreement_lock_status_i N/A 1 + agreement_lock_statu

agreement_performance_i N/A 2 + agreement_valid_from
+ agreement_valid_to

agreement_quarantine_id N/A 1 + agreement_quarantine

agreement_send_date_idx N/A 1 + agreement_send_date

agreement_template_flag N/A 1 + agreement_template_f

agreement_template_obj_ N/A 1 + agreement_template_o

branch_obj_idx N/A 1 + branch_obj

ccm_idx N/A 1 + agreement_ccm_flg

export_trygg_flg N/A 1 + export_trygg_flg

pu pidx N/A 1 + agreement_obj

u PK_agreement_obj N/A 1 + agreement_obj

resale_idx N/A 2 + agreement_resale_flg
+ agreement_resale_ven

skapatisql_parent1 N/A 2 + agreement_parent1_ob
+ agreement_status_cod

skapatisql_parent2 N/A 2 + agreement_parent2_ob
+ agreement_status_cod

status_code_idx N/A 1 + agreement_status_cod





Table: item_price

Flags Index Name St Area Cnt Field Name
----- ----------------------- ------- --- ----------------------
agreement_comp_idx N/A 6 + agreement_obj
+ item_obj
+ item_price_bracket
+ item_price_delivery_
- item_price_fromdate
+ item_price_status_co

agreement_idx N/A 1 + agreement_obj

fromdate_desc_idx N/A 1 - item_price_fromdate

item_idx N/A 1 + item_obj

item_price_change_date_ N/A 1 + item_price_change_da

item_price_net_flg_idx N/A 1 + item_price_net_flg

item_price_parent_idx N/A 1 + item_price_parent_ob

pu pidx N/A 1 + item_price_obj

status_code_idx N/A 1 + item_price_status_co



Table: item

Flags Index Name St Area Cnt Field Name
----- ----------------------- ------- --- ----------------------
brand_idx N/A 1 + brand_obj

expire_idx N/A 1 + item_expiering

item_id_idx N/A 1 + item_id

item_market_code_idx N/A 1 + item_market_code

item_name2_idx N/A 1 + item_name2

item_name_idx N/A 1 + item_name

item_replaced_by_idx N/A 1 + item_replaced_by

item_subclass_idx N/A 1 + item_subclass_obj

item_type_idx N/A 1 + item_type

pu pidx N/A 1 + item_obj

u PK_item_obj N/A 1 + item_obj

skapatisql_item_status_ N/A 2 + item_obj
+ item_status_code

status_code_idx N/A 1 + item_status_code

vat_code_idx N/A 1 + item_vat_code

vendor_idx N/A 1 + vendor_obj
*/
 

Attachments

  • ToProgressForum.p
    5.9 KB · Views: 1
Last edited by a moderator:

Patrice Perrot

New Member
Hi Jorgen,

Could you give us the SQL Plan
(I think it will begin by ITEM_price with index "fromdate_desc_idx").

With Progress You are using the folowing index
- agreement Index pidx WHOLE-INDEX
- ITEM_price Multiple index (Index "agreement_idx" + Index "fromdate_desc_idx" )
- Item Index "pidx"

If i am correct on the SQL PLAN, You can re-write your query to begins by the table ITEm_PRICE and join the otrher table .
===> The "item_price_fromdate = dd " should be a restritive equality....

If you want to avoid the Whole index on agreement you can modify "agreement.agreement_status_code <> '9' " by "agreement.agreement_status_code < '9' Or agreement.agreement_status_code > '9' " to use a multiple index (status_code_idx twice).


I Think you could have a look to you index ... (Index "pidx " and "PK_item_obj" seems to be identical., probably notenough depper index majority with 1 field).

Some times when a multiple index is used by a request you could try to use only one of this index (one of us is not a restrictive index).
You could make a try with a "use-index agreement_idx" or "USe-Index fromdate_desc_idx" :
- this is depending of the restrictivity of " item_price.agreement_obj = agreement.agreement_obj " and "item_price.item_price_fromdate = dd" .
- you should use a tool like Protopin develoopper mode to check the read of the table item_price
- If you put a use-index comment it in your code.

Regards
Patrice
 

TomBascom

Curmudgeon
Please wrap code snippets in [ C O D E ] tags so that they are readable as code.

If SQL is 16x faster than Progress why are you complaining? ;)

Patrice's suggestions for improving the query look quite good.

If this were a 4gl query it would obviously perform horribly. The first thing I would do is find a way to avoid saying "<> 9". For instance, "<= 8 or >= 10" would probably be better because you have two statements that you could bracket on rather than 0. You might need some new indexes to properly support such a query - it's hard to tell, the indentation and line wrapping in your post are making my eyes hurt.

I'd profile the code regardless - you might be surprised what turns up. It would be a pity to spend a couple of weeks optimizing the heck out of stuff that doesn't matter while the real problem goes unaddressed.
 

JorgenKatz

New Member
Hi Jorgen,

Could you give us the SQL Plan
(I think it will begin by ITEM_price with index "fromdate_desc_idx").

With Progress You are using the folowing index
- agreement Index pidx WHOLE-INDEX
- ITEM_price Multiple index (Index "agreement_idx" + Index "fromdate_desc_idx" )
- Item Index "pidx"

If i am correct on the SQL PLAN, You can re-write your query to begins by the table ITEm_PRICE and join the otrher table .
===> The "item_price_fromdate = dd " should be a restritive equality....

If you want to avoid the Whole index on agreement you can modify "agreement.agreement_status_code <> '9' " by "agreement.agreement_status_code < '9' Or agreement.agreement_status_code > '9' " to use a multiple index (status_code_idx twice).


I Think you could have a look to you index ... (Index "pidx " and "PK_item_obj" seems to be identical., probably notenough depper index majority with 1 field).

Some times when a multiple index is used by a request you could try to use only one of this index (one of us is not a restrictive index).
You could make a try with a "use-index agreement_idx" or "USe-Index fromdate_desc_idx" :
- this is depending of the restrictivity of " item_price.agreement_obj = agreement.agreement_obj " and "item_price.item_price_fromdate = dd" .
- you should use a tool like Protopin develoopper mode to check the read of the table item_price
- If you put a use-index comment it in your code.

Regards
Patrice

Please wrap code snippets in [ C O D E ] tags so that they are readable as code.

If SQL is 16x faster than Progress why are you complaining? ;)

Patrice's suggestions for improving the query look quite good.

If this were a 4gl query it would obviously perform horribly. The first thing I would do is find a way to avoid saying "<> 9". For instance, "<= 8 or >= 10" would probably be better because you have two statements that you could bracket on rather than 0. You might need some new indexes to properly support such a query - it's hard to tell, the indentation and line wrapping in your post are making my eyes hurt.

I'd profile the code regardless - you might be surprised what turns up. It would be a pity to spend a couple of weeks optimizing the heck out of stuff that doesn't matter while the real problem goes unaddressed.

Hi Jorgen,

Could you give us the SQL Plan
(I think it will begin by ITEM_price with index "fromdate_desc_idx").

With Progress You are using the folowing index
- agreement Index pidx WHOLE-INDEX
- ITEM_price Multiple index (Index "agreement_idx" + Index "fromdate_desc_idx" )
- Item Index "pidx"

If i am correct on the SQL PLAN, You can re-write your query to begins by the table ITEm_PRICE and join the otrher table .
===> The "item_price_fromdate = dd " should be a restritive equality....

If you want to avoid the Whole index on agreement you can modify "agreement.agreement_status_code <> '9' " by "agreement.agreement_status_code < '9' Or agreement.agreement_status_code > '9' " to use a multiple index (status_code_idx twice).


I Think you could have a look to you index ... (Index "pidx " and "PK_item_obj" seems to be identical., probably notenough depper index majority with 1 field).

Some times when a multiple index is used by a request you could try to use only one of this index (one of us is not a restrictive index).
You could make a try with a "use-index agreement_idx" or "USe-Index fromdate_desc_idx" :
- this is depending of the restrictivity of " item_price.agreement_obj = agreement.agreement_obj " and "item_price.item_price_fromdate = dd" .
- you should use a tool like Protopin develoopper mode to check the read of the table item_price
- If you put a use-index comment it in your code.

Regards
Patrice

Hi Jorgen,

Could you give us the SQL Plan
(I think it will begin by ITEM_price with index "fromdate_desc_idx").

With Progress You are using the folowing index
- agreement Index pidx WHOLE-INDEX
- ITEM_price Multiple index (Index "agreement_idx" + Index "fromdate_desc_idx" )
- Item Index "pidx"

If i am correct on the SQL PLAN, You can re-write your query to begins by the table ITEm_PRICE and join the otrher table .
===> The "item_price_fromdate = dd " should be a restritive equality....

If you want to avoid the Whole index on agreement you can modify "agreement.agreement_status_code <> '9' " by "agreement.agreement_status_code < '9' Or agreement.agreement_status_code > '9' " to use a multiple index (status_code_idx twice).


I Think you could have a look to you index ... (Index "pidx " and "PK_item_obj" seems to be identical., probably notenough depper index majority with 1 field).

Some times when a multiple index is used by a request you could try to use only one of this index (one of us is not a restrictive index).
You could make a try with a "use-index agreement_idx" or "USe-Index fromdate_desc_idx" :
- this is depending of the restrictivity of " item_price.agreement_obj = agreement.agreement_obj " and "item_price.item_price_fromdate = dd" .
- you should use a tool like Protopin develoopper mode to check the read of the table item_price
- If you put a use-index comment it in your code.

Regards
Patrice
You are right! Progress query should be written as the SQL query. Started with item_price, join item, join agreement. When this was done, the time went down from 37 seconds to 3 seconds.

In this code, after the loop was changed to a "3-secondsloop", there was no difference when switching from <> "9" to (<"9" OR> "9"). I switched from multiple index to enter index. It made no difference.

SQL plan is attached. Extension has been changed from .sqlplan to .txt
 

Attachments

  • My_execution_plan.txt
    34.7 KB · Views: 1

JorgenKatz

New Member
Please wrap code snippets in [ C O D E ] tags so that they are readable as code.

If SQL is 16x faster than Progress why are you complaining? ;)

Patrice's suggestions for improving the query look quite good.

If this were a 4gl query it would obviously perform horribly. The first thing I would do is find a way to avoid saying "<> 9". For instance, "<= 8 or >= 10" would probably be better because you have two statements that you could bracket on rather than 0. You might need some new indexes to properly support such a query - it's hard to tell, the indentation and line wrapping in your post are making my eyes hurt.

I'd profile the code regardless - you might be surprised what turns up. It would be a pity to spend a couple of weeks optimizing the heck out of stuff that doesn't matter while the real problem goes unaddressed.
Hi Tom! I am a beginner in this forum. I will try [C O D E ] next time.
And, Yes, the Profiler seems to be more and more necessary! :)
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
- you should use a tool like Protop in developer mode to check the read of the table item_price
I second Patrice's suggestion to look at logical I/O as a measure of query efficiency, using whatever tooling and system tables your SQL database platform provides.

Measuring query performance by looking at elapsed time on the client can be interesting but it can also be fickle and misleading. It is influenced by the efficiency of your query code, but also by external factors that can change with time and place, e.g.:
  • the capabilities of the client machine, which will vary from one machine to another;
  • the current workload of the client machine (CPU/disk/network/memory);
  • the current workload of the server;
  • the current network path between client and server;
  • the current throughput in the network path between client and server;
  • the current workload of the database server process servicing the query requests;
  • the current client/server configuration settings;
  • the current size/content/usage of the database buffer pool and other shared database resources.
By contrast, the logical I/O (the number of table and index reads/creates/updates/deletes) that results from executing your data-access code should be invariant with the above factors. If a table contains 1,000 records, an unbracketed query for a single record will read 1,000 records and a well-bracketed query will read one record. Poor query efficiency won't be masked by fast hardware, as it can be if you are using elapsed time as your efficiency metric. These results will be repeatable regardless of when or where or how the test is conducted. (Perhaps this is not absolutely always the case in a SQL database with a cost-based query optimizer, as opposed to an ABL query in an OpenEdge database with a rules-based optimizer, but I suspect it is very likely to be true, especially for queries run within a small time span.)
 

andre42

Member
If this were a 4gl query it would obviously perform horribly. The first thing I would do is find a way to avoid saying "<> 9". For instance, "<= 8 or >= 10" would probably be better because you have two statements that you could bracket on rather than 0. You might need some new indexes to properly support such a query - it's hard to tell, the indentation and line wrapping in your post are making my eyes hurt.
Add another "or = ?" it the field is not mandatory.
 

Patrice Perrot

New Member
Hi,
I think Tom's suggestion is for this request "FOR EACH agreement WHERE agreement.agreement_status_code <> '9' NO-LOCK"

In this case "FOR EACH agreement WHERE agreement.agreement_status_code < '9' agreement.agreement_status_code > '9' NO-LOCK" is enough because the request is using the index status_code_idx (field agreement_status_code) and the upper value of this index is ? which is included in "> '9'),
To simplify "the value of this field is checked on index level (where ? is the upper value) not on field level where ? is ?".

if the request used another idnex , your suggestion will be correct (the value " ? > '9' "is ? which is not true.

I am using "< 9 or > 9" instead of "<= 8 or >= 10" for value like "85" (character field "8" < "85" < "9" < "95" < "10").

On Sports2000 DB, you can make this test
"For Each order No-Lock Where orderdate < ?" will give you all the order where orderdate <> ? because it use "orderdate" index.
"For Each order No-Lock Where orderdate < ? and custnum > 0" will give you no result because it use "Custorder" index

Regards
Patrice
 
Top