D
dbeavon
Guest
@Stefan Yes exactly. The SQL92 engine can optimize & execute my dynamic queries on multiple joined tables via the CLR bridge. The results can be returned with multiple ROWID's in the columns (representing any interesting FK relationships). Then you just EXPORT/IMPORT the results back into my ABL session (the topic of a different thread). This technique will gather lots of raw data in a small fraction of the time that "client/server" ABL would do it (primarily because "client/server" performance is super-chatty and the performance of many FOR-EACH "queries" will degrade in proportion to the number of records that are being retrieved) The only thing left to do is to fetch any remaining/discretionary column data which isn't very SQL92-friendly (or isn't EXPORT-IMPORT-friendly, see PS***). That is where I need the "special" ROWID format. The SQL92 engine doesn't provide the "special" format for ROWID but I could use your query (or do a little bit of query-post-processing within the CLR bridge assembly) Once I have a distinct list of ROWID's, then "client/server" ABL can once again play a role in the data-gathering operation. It can retrieve a reasonable list of records based on their ROWID's. This is fairly quick so long as it is happening on a single table at a time, and can be done in batches. The technique here is to use a series of dynamic queries with batches of up to 100 predicates in the form ("OR ROWID(customer) = TO-ROWID(" + Ch-RowId + ")"). Notice that these dynamic queries require the "special" string representation of ROWID. Each query will be handled as a single round-trip to the server, but it will fetch large amounts of data and isn't super chatty. Yes, it is not a very simple process, but the performance improvement can be compelling for large queries. Also, much of this can be done with generic, re-usable code that runs behind an abstracted interface (ie OO classes that hides the CLR bridge interop). The end result is that all of our local, client session TT's can be filled with OpenEdge data much quicker than before. Beyond the performance improvement, there are additional benefits related to the ability to run dynamically-generated SQL queries that have customized predicates. Thanks, David *** PS. Examples are the "extent" columns and other columns like CLOBs, BLOBs, multi-line CHARACTER, etc.
Continue reading...
Continue reading...