Board index » delphi » Strategy to optimize performance with ibx (tIbDataSet).

Strategy to optimize performance with ibx (tIbDataSet).

Hi all,

I have an application in Delphi 5.01 and Ibx that create and manage
invoices; in this application I have a customer table with more than 50.000
customers.

When the user must find a customer code, I create a new form with a tdbgrid
that browse all the customers; in this form the user can browse through the
grid or find the customer code with the initial characters of the
description.

The component that I use to select the customers code is the tIbdataset.

With this strategy I known that the TibDataset fetch from the interbase
server the number records that are set in the BufferChunks parameter and in
my example, when the user go to the last record of the browse, the
TibDataSet Fetch all records from the server.

To optimize the performance I think that a good solution will be select a
small number of records every time that the user go to the first, last, next
and prior row of the table, changing the sql command of the tIbDataset.

Does anyone have a suggestion to optimise the performance when browsing a
large table  ?

Thanks in advance.

Massimo.

 

Re:Strategy to optimize performance with ibx (tIbDataSet).


Let user select what customer he wants like:
- name beginning with
- name containing (slower to find!)
- customer code equals
etc..

Experiment with building indexes on the database for the fields user can
select on.

You could also use a clientdataset to load the selected data locally.
Once loaded with the clientdataset you can do sorting locally by
setting the indexfieldnames property. Could be done via a dbgrid.titleclick
.
But again: don't let the clientdataset load all the records.
In clientdataset you can also select how many records are loaded in one
time.
(PacketRecords property)

Load as few fields as possible (like only code and name). If user want's to
see more fields, load all fields of one selected record.

Re:Strategy to optimize performance with ibx (tIbDataSet).


Quote
Max wrote:

> With this strategy I known that the TibDataset fetch from the interbase
> server the number records that are set in the BufferChunks parameter

This is incorrect.  IBX only returns one record at a time as needed.  Your grid
will force everything it needs to display to be fetched.  BufferChunks is how
much memory is allocated every time the existing buffer is full and a new record
is to be inserted/retrieved.

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
This sad little lizard told me that he was a brontosaurus on his mother's
side.  I did not laugh; people who boast of ancestry often have little else
to sustain them.  Humoring them costs nothing and adds to happiness in
a world in which happiness is in short supply.   (RAH)

Re:Strategy to optimize performance with ibx (tIbDataSet).


Quote
Jeff Overcash (TeamB) wrote:
> This is incorrect.  IBX only returns one record at a time as needed.  Your
grid
> will force everything it needs to display to be fetched.  BufferChunks is
how
> much memory is allocated every time the existing buffer is full and a new
record
> is to be inserted/retrieved.

So if the user go to the last record using the scroll bar of the grid, IBX
only returns a small amount of records but the problem is the grid that
request all the records ?

Thanks

Massimo

Re:Strategy to optimize performance with ibx (tIbDataSet).


Quote
Jeff Overcash (TeamB) wrote:
> This is incorrect.  IBX only returns one record at a time as needed.  Your
grid
> will force everything it needs to display to be fetched.  BufferChunks is
how
> much memory is allocated every time the existing buffer is full and a new
record
> is to be inserted/retrieved.

So if the user go to the last record using the scroll bar of the grid, IBX
only returns a small amount of records but the problem is the grid that
request all the records ?

Thanks.

Massimo

Re:Strategy to optimize performance with ibx (tIbDataSet).


Quote
Max wrote:

> Jeff Overcash (TeamB) wrote:

> > This is incorrect.  IBX only returns one record at a time as needed.  Your
> grid
> > will force everything it needs to display to be fetched.  BufferChunks is
> how
> > much memory is allocated every time the existing buffer is full and a new
> record
> > is to be inserted/retrieved.

> So if the user go to the last record using the scroll bar of the grid, IBX
> only returns a small amount of records but the problem is the grid that
> request all the records ?

No the problem isn't in the grid.  IB has only uni directional cursors.  To get
to the last you have to read all records before the last.  

Quote

> Thanks

> Massimo

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
This sad little lizard told me that he was a brontosaurus on his mother's
side.  I did not laugh; people who boast of ancestry often have little else
to sustain them.  Humoring them costs nothing and adds to happiness in
a world in which happiness is in short supply.   (RAH)

Re:Strategy to optimize performance with ibx (tIbDataSet).


Quote
"Max" <dbsoftinformat...@libero.it> wrote in message

news:3c9859db_2@dnews...

Quote

> When the user must find a customer code, I create a new form with a
tdbgrid
> that browse all the customers; in this form the user can browse through
the
> grid or find the customer code with the initial characters of the
> description.

> The component that I use to select the customers code is the tIbdataset.

Your goal should always be to select as few records as possible from the
server. There is never a valid reason for a user to need to browse through
50,000 records to find the one they want to edit, this work is precisely
what SQL databases are good at so you should use that ability. You can still
provide the grid for the results, but you should display *no* records there
until the user has entered some search criteria and pressed a Search button.
Pass the search criteria as a Where clause in the select statement.

You will find users (at least most of them) will accept this very quickly
even if right now they claim that this would be "too much" work. It ends up
being less work then scrolling through 50000 records.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
In a tornado, even turkeys can fly. - unknown

Re:Strategy to optimize performance with ibx (tIbDataSet).


In general, it is global problem - to provide full-function navigation
(instead of selection) on the big size data. For this purpose I have written
additional components for IBX (gb_DataSets Components). You can download
them from CodeCentral section "Delphi Source". For this components
additional index is necessary for normal perfomance.

Spirin Sergey.

Quote
Max wrote:
> Hi all,

> I have an application in Delphi 5.01 and Ibx that create and manage
> invoices; in this application I have a customer table with more than
50.000
> customers.

> When the user must find a customer code, I create a new form with a
tdbgrid
> that browse all the customers; in this form the user can browse through
the
> grid or find the customer code with the initial characters of the
> description.

> The component that I use to select the customers code is the tIbdataset.

> With this strategy I known that the TibDataset fetch from the interbase
> server the number records that are set in the BufferChunks parameter and
in
> my example, when the user go to the last record of the browse, the
> TibDataSet Fetch all records from the server.

> To optimize the performance I think that a good solution will be select a
> small number of records every time that the user go to the first, last,
next
> and prior row of the table, changing the sql command of the tIbDataset.

> Does anyone have a suggestion to optimise the performance when browsing a
> large table  ?

> Thanks in advance.

> Massimo.

Other Threads