D
dbeavon
Guest
@tbergman thanks for the pointer to your presentation! There is lots of great stuff in there for those who are in a position to use the CLR bridge. I found the demos that you referred to (SQLDemo1.p and SQLDemo2.p). It occurred to me that you could have shown an example of connecting to an OE database via SQL92 (with an OdbcConnection), but you demonstrated a SqlConnection instead. Is there some reason why you avoided a connection to the OE database? That is what I've been focused on lately. I'm pretty eager to start making some large queries back to the Progress database using SQL92 via the CLR bridge. Given that we are still running OE 11.7.4, I suspect I can use SQL92 queries to get a *substantial* performance improvement on certain queries - perhaps the improvement will be comparable to the 300% that is promised when we finally upgrade to OE 12 (wherein Progress has implemented multi-threading and server-joins in their remote database servers). Making queries from .Net is easy. And after making a large SQL92 query, the only real trick left is to quickly map the data from .Net back to an ABL temp-table. I see in your SQLDemo1 and SQLDemo2, you used a "SqlDataReader" and "WriteXml/READ-XML", respectively. I don't want to look a gift horse in the mouth, but I've found these approaches to be a bit slow for large datasets with lots of columns. They both seem quite CPU-intensive. The additional time for those approaches can take longer than the original query itself. It would be better if there was built-in functionality for mapping the results back to ABL from .Net. @rblanchard Your approach to WRITE-XML/ReadXml is fairly fast (from ABL to .Net). But as I mentioned, coming back the other way via WriteXml/READ-XML (.Net to ABL) is quite slow. That is the one that we'd be using most in order to retrieve data from an OpenEdge DB. It will happen after using an ODBC connection to the SQL92 engine and running a query via the ODBC driver (DRIVER=Progress OpenEdge 11.X Driver). It will alleviate many of performance bottlenecks we have today when we try to execute some large "client-server" queries from regular ABL code. Another problem with READ-XML (making it even more slow) is the artificial concurrency conflict that can impact PASOE if multiple sessions are trying to use that functionality at the same time (see community.progress.com/.../57795 ). In order to map .Net data back to ABL, the fastest approach I've found so far seems to be via the ABL "IMPORT" statement. There is some legwork needed to create a generic custom .Net assembly that generates the Progress "IMPORT" file format (after running an ODBC query). But the result the custom assembly would be a file on disk which ABL can then "IMPORT" quickly into an ABL temp-table. There are only two major issues I have (so far) with the IMPORT approach. First, I don't like the fact that I have to use the local disk for intermediate storage, since the .Net code and the ABL code are running in the *same* msagent process. It seems pretty silly for the results to be sent out to disk and then pulled back into memory again. And secondly, I don't particularly like the IMPORT format since it is not binary and doesn't seem standardized (any more than a csv). I'm not eager to see what happens when my ODBC results have my delimiters within the data itself (or it may even have line breaks). I suppose JSON might be a middle-ground between the "IMPORT" format and the full XML format. But I'm fairly certain that it will still be CPU-intensive to deserialize large amounts of JSON into an ABL temp-table. Please let me know if anyone has a faster or cleaner way to map this .Net data back into ABL. I'd love to hear about any other options.
Continue reading...
Continue reading...