You can do it the way I was describing; I do have the same
master-detail relations and the same approach as you have.
Just give it a try if you find the time.
Instead of creating the complete tables locally, INSERT the records
into the temp-table in the temp-database 'stamped' with a session-ID.
Instead of deleting the complete tables locally, DELETE the records
belonging to the session-ID from the server.
Make a garbage-collection of the temp-database regularly (backup and
restore).
This is only a very small change to existing code, and no change of
delphi-components is needed.
Because with every "clean-up" the temp-tables will be completely empty
again, you can easily afford to have a lot of them on the server. I
was before copying the raw and complete dbase-tables to a local disk,
and then doing all joins and master-detail relations.
But now I have a temptable-skeleton for every possible master-detail
relationship and into them, I am only copying the data which is needed
by the clients.
Not much to change on old code, but already works fast as hell.
And no problem anymore with the BDE-typical "read-only"-datasets as
result of multiple joins.
That gives me the time to COMPLETELY restructure my application, while
I do have all the data already in firebird databases, and can use them
already for business reporting.
~~~
ciao,
André
> What you suggest works if you want only one temporary database in
the server. What I'm doing now is using a temporary database in each
client machine. When the application starts, it creates this database
and it creates temporary tables when it needs them and after using
them it erases the tables. When the application finishes, it deletes
the temporary database. Also, when the application starts, it checks
if there is a temporary database it couldn't delete the last time the
application was used and deletes it. Using fbembed.dll instead of
fbclient.dll, it works.
>
> The main reason because of which I use a temporary database is the
following. I have many cases of "master-detail" tables, for example a
customer table and another table which has data of employees of each
customer. When a user wants to modify the data of a customer, the
application uses a temporary table in which it copies the registers of
the employee table corresponding to that customer. Then it shows a
form with the customer data that contains a grid with the employee
registers. The DataSource.DataSet property of this grid is the
temporary table. When the user clicks the "save" button, the
application, copies the registers of the temporary table to the not
temporary employee table.
>
> Anyway, I think that I'm doing this because I'm "thinking in dBase"
and I'm sure that with an RDBMS there might be a better way to do
this. I would appreciate if anyone can give some ideas about this subject.
>
> I also would appreciate ideas about what are the most important
things that I have to consider in order to improve the performance of
my application. May be related with this, or with the use of Commit
and CommitRetaining or with anything else.
>
> Thanks in advance
>
> Gustavo
> ----- Mensaje original -----
> De: lysander_fb
> Para: firebird-support@yahoogroups.com
> Enviado: Jueves, 23 de Junio de 2005 04:48
> Asunto: [firebird-support] Re: Client in FireBird
>
>
> --- In firebird-support@yahoogroups.com, "Gustavo" <gusm@d...> wrote:
> > Adam (and David):
>
> I am also converting dBase/BDE/dBase to Delphi/Firebird, via
> dBase/BDE(ODBC)/Firebird.
>
> I also am in the dead-corner that I can not just switch off the
> temptable-approach. As a matter of fact, BDE/dBase was only sufficient
> fast in the network WITH temptables.
> Where I am still needing temptables I am doing the following, and it
> works good:
> create an additional database, for example "Temptables.fdb"
> For every table that you plan to use as a temptable, add a
> "session-id"-field to the structure.
> session-id can be an autoinc by use of a generator (this is how I am
> doing it), but I guess that using Current_Transaction or a selfmade
> key of "date/time/user" would also be good.
>
> create one session header
> in the header, for every new session, insert a record
> (session-id, tablename, state-of-process, maybe additionally starting
> and ending datetime of the session).
>
> starting the session has a process-state of 1
> finishing it is 2
> aborting it is 0
>
> In a house-cleaning module I am deleting from my temp-database all
> records where the session state is 0 or 2, and I am reviewing all
> sessions which have a state of 1 but are already running for more than
> one day.
>
> Using this approach, I can use Firebird instead of dBase, but can
> afford to change the data-access-components only step by step.
>
> ~~~
>
> There is no perfect guide for when to use commit() or
> commitretaining(). As a rule of thumb, if you do not EXPECT a lot of
> failed transactions, and therefore don´t have a need for frequent
> rollback()s, better use commit().
>
> ciao,
> André
>
> > registers. The DataSource.DataSet property of this grid is the
> temporary table. When the user clicks the "save" button, the
> application, copies the registers of the temporary table to the not
> temporary employee table.
> >
> > I believe I remember reading in this forum that using Commit has
> better performance than using CommitRetaining.
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>
>
>
------------------------------------------------------------------------------
> Yahoo! Groups Links
>
> a.. To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
> b.. To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
> c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service.
>
>
>
>
> [Non-text portions of this message have been removed]