When faced with the task of copying a record in a database and modifying just a handful of values, I tend to use a temporary table to avoid writing out all of the unchanged columns. Does anyone know how this would affect performance on a large scale system?
A quick example (which also shows why I prefer the temporary table method):
Let's say I have a table mytbl
with 50 columns col1
...col50
. I want to insert a new record that is an exact duplicate of the row where col5 = 'Some Value'
except that col45
will be set to 'Some other value'
.
Method 1
CREATE GLOBAL TEMPORARY TABLE tmptbl AS
SELECT * FROM myschema.mytbl;
INSERT INTO tmptbl
(SELECT *
FROM myschema.mytbl
WHERE mytbl.col5 = 'Some Value');
UPDATE tmptbl
SET col45 = 'Some Other Value';
INSERT INTO myschema.mytbl
(SELECT * FROM tmptbl);
DROP TABLE tmptbl;
Method 2
INSERT INTO myschema.mytbl (col1,
col2,
col3,
col4,
col5,
col6,
col7,
col8,
col9,
col10,
col11,
col12,
col13,
col14,
col15,
col16,
col17,
col18,
col19,
col20,
col21,
col22,
col23,
col24,
col25,
col26,
col27,
col28,
col29,
col30,
col31,
col32,
col33,
col34,
col35,
col36,
col37,
col38,
col39,
col40,
col41,
col42,
col43,
col44,
col45,
col46,
col47,
col48,
col49,
col50)
SELECT col1,
col2,
col3,
col4,
col5,
col6,
col7,
col8,
col9,
col10,
col11,
col12,
col13,
col14,
col15,
col16,
col17,
col81,
col19,
col20,
col21,
col22,
col23,
col24,
col25,
col26,
col27,
col28,
col29,
col30,
col31,
col32,
col33,
col34,
col35,
col36,
col37,
col38,
col39,
col40,
col41,
col42,
col43,
col44,
'Some Other Value',
col46,
col47,
col48,
col49,
col50
FROM myschema.mytbl
WHERE col5 = 'Some Value';
How much overhead does creating/dropping a temporary table introduce? If, for example, this were part of a daily process on a production sized system, would the additional overhead be noticeable? I realize that depends a lot on the specifics of the system, but a general idea would be fantastic.