I am trying to create a dashboard type thing, with a list of upcoming events. So I have a portal showing the events that match a range of dates. But it seems that no matter how I slice this filtering business the layout will transfer about 18MiB of data down, which takes quite a while (20 - 60 seconds).
I have tried the regular filtered portal, using a few different filters:
1) Explicit list of dates in the range (yeah, not real dynamic, but was just a test):
Event::TheDate = Date (10;1;2012) ; 1 ;
Event::TheDate = Date (10;2;2012) ; 1 ;
Event::TheDate = Date (10;19;2012) ; 1 ;
Event::TheDate = Date (10;20;2012) ; 1 ;
2) More compact date range with inequalities:
Event::Date ≥ Date (10; 1; 2012) and
Event::Date ≤ Date (10; 20 ; 2012)
3) A friend suggested using a Relationship Filter (with a different TO) that is defined by a global field, which in turn is populated with IDs by script. The script makes an ExecuteSQL call to get the IDs for records matching a range:
ExecuteSQL( "SELECT id FROM Events WHERE TheDate BETWEEN ? and ? "
;"" ; "" ; "10/1/2012" ; "10/20/2012")
But each of these moves around the same amount of data, even though some are much longer than others. I have moved the date ranges around to return anything from 0 records to 234 records, and each time it is the same amount of data. It really just seems like I am getting the entire table of data and the filtering is happening locally. (The table is rather wide - 700 fields, and has 3100+ records, average record size is 15KiB, which doesn't multiple out to 18.)
What's going on here? I thought filtered portals were supposed to happen at the server in FM12? (FMSA, BTW. Clients are remote; FMPA12.) It is filtering SOMETHING (if the average record size is accurate), but it certainly doesn't appear to filtering down to the actual records visible, at least not until I get it.
UPDATE (before I even posted ) : While the SQL-and-relation system was noticably faster than the portal filter (the 20 seconds part of the range), it appears that the 'Between' key word was very slow. I ran a version that just used "Where TheDate > date1 and TheDate < date2" and it ran in about 1 second. Nice! That is pretty bearable. But that still leaves the question of what FM is doing in the other circumstances.
And can you use ExecuteSQL inside the portal filter definition box somehow? It seems that the statement I used was about as simple as it might get for that definition.