Foxhound is the better* Database Monitor for SQL Anywhere. *better: More thorough, more relevant, more effective. ...more Alerts, more All Clears, more details, more control in your hands.

Pre-Loading DataWindows

How do I deal with a DataWindow that might contain up to 50,000 rows? I'd like to avoid using qualified retrievals that restrict the result set, but pre-loading all the rows when the application starts is an acceptable approach.

The knee-jerk response is to say "Why on earth would anyone want to see 50,000 rows?" That kind of answer is typical of someone who hasn't worked in the real world very long, where it is common for a user to say "I don't mind waiting to get all my data as long as it works well once it's loaded. I have a difficult task to accomplish and I'll pay to get it done... more memory, more time, more money, it's all the same to me. Just don't tell me I don't need to do it!"

Large DataWindows consume memory in proportion to the size of the buffer. The exact formula is not available but it's roughly this:

   Memory required = ( number of rows ) * ( number of bytes per row )

If the row size is reasonable then 50,000 rows is certainly possible. You can use Program Manager - Help - About to see how much memory is used before and after the following simple feasibility test: Run your SQL select in the Database Administration painter. That will dynamically create a grid DataWindow and you can see if the performance is acceptable and if the data will fit in memory. In fact, it is probably a worst-case as far as performance is concerned because this dynamic DataWindow displays a "rows retrieved so far" counter, something that usually slows down retrieve() performance quite noticeably

In PowerBuilder 4 and earlier a popular technique is to use a hidden main window to pre-load a DataWindow, and then call ShareData() whenever the data must be displayed. Place the retrieve() call in the window open script and then open the window in the application open script.

If you want to defer loading the DataWindow until it is first needed then use this code immediately ahead of any logic that needs the data:

   if not IsValid ( w_hidden_data ) then
      open ( w_hidden_data )
   end if

Place the following code in the frame window close event to get rid of the data when the application shuts down:

   if IsValid ( w_hidden_data ) then
      close ( w_hidden_data )
   end if

The advantage to a hidden main window is that you can refer to its global name throughout the application. Although these global window names aren't visible in the Global Variables painter they can be seen in the exported code for each window:

   global w_hidden_data w_hidden_data

That declaration creates a global variable of type "w_hidden_data" with the variable name "w_hidden_data". Once this window is opened the variable w_hidden_data can be directly referred to, as can its contents:

   li_RC =  w_hidden_data.dw_hidden.ShareData ( dw_visible )
   if li_RC <> 1 then
      MessageBox ( "Error", "ShareData failed" )
   end if

The existence of these global window variables is a source of confusion to new PowerBuilder programmers. When the following code is executed

   open ( w_hidden_data )

it is actually creating an object of type w_hidden_data and assigning it to the variable called w_hidden_data. The argument to open() is the variable name and its type is determined from the global declaration.

Object oriented purists might complain about any use of global variables but there is a justification in this case: The database itself is global to the application and the global window is an extension to the database to change its behaviour and improve performance. It is just a re-packaging of the already-global database data.

PowerBuilder 5 offers an object-oriented alternative: The non-visual DataStore object which is simply a DataWindow without a visible component. It can be encapsulated within a non-visual user object and created/destroyed without the need for a hidden window.

There is an important distinction between visual versus non-visual and visible versus invisible. The first indicates whether it is possible to display the object: non-visual objects cannot be displayed and so they are always invisible. The second applies to whether or not a visual object is actually displayed: Visibility is optional.

If you object to using global variables in PowerBuilder 4 you can encapsulate the hidden data in a non-visual DataWindow. A hidden window is still required but it can be opened from within the non-visual object as follows:

   // Private instance variable declaration:

   private:
   w_hidden_data idw_hidden

   // Public object function of_load:

   open ( idw_hidden )

That call to open() creates an object of type w_hidden_data and assigns it to the private instance variable idw_hidden. This technique allows you to define object functions that restrict and control access to the data inside w_hidden_data, but you still need that hidden window.

Simply referring directly to the global w_hidden_data variable is arguably simpler. Whichever technique you choose it's important to use a DataWindow to store the pre-loaded data. Alternatives using cursor fetch loops and arrays of rows of columns and other non-DataWindow techniques are to be avoided at all costs. DataWindows perform well, PowerScript code does not. In fact, PowerScript fetch loops often run on a geological time scale, barely faster than continental drift.

Pre-loading large DataWindows is a valuable technique for large static result sets. It can even be used for DropDownDataWindows that are used in many other DataWindows to vastly reduce the time required to open windows.