Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: FB databse with too many records slowing down application


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 3 - Last Post: Nov 5, 2017 4:30 AM Last Post By: Eduardo Tavares Threads: [ Previous | Next ]
Eduardo Tavares

Posts: 29
Registered: 1/17/05
FB databse with too many records slowing down application  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 3, 2017 3:21 AM
Hi!

I have an application with sqlconnection, sqldataset,datasetprovider and clientdataset using Firebird and my database has 30,000 records. It is very slow to search using CDS.locate(...);

what is the best thing to do to speed things up with this set up?

I have tried set packetrecords to 300 something like that but did not help.

what is the best approach for this scenario?

thank you

Eduardo Tavares

Posts: 29
Registered: 1/17/05
Re: FB databse with too many records slowing down application  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 3, 2017 10:23 AM   in response to: Eduardo Tavares in response to: Eduardo Tavares
Eduardo Tavares wrote:
Hi!

I have an application with sqlconnection, sqldataset,datasetprovider and clientdataset using Firebird and my database has 30,000 records. It is very slow to search using CDS.locate(...);

what is the best thing to do to speed things up with this set up?

I have tried set packetrecords to 300 something like that but did not help.

what is the best approach for this scenario?

thank you


Nobody knows?
Peter Below

Posts: 1,227
Registered: 12/16/99
Re: FB databse with too many records slowing down application  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 4, 2017 1:34 AM   in response to: Eduardo Tavares in response to: Eduardo Tavares
Eduardo Tavares wrote:

Hi!

I have an application with sqlconnection, sqldataset,datasetprovider
and clientdataset using Firebird and my database has 30,000 records.
It is very slow to search using CDS.locate(...);

what is the best thing to do to speed things up with this set up?

I have tried set packetrecords to 300 something like that but did not
help.

what is the best approach for this scenario?

You have to rethink your approach. What you are currently doing
basically loads the whole database table into client memory. That works
for small, single-user databases, but not for multiuser databases using
an advanced database engine, with support for transactions and
concurrent access from different users, and potentially millons of
records in multiple tables.

The equivalent of using something like locate is to formulate a SQL
SELECT statement that returns only the records fitting your search
criteria. To make SELECT performant even for large numbers of records
in the table(s) you need to define indices on the table for the columns
you typically use for searching. SQL also allows you to formulate
queries that collect data from several database tables and return it as
if it came from a single table. Such result sets are usually read-only,
though.

--
Peter Below
TeamB

Eduardo Tavares

Posts: 8
Registered: 2/6/06
Re: FB databse with too many records slowing down application  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 5, 2017 4:30 AM   in response to: Peter Below in response to: Peter Below
Peter Below wrote:
Eduardo Tavares wrote:

Hi!

I have an application with sqlconnection, sqldataset,datasetprovider
and clientdataset using Firebird and my database has 30,000 records.
It is very slow to search using CDS.locate(...);

what is the best thing to do to speed things up with this set up?

I have tried set packetrecords to 300 something like that but did not
help.

what is the best approach for this scenario?

You have to rethink your approach. What you are currently doing
basically loads the whole database table into client memory. That works
for small, single-user databases, but not for multiuser databases using
an advanced database engine, with support for transactions and
concurrent access from different users, and potentially millons of
records in multiple tables.

The equivalent of using something like locate is to formulate a SQL
SELECT statement that returns only the records fitting your search
criteria. To make SELECT performant even for large numbers of records
in the table(s) you need to define indices on the table for the columns
you typically use for searching. SQL also allows you to formulate
queries that collect data from several database tables and return it as
if it came from a single table. Such result sets are usually read-only,
though.

--
Peter Below
TeamB


thank you Peter.I will try to make some changes and see if it gets better.thank you very much
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02