0

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):

  1. There is a tool that handsles importing exporting files (mostly CSV) to the DB
  2. the tool handles the common parts ie logging or moving file based on success/error
  3. there are more interfaces, depends on deployment configuration, lets assume ... on avarage 10-20 executions per day
  4. XML contains phases for imort - init, validate, import/export, deinit
  5. 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'
2

I doubt that the example you provide is a valid use case for temporary procedures (I don't see any benefit here to using #temp procedures over permanent procedures), but for #temp tables, which have a much wider set of use cases, the only way to fight these arguments with policy-setters is to run the code - using a full load and during typical workload activity - without using the #temp tables. If you can demonstrate to them that even one of these processes works worse without the #temp table, then that should be a valid counter. If they still won't listen, then this is not a question for this site - maybe workplace.

  • Well yes, temp procedures are .... convenient. Deployment wise, given that they are used only in one place and one place only, its simplier and easier and overall better to use temp procedures then to deliver extra packages. But worst-case, normal SP can be used, referencing temp tables. sure As for temp tables, thats the problem, because its easier to use #tables to store connection specific data then to use permanent table and some ID to distinguish between connections... – Vladislav Zalesak Jul 8 '14 at 15:48
1

No there are no specific bad things about using temp tables and temp procedures. For the temp tables you'll want to make sure that you've got indexes as needed when querying from them, but that applies to normal tables as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.