Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'
share|improve this question
    
You're going to have a pretty hard time getting specific answers without providing specific details of your situation. For certain use cases, #temp tables are probably the best thing to use, and for others, they might spell a horrible and slow death for your app. The answer to your question is therefore *it depends*™ –  Max Vernon Jul 8 at 13:29
    
Also, what version of SQL Server are you using? I'm assuming this is SQL Server. –  Max Vernon Jul 8 at 13:30
1  
#Temp tables are most definitely NOT a bad practice. If you need to work with intermediate result sets of any size, they are the way to go. @table variables have a host of issues that are covered elsewhere on this site. The use case for #procs is a lot narrower but like Max said, we need specifics to give any concrete feedback. –  JNK Jul 8 at 13:35
1  
As for your answer to "is it bad"? -> Microsoft supports the use of both #temp tables and temporary procedures, and uses them in officially supported products, so I doubt very much that there is any official documentation that states their use is not in line with "best practices". –  Max Vernon Jul 8 at 13:35
1  
Have you thought of using SSIS with the task that you are trying to do as SSIS will be best suited in your scenario and it has optimizations like fast load, etc. Possibly just call it using your front end or schedule it using SQL agent job. –  Kin Jul 8 at 13:47
show 6 more comments

2 Answers 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.

share|improve this answer
    
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 at 15:48
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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