Re: Temporary tables

In article <6b5r8l$o1l$1@godzilla.zeta.org.au>, Bryan Tonnet <batonnet@zeta.org.au> writes >In <6b4kqa$290@news.singlepointsolutions.co.uk> "Paul N. Daly" ><pndaly@globalnet.co.uk> writes: > >>Is it better to select into a temp table or create a temp table, prepare the >>select and then use an insert cursor to populate it? > select into temp table but only VERY VERY slightly. This involves less traffic between the application and the server:- select into temp ================ Application sends "SELECT..." Server replys "OK" One round trip on network.. Prepare ======= Application sends "CREATE TEMP" Server responds "OK" One round trip on network.. Application sends "prepare" Server reponds "OK" Second round trip on network.. Application send ".." etc Of course the extra overhead of 10-15 network packets is usually insigificant... >And the answer is........depends! I asked the same question some time back >and noone had any performance or coding etiquette worries with either method. >I find that SELECT INTO TEMP is somewhat restricting as the statement cannot >be run again to add additional rows to an existing table, and such like. > Try but you can then you insert into <table> select.... afterwards.. >I prefer CREATE TEMP TABLE, but it's annoying to not be able to use the >LIKE clause. At least with the SELECT INTO TEMP method, changes to the >database schema are picked up by the program with a simple recompile, but >CREATE TEMP TABLE requires a less lazy maintenance methodology :)> > -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care