UI performance and large data set in Table Object

[Jan Schenkel]

--- mfstuart <mfstuart at cox.net> wrote:
> 
> Hi All,
> 
> I have a question about large data sets (thousands
> of lines) in a table
> object and the slowing down of the UI performance,
> especially on resizing
> the stack.
> 
> My application interfaces to an MS SQL 2000 database
> via ODBC. No problem
> there.
> The SQL table I have has over half a million records
> in it, and this grows
> all the time.
> 
> Potentially, the user could return all records,
> which would take a while to
> load them all.
> But I've created the interface to allow the user to
> return a smaller record
> set. This can be alot of records - 10, 30, 40, 50000
> thousand or more, or
> just a few hundred. It depends on how the user
> searches the database.
> 
> Now when large record sets are returned from a
> search, the UI (user
> interface) slows down, especially when resizing the
> stack to see more
> records in the table object. When resizing with no
> records, the UI is
> performs normally with fast resizing.
> 
> Q: has any one seen this before? And if so, how do
> you handle the drop in UI
> performance?
> 
> Thanx,
> Mark Stuart
> 

Hi Mark,

The problem is two-fold: it's a lot of data that has
to go through some pipe before it arrives in
Revolution, and Revolution then has to process and
format a lot of that to determine what to actually
display on screen, recalculate the size of the
scrollbar, etc.

For a project where it was taking a lot of time before
the user saw anything, I decided to take a different
approach: rather than rely on a single call to
revdb_querylist()/revDataFromQuery(), to use a result
set cursor and a 'send in time' construct to fetch 20
records at a time.

This approach allows the user interface to remain
responsive, and the user can see the table filling up
as more data comes in. It also means you can cancel
the rest of the data download as soon as the user
finds the record he wants, or decides to make a
different selection.

It looks something like this:

##
global gConnectionID
local sFetchMsgID, sCursorID, sColumnCount

on mouseUp
  CancelFetchTimer
  put "SELECT * FROM Customers" into tQuery
  put revdb_query(gConnectionID, tQuery) into
sCursorID
  if sCursorID is not a number then
    answer error sCursorID
    exit mouseUp
  end if
  put revDatabaseColumnCount(sCursorID) into
sColumnCount
  put empty into field "QueryResults"
  send "FetchNext20Records" to me in 0 milliseconds
  put the result into sFetchMsgID
end mouseUp

on FetchNext20Records
  put false into tStopFlag
  repeat 20 times
    repeat with tColumn = 1 to sColumnCount
      put revDatabaseColumnNumbered(sCursorID,
tColumn) & \
          tab after tNextPart
    end repeat
    put return into char -1 of tNextPart
    put revCurrentRecordIsLast(sCursorID) into
tStopFlag
    if tStopFlag then
      delete char -1 of tNextPart
      exit repeat
    end if
  end repeat
  put tNextPart after field "QueryResults"
  if tStopFlag then
    put empty into sFetchMsgID
  else
    send "FetchNext20Records" to me in 100
milliseconds
    put the result into sFetchMsgID
  end if
end FetchNext20Records

on CancelFetchTimer
  if sFetchMsgID is not empty then
    cancel sFetchMsgID
    put empty into sFetchMsgID
  end if
end CancelFetchTimer
##

This is all off the top of my head, so beware of
typos.

Hope this helped,

Jan Schenkel.

Quartam Reports & PDF Library for Revolution
<http://www.quartam.com>

=====
"As we grow older, we grow both wiser and more foolish at the same time."  (La Rochefoucauld)


      ____________________________________________________________________________________
Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs