This is a common problem with search tasks, with well known patterns for
dealing with it.
One way to deal with it transparently (to the GUI designer as well as
the user) is with a ValueListHandler (google on it). It would only
fetch as many items as are to be displayed in one page, and would then
fetch remaining pages as needed, so the whole result set would not be
brought over from the server. It can be made as sophisticated as you
would like, so it can do lazy loading or cache results.

-----Original Message-----
From: Stephen Searfoss [mailto:xpertmart@...]
Sent: Friday, July 30, 2004 8:36 AM
To: Firebird-Java@yahoogroups.com
Subject: Re: [Firebird-Java] Re: Speed problem retrieving 50,000 rows...

Let me add my opinions and observations on this...

--- Roman Rokytskyy <rrokytskyy@...> wrote:

> > Yes, the connection is in auto-commit mode... some
> things have
> > changed a bit and the time taken is now 4.8
> seconds. When I turn off
> > autocommit, time is 2.0 seconds.
> > While this is a vast improvement, this part of our
> code is executed
> > often, so 2 seconds is noticeable...

From a non-technical person's point of view I have
never understood why auto-commit or not affects in any
way a read operation. It seems to me that since a read
operation by definition is not changing the database,
it will never be "commited" anyway.

This is a minor detail perhaps, but it seemed with
the older InterClient to make more sense that the
initial query prepared the result set on the server
and held it there for me to then actually read as many
registers as I wanted, one by one.

> Do you want to say that you execute the SELECT
> statement but you need only
> few entries from it? Then you better use SELECT
> FIRST x [SKIP y] statement.

In GUI operations with a user sitting in front of
a workstation the programmer never knows if he is
going to look at three registers and finish or if he
actually wants to scroll through all 50,000 records.

> But if at the end you fetch the complete result
> locally, you will have to
> spend that 2.0 seconds sooner or later. Or do I miss
> something?

If the users sees what he wants after looking a a
dozen records then he should not have to take the 2.0
seconds.

Often the users does not know there are 50,000
records. He sees some of the results and decides he
needs to redefine his query to be more specific.

One last not on this is that aside from time
issues, in Jaybird, you also need a lot of RAM on the
client side to store all the result set when you are
in auto-commit mode!

> > can you tell me more about the (default) fetch
> size (or a URL describing
> > it)?
>
> There is no way to specify the default fetch size in
> the URL. The default
> value is just a number, it has little sense when the
> record size is not
> taken into account. It makes also little sense to
> set it for the complete
> connection, since different queries will be
> executed.
>
> > I saw your follow-up which said the requested
> fetch size is not
> > guaranteed, but maybe I can at least tweak it a
> little...?
>
> No idea. Fetch size only tells driver how many
> isc_dsql_fetch calls should
> be made to fill the internal buffer. You can set it
> to 1 and this will
> guarantee that isc_dsql_fetch will be called only
> once.
>
> Roman
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~-->
> Make a clean sweep of pop-up ads. Yahoo! Companion
> Toolbar.
> Now with Pop-Up Blocker. Get it for free!
>

http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/saFolB/TM

>

--------------------------------------------------------------------~->

>
>
>
> Yahoo! Groups Links
>
>
> Firebird-Java-unsubscribe@yahoogroups.com
>
>
>
>

__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail




Yahoo! Groups Links