RE: [DUG] Temporary Tables & Interbase / Firebird

[C Fraser]

Main web sites for FB are www.firebirdsql.com and http://www.ibphoenix.com/.   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 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Robert martin Sent: Tuesday, 1 June 2004 9:05 a.m. To: Trevor Jones; NZ Borland Developers Group -Delphi List Subject: Re: [DUG] Temporary Tables & Interbase / Firebird 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 ----- From: Trevor Jones To: NZ Borland Developers Group - Delphi List 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 ----- From: Robert martin To: [EMAIL PROTECTED] org. nz 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