Performance Enhancement Permanent Tables,Table DataType and Temp Tab

[nikhilparchure]

Hi Guys,
We have a application for reporting purpose.
there has been a suggestion to use-query-drop permanent tables instead of using temporary tables Or table datatypes. This would obviously improve performance. Yes it does.
Although the reports wont be run simultenously hence duplication of tables would be avoided.
I somehow am not for this , since I am very for using temporary table and table datatypes.

Can anyone help me in finding the pros and cons for using such approach.


Regards
Nikhil

 

[nigelrivett]

>> use-query-drop permanent tables
what do you mean by that?

table datatypes will be faster than temp tables for small tables - temp tables may be faster for large table (and you can index them) as they give more optimisation options.

With permanent tables you can truncate them at the begining of the SP and as long as you single thread it shouldn't be a problem.

I would go with temp tables unless you have data that you want to hold in the database.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nikhilparchure]

Thanks nigel for the coments.
Really appreciate it....

something more i would like to ask for

Well I know the use of physical table instead of temporary tables speed up the operation but I want some quantifying
reasons why they are the latter is slower that the using
physical tables.