R:azzak:
Good to hear from you, thank you. That's the best mini-tutorial on temp
tables I've yet seen.
The creation and management of temporary tables is a long-established
practice for me. The SET ERROR MESSAGE 2038 [ON|OFF] syntax by default
brackets all my DROP TABLE commands. No mystery, there.
In the circumstance prompting my question today, a temp table definitely
exists, and has just been populated with rows in a recursive process
which either has or has not produced a desired value in one column of
the final row. If it has not produced that value, I adjust several
variable values, obliterate the just-created values, and run the process
again. I may have to run the process several hundred times for many
thousands of rows, something which R:BASE excels at.
I rephrase my question as: "What's the fastest, cheapest and, so far as
system memory management is concerned, most efficient method of
obliterating these previous values? Delete rows from an existing temp
table, or drop and recreate the temp table?"
I'm thinking it's best to just Drop/Create, but, given the rapid,
repeated creation/destruction of this temp table, I thought I'd get blue
clue before committing to final coding.
Thanks as ever, looking forward to (my first) Conference in Vegas!
Bruce Chitiea
SafeSectors, Inc.
909.238.9012 m
------ Original Message ------
Sent: 5/20/2018 12:47:04 PM
Subject: Re[2]: [RBASE-L] - Temp Table: Delete Rows or Drop Table?
From: "A. Razzak Memon" <[email protected]>
To: [email protected]
Cc:
Bruce:
To DELETE ROWS, the temp TABLE must exist first. otherwise you will run
into another -ERROR- message as follows:
-ERROR- tablename is an undefined table. (2038)
In that case, you have to first check the existence of TEMPORARY table
and then use the DELETE ROWS from that TEMP table.
-- Example
SET VAR vRows INTEGER = 0
SELECT COUNT(*) INTO vRows INDIC iv1 FROM SYS_TABLES WHERE
SYS_TABLE_NAME = 'tablename'
IF vRows = 0 THEN
CREATE TEMPORARY TABLE tablename ...
or
PROJECT TEMPORARY tablename FROM existingtablename USING columnlist
WHERE COUNT = 0
ELSE
DELETE ROWS FROM tablename
ENDIF
CLEAR VARIABLES iv1,vRows
So, why go through all that checking, etc.?
It is much simpler to just use the technique as follows:
SET ERROR MESSAGE 2038 OFF
DROP TABLE tablename
SET ERROR MESSAGE 2038 ON
CREATE TEMPORARY TABLE tablename ...
or
PROJECT TEMPORARY tablename FROM existingtablename USING columnlist
WHERE COUNT = 0
Now that you know the difference, pick the best approach that fits your
programming logic.
Hope that helps!
Very Best R:egards,
Razzak
At 01:30 PM 5/20/2018, Bruce A. Chitiea wrote:
Albert:
Thanks. RSyntax has a very good section: "Temporary Tables and Views",
and your DROP TABLE code is correct.
Coding aside, I'm just wondering if there are any operational
advantages to DROP or DELETE ROWS
<http://www.rbase.com/support/rsyntax/rbgx/index.html>http://www.rbase.com/support/rsyntax/rbgx/index.html
Bruce
------ Original Message ------
Sent: 5/20/2018 10:06:25 AM
Subject: Re: [RBASE-L] - Temp Table: Delete Rows or Drop Table?
From: "Albert Berry"
<<mailto:[email protected]>[email protected]>
To: "[email protected]"
<<mailto:[email protected]>[email protected]>
Cc:
Somewhere back in the dark ages of history I began to drop and
recreate. I think the temp table is a $$$ file on the drive, so I think
I did it due to possible increase in the size of the $$$ file.
I have no idea whether or not I am correct but I have this code
whenever I work with Temp Tables. They seem to take an infinitesimal
amount of time to process.
SET ERR MESS 2038 off
DROP TABLE tmpTableName
SET ERR MESS 2038 on
CREATE TEMP TABLE …¦.
Albert
On May 20, 2018, at 10:19 AM, Bruce A. Chitiea
<<mailto:[email protected]>[email protected]> wrote:
All:
Which is preferable from a speed/memory management perspective:
a) DROP TABLEÂ humpty_tt, or
b) DELETE ROWS FROMÂ humpty_tt
I have a brute-force, iterative process which fills and clears a temp
table (say, humpty_tt) for as many iterations as it takes to produce
the desired result. Possibly LOTS of iterations, several thousand rows
per iteration.
Working RAM capacity is not an issue.
Thanks
Bruce A. Chitiea
SafeSectors, Inc.
909.238.9012 m
-- For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
--- You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
For more options, visit https://groups.google.com/d/optout.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
