Re: [gnome-db] libgda cursor support

["Vivien Malerba" <vmalerba gmail com>]

On 5/25/07, Vivien Malerba <vmalerba gmail com> wrote:
> On 5/25/07, Murray Cumming <murrayc murrayc com> wrote:
> > I believe that libgda theoretically allows the use of cursors instead of
> > getting the whole query result at once. I don't know what API would
> > actually be used for this - an example would be nice.
> >
> > But I don't think that any of the libgda providers actually implement
> > this. Does anyone have plans to do this, or can anyone suggest how it
> > should be done? This seems to be the relevant bug:
> > http://bugzilla.gnome.org/show_bug.cgi?id=318742
>
> The default is if you don't specify a data access method, then a
> random access data model is returned (possibly consuming a lot of mem)
> which allows random access and  of course cursor access.
>
> If you want a data model which is based on cursors only (no random
> access then), you need to set a parameter named "ITER_MODEL_ONLY" of
> type boolean to TRUE which is given to the
> gda_connection_execute_command() family functions as the @params
> attribute. The same is true for the gda_query_execute() function.
>
> This function then in the end calls
> gda_server_provider_execute_command() which is passed the same @params
> list of parameters. Each DBMS provider is then free to treat that
> parameter or ignore it (as is currently the case). If a provider
> chooses to honor that request, then the associated data model needs to
> be improved to support cursor based nabigation (for example for
> PostgreSQL, the GdaPostgresRecordset object needs to be modified).

It's now in SVN trunk (won't be in 3.0.2 but in 3.2.x). I've also
added an "ITER_CHUNCK_SIZE" parameter which, if specified, instructs
the provider to fetch SIZE rows at once instead of only one when the
iterator moves to avoid performances problem related to many data
fetches from the DB.

For instance on my test app, I get the following:
1 1050401 352.830135 (default)
2 527651 269.491419
3 353502 239.642737
4 266201 222.359096
5 213881 207.689070
6 178769 203.641199
7 154096 197.981889
8 135153 200.689542
9 120940 197.943023
10 109091 197.217066
11 99726 195.224419
12 91441 192.822999
13 84208 190.728743
14 79112 191.649108
15 73332 190.923498
20 56321 190.146810
25 45617 184.298655
30 37920 181.133788
40 28321 182.492960
50 23459 179.566597
60 18827 192.137447
70 16072 175.48963
80 13817 180.283506
90 12062 173.567836
100 10505 178.165698
where the 1st column is the SIZE, the second column is the number of
times data has been fetched from the DB, and the 3rd column is the
execution time in seconds (the test app performs about a million
iterator moves for each value of SIZE). Even if the execution time
numbers are not very accurate, they are an indication.

Tell me if it's Ok for you and Glom.

Cheers,

Vivien