[Progress Communities] [Progress OpenEdge ABL] Forum Post: RE: Newbie questions about dynamic query preparation in ABL

Status
Not open for further replies.
D

dbeavon

Guest
Thanks for the update Tim. I've been struggling with getting QUERY-PREPARE to perform well, especially when using lots of queries. I appreciate any tips and suggestions. Another approach I've been evaluating is quite a lot more promising... we may start relying more on the SQL92 engine. This can be initiated right within the ABL code, and it can even be done within the *same* agent process, so long as PASOE is running on Windows. This can be accomplished by using the "CLR Bridge" to prepare an ADO.Net adapter with a relevant SELECT statement. Then the adapter method (Fill) will populate a the data. Since the resulting data is initially presented to us on the .Net side (not in ABL) then there is additional work to migrate the data back over to a ProDataSet again . (... which is a part of the work would not have been necessary if QUERY-PREPARE had been sufficient for our queries). Migrating the data back from the CLR Bridge to a ProDataSet was a bit of a trick. If you aren't careful, then this movement of data can be very slow (despite the fact that it is simply moving data from one portion of process memory to another, within the same PASOE msagent). In fact, if you are really unlucky, then moving the data from one part of memory to another can be even *slower* than performing the Adapter.Fill operation in the first place - via the SQL92 engine. Insofar as moving data back from the CLR Bridge to a ProDataSet ... I've had two discussions about moving data between the runtimes (where both runtimes are hosted in the same msagent process): Mapping between .Net DataTable and ABL temp-table Options for mapping between .Net DataTable and ABL temp-table. - Forum - OpenEdge Development - Progress Community Using Progress.Data.BindingSource for non-GUI Purposes Using Progress.Data.BindingSource for non-GUI Purposes (on Windows) - Forum - OpenEdge Development - Progress Community The approach I'm currently settled on is to serialize the ADO.Net data into a JSON stream using newtonsoft . Then you just deserialize it again into my ABL prodataset using READ-JSON(). This happens entirely in memory without sending data out to the file system. It is a bit slow (about 100 ms per 10,000 records) but typically it doesn't exceed the amount of time it takes to run the SQL92 query in the first place. As an added bonus, I retrieve several different ROWID's columns for any of the foreign key references that may be of interest. (The rowid's are serialized as 16 digit hex strings and can be used for gathering even more additional data via TO-ROWID()). This current approach involves several moving parts - but the end result is worth it. The SQL92 engine offers a lot more than you get from QUERY-PREPARE. In fact, with SQL92 I already have my "server-side joins" and my "multi-threaded server" features! This is despite the fact that we are still running OE 11.7 (Progress is making these things available to normal ABL programs in OE version 12). Since there are some moving parts, it is important to build a bit of abstraction over the top of this (using OOABL). With a good abstraction layer, the syntax for using the SQL92 engine can appear just as straight-forward as using QUERY-PREPARE. After the initial SQL92 query is made, and after a TT is filled with our data, then the last step is to chase down any interesting foreign-key records that you might still want. If you had already prepared for this in advance, then you will have the 16 digit hex ROWIDs for foreign-key records. You can either go get these one at a time (using WHERE ROWID(customer) = TO-ROWID(query_output.customer_rowid_hex) ) or you can build a dynamic query to get them in batches (100 rowids at a time). This final step is actually a suitable problem for the use of QUERY-PREPARE. It is worthwhile to retrieve the foreign-key records in batches if there are 100's or 1000's of them, and if you are retrieving them via "client/server" (the PASOE instance is remote from the database). Sorry for this long post, but I thought it was important to share it here, since I've gotten so much help while working on this approach. Migrating our "shared memory" code to a remote PASOE instance has been challenging. Of all the challenges we've faced while migrating to PASOE, the performance of "client-server queries" has been the most challenging (more challenging than everything else combined). We've already spent a year migrating to PASOE from our "classic" appservers that were running with "shared memory" connections. And on top of that, we're simultaneously migrating our ABL from the HP-UX platform to the Windows platform. Moving to the Windows platform makes it possible to use the CLR Bridge for our SQL92 queries. When we were on HP-UX it would have involved even more complexity to make use of SQL92 from ABL code. A lot of the work would probably have been done "out-of-process" (probably in some scary shell scripts or something like that.)

Continue reading...
 
Status
Not open for further replies.
Top