TAP and large resultsets

[Doug Tody]

I think this might be a reasonable compromise.  For exploratory or
interactive queries the client could specify a reasonable size
query using TOP+ORDER BY or NRECORDS (which is the same as TOP but
reflects the natural sort order of the table).  Then when it is ready
to get a big chunk of data it could simply ask for more, up to the
maximum number of records as specified in the service capabilities
for a single "simple" query.  With this approach, we probably want
services which define a reasonably small default NRECORDS and a fairly
large maxRecords.  The value of maxRecords would be related to the
maximum result set which can be computed before timing out, and the
maximum memory the server is willing to allocate to a single query.
For anything larger than this we probably need to go to asynchronous
techniques in any case.   - Doug


On Mon, 29 Jan 2007, Patrick Dowler wrote:

> On Monday 29 January 2007 11:26, Patrick Dowler wrote:
>> Note: by using TOP and ORDER BY judiciously, the client can arrange to do
>> the chunking without any support in the service, especially easy if there
>> is a known unique key:
>>
>> select top N * from some_table
>> [ where uid > biggest_id_I_have_seen ]
>> order by uid
>
> I should have added something else here: doing chunking with top and order by
> is fairly friendly to the DB since the good columns for the order by and the
> condition are almost always going to be indexed, so finding the start of the
> chunk is well optimised. And when you tell the db how many rows you want via
> top, it can perform other optimisations. Most DBs will try to use an index on
> uid for the above example, especially when N is small, but can use it in
> later query exec stages as dictated by table stats, etc.
>
> This kind of client-specified chunking in the query is easy to do and well
> behaved, and it requires no server-side state. I think it would be tricky to
> have the TAP service implement the same thing (inject some extra SQL, for
> example), certainly would require some server-side state, and could conflict
> with a user-specified ORDER BY clause.
>
> So in an effort to make the TAP spec as simple as possible, I think we should
> just punt since the client will be able to do it within core ADQL features.
>
> -- 
>
> Patrick Dowler
> Tel/Tél: (250) 363-6914                  | fax/télécopieur: (250) 363-0045
> Canadian Astronomy Data Centre   | Centre canadien de donnees astronomiques
> National Research Council Canada | Conseil national de recherches Canada
> Government of Canada                  | Gouvernement du Canada
> 5071 West Saanich Road               | 5071, chemin West Saanich
> Victoria, BC                                  | Victoria (C.-B.)
>