----- Original Message -----
Sent: Tuesday, June 01, 2004 9:29
AM
Subject: RE: [DUG] Temporary Tables &
Interbase / Firebird
The lists and news groups (see the firebirdsql site) are
very busy... almost to much to take in... 50-150 messages a day, but there is
a lot of expert knowledge there.
One of the first things to look at is how are you
accessing the DB, BDE, IBO, IBX etc... Everyone seems to try and avoid the BDE
(even Borland now a days I believe...).
Regards
Colin
Thanks to everyone who replied. There is
certainly a lot to think about.
We always try to use joins wherever possible and
I agree that sub selects are as slow as the plague :). We are using
Firebird and hoping to maintain IB compatibility if possible. This is
our first major FB / IB application and as such are very much at the novice
stage of things. Do you have recommended text or web site etc
?
I have seen some people mention temptables in
reference to IB / Firebird etc. Are these simply tables created in the
DB, used and then deleted (Drop table xxxx), or are they something else
special?
You (and others) mention using a stored procedure
to work around this. I understand this might be useful if the temp table
is for display purposes however in this specific case an empty table is
created with a variable number of fields and used for entry
/ processing. Can a stored procedure be used in this
case?
Sorry if this topic should be in the DUG DB, I
will subscribe shortly.
Rob Martin
Software Engineer
phone 03 377 0495
fax 03
377 0496
web
www.chreos.com
----- Original Message -----
Sent: Monday, May 31, 2004 5:56
PM
Subject: Re: [DUG] Temporary Tables
& Interbase / Firebird
Robert,
I think IB 7.1 supports temporary
tables, but I have never found a good reason to use them in FB.
A dBase app is necessarily going to be
very different from one that works with either IB or FB, so you are going to
find that you have to re-think alot of the things that you do.
Stored procedures often help fill the
void when you think that you need a temp table, but the FB/IB language is so
rich that you can often get away without having to even think in terms of
temp tables.
I've converted a whole bunch of apps
from Paradox/dBase to IB/FB, and have found that the best solution is to
re-think the way that you want to access the data. A good tool that
can show you not only query plans, but also number of reads is
essential. Sometimes a full table scan in IB/FB is more efficient than
trying to jump through hoops ensuring that the index that you think is the
best is the one used.
Stuff that looks like it would suck
performance-wise in Paradox or dBase is often very fast when you let the
server do all of the work. Also, if you brush up on using joins, you
can make the server respond much faster than you could ever get a flat-file
db to respond.
Lots of people come unstuck when trying
to use sub-selects in queries. These will always run slow, and can
almost always be re-written as joins which run quickly, but the join syntax
to avoid sub-selects is often not available in dbAccess methods like the
BDE.
There are some cool functions (such as
coalesce) which are available in FB (but not IB) which can remove
sub-selects & make your query faster, but unfortunately they also tie
you to a specific engine. I like to use the "break" keyword in my FB
databases, but it doesn't work in IB.
If you give us specific examples, I'm
sure that members of the community can show you how to translate what you
want to do in dBase to what you should ask a FireBird server to
do.
HTH
Trevor
----- Original Message -----
Sent: Monday, May 31, 2004 4:25
PM
Subject: [DUG] Temporary Tables &
Interbase / Firebird
Hi
As you may have gathered we are moving from
Dbase to Firebird. An app we have makes some temporary (dbase)
tables and after processing disposes of them. I know we can create
tables in FB / IB on the fly but we are reluctant to risk polluting our
database with these temporary tables. Any suggestions greatly
appreciated :)
Rob Martin
Software
Engineer
phone 03 377 0495
fax 03 377
0496
web www.chreos.com
_______________________________________________
Delphi mailing
list
[EMAIL PROTECTED]
http://ns3.123.co.nz/mailman/listinfo/delphi
_______________________________________________
Delphi mailing
list
[EMAIL PROTECTED]
http://ns3.123.co.nz/mailman/listinfo/delphi
Attention:
The information in
this email and in any attachments is confidential. If you are not the intended
recipient then please do not distribute, copy or use this information. Please
notify us immediately by return email and then delete the message from your
computer. Any views or opinions presented are solely those of the author.
_______________________________________________
Delphi mailing
list
[EMAIL PROTECTED]
http://ns3.123.co.nz/mailman/listinfo/delphi
