One of our clients has reported a problem which we have managed to replicate in-house.
Within our 10.1B Progress database we have created several predefined SQL views (see attachment) which are designed to explode multi-extent database fields into separate SQL fields.
What we have found is that accessing values from these exploded fields through ODBC causes quite a dramatic decrease in performance. The client-technology doesn't seem to make any difference (we have tried ADO.Net and adodb to query data from the view).
I have 7500 rows in the ADDDATA table.
Typical performance of querying (end exporting to CSV for example) of data directly from this table, e.g.
select "type", "reference", "tab-code" from pub.adddata
takes just over 4 seconds.
Changing this to:
select "type", "reference", "tab-code" from a.adddata
is almost identical at just over 4 seconds.
But as soon as we add one "exploded" field the select statement, it makes a big difference in performance, e.g:
select "type", "reference", "tab-code", "data@1" from a.adddata
This takes over 5 seconds.
And the more exploded fields we add the slower it gets, e.g:
select "type", "reference", "tab-code", "data@1", "data@2", "data@3", "data@4" from a.adddata
Takes 8 seconds.
select * from a.adddata
Takes more than 30 seconds.
I can only think that either the pro_element(...) or convert(...) SQL functions are slowing things down.
I've done some experiments with simple (single-extent) fields and very roughly I need to add more than 10 non-array type fields to get the same decrease in performance as adding one exploded multi-extent field.
Has anyone else found a similar problem and is there anything we can do about it, apart from avoiding multi-extent fields?
Thanks in advance
Steve
Have you compared the performance to using PRO_ELEMENT() in the SQL directly versus using a view?
You can also do it this way:
select "type", "reference", "tab-code", data[1], data[2], data[3], data[4] from a.adddata;
I haven't tried it in a long while, but I thought that the array notation was no longer supported in SQL and that is why PRO_ELEMENT was introduced?
Thanks Thomas & Gus for your replies.
I've tried your suggestions and done some more benchmarking and this is what I've found (in OpenEdge 10.1).
I'm still using the original test data from my original post (7,500 rows of data in my PUB.ADDDATA table which has several fields including a field called DATA with 32 extents), and executing an ODBC "select" statements to select all columns and all rows and export all values to CSV with a .Net app (using ADO.Net).
Selecting everything from the table itself PUB.ADDDATA and using square bracket [ ] syntax:
select "TYPE", "REFERENCE", "TAB-CODE", "FAC-TAB-NUMBER", data[1], data[2], ..., data[31], data[32], spare1, spare2,spare3, spare4, spare5, spare6, spare7, spare8 from pub.adddata;
This takes 13 seconds.
Using pro_element(...) syntax to select from the table:
select "TYPE","REFERENCE","TAB-CODE","FAC-TAB-NUMBER", pro_element("DATA",1,1), pro_element("DATA",2,2), ... , pro_element("DATA",31,31), pro_element("DATA",32,32), spare1, spare2, spare3, spare4, spare5, spare6, spare7, spare8 from pub.adddata;
This takes 18 seconds.
And selecting everything from the view A.ADDDATA:
select "TYPE","REFERENCE","TAB-CODE","FAC-TAB-NUMBER", "data@1", "data@2", "data@3", "data@4", "data@5", "data@6", "data@7", "data@8", "data@9", "data@10", "data@11", "data@12", "data@13", "data@14", "data@15", "data@16", "data@17", "data@18", "data@19", "data@20", "data@21", "data@22", "data@23", "data@24", "data@25", "data@26", "data@27", "data@28", "data@29", "data@30", "data@31", "data@32", spare1, spare2, spare3, spare4, spare5, spare6, spare7, spare8 from a.adddata
This takes 31 seconds.
So the square-bracket syntax works fine for me in 10.1B and gives me the best performance.
I guess it's not really surprising that selecting from the view is slower because of the pro_element( ) and convert( ) functions being used in the view definition.
I'll give both options (square-bracket and pro_element) to our client that raised the issue, hopefully the improved performance will solved their problem.
Thanks again.
Steve
Well ... almost.
The square bracket array notation was in our sql 89 implementation. When we did the new sql, we did not have time initially to get the square bracket notation in and we perpetrated a disgusting temporary solution with pro_element. No one should use that any longer since we have a proper (and standard conforming, did you know arrays are in the sql spec since sql99 ?) solution.
Thanks Gus, I'll pass that info to the Progress developers in SOS, sounds like we should think about redesigning our view definitions!