I am working on a project and part of it is an interface tool that executes SQL code stored in a xml file to import/export files.
In the file, temp procedures and temp tables are used quite heavily (list of imported/exported records, logs, support tables, stored procedures for easier reading etc).
It has been decided by powers above and out of reach (luckily for both them and for me ;) ) that creating temp tables and temp procedures called during execution is 'not so good, maybe even bad' and has to be removed.....
This got me thinking and I would like to know (as I can't ask THEM), did anyone come over some general knowledge stating that 'using temp tables and procedures is not so good and should be avoided'
I know this question is a bit more vague then usual and is a bit subjective, but I would be really thankful for any insight from other, more experienced, wiser heads.
Details (obviously , i cannot be totally specific, so please bear with some obfuscation):
- There is a tool that handsles importing exporting files (mostly CSV) to the DB
- the tool handles the common parts ie logging or moving file based on success/error
- there are more interfaces, depends on deployment configuration, lets assume ... on avarage 10-20 executions per day
- XML contains phases for imort - init, validate, import/export, deinit
- data size varies (so does speed), usually takes less then 10 min to run, some take 1 hour maybe 1,5 hours. CSV have some 100s some have 1000s some have 10000s rows, less then 100 'columns in CSV'