none
temp table size in ssis

    Question

  • I have 100 table (main) in MS SQL. Along with this, I have other 100 temp tables. For example test, test_tmp,test1,test1_tmp etc

    I get data into these temp tables from different sources (oracle, MS SQL, db2, excel, sharepoint etc) first, and by using stored procedure, I move data into main tables.

    My problem - temp tables size is occupying huge space compared to main tables. I don't understand why is that? Is SSIS package takes double space, while dumping data into temp tables? Please help me  in understanding this problem.

    For example -

    test1         5000(rows),53000(reserved),40680(data size),232(index size),12508(unused size)

    test1_tmp 5000(rows),464000(reserved),484680(data size),16(index size),30494(unused size)

    Monday, July 22, 2013 5:38 PM

Answers

  • If size of table is big/huge and you are incrementing data on temp table i.e you are using temp table as staging table, I would rather suggest use real tables with truncate and insert strategy. You can also purge and compress the staging table and you can configure purging and compression.

    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, July 22, 2013 6:41 PM

All replies

  • Why are you using temp table for getting the data from different sources (oracle, MS SQL, db2, excel) Use SSIS Dataflow without using temp tables. 

    to answer your question regarding temp table NOT dropping it explicitly and letting SQL server do the cleanup instead, takes advantage of the temp-table caching feature, this might be increasing the space used by temp tables


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, July 22, 2013 5:50 PM
  • As a part of good practice and for data massaging, we have introduced temp tables. Could you please explain me about temp-table caching feature. How do I implement that in the above scenario. Please help me out.
    Monday, July 22, 2013 5:54 PM
  • below link will help you 

    http://www.sqlservercentral.com/Forums/Topic510942-360-1.aspx


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, July 22, 2013 5:57 PM
  • Thanks for your swift reply. How do I make use of this in the above scenario. Anyway, am also looking this link...
    Monday, July 22, 2013 6:01 PM
  • it would be helpful if you can explain or rather teach me How temp table here is a good practise and why data flow can not be used for messaging please

    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, July 22, 2013 6:29 PM
  • Sometimes, we are incrementing data in few of main tables. In that we definitely need temp tables. For this, we are getting data into temp tables first, then moving to main tables, this will make our job easy. Please suggest me if you have any good or other practice.
    Monday, July 22, 2013 6:32 PM
  • If size of table is big/huge and you are incrementing data on temp table i.e you are using temp table as staging table, I would rather suggest use real tables with truncate and insert strategy. You can also purge and compress the staging table and you can configure purging and compression.

    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, July 22, 2013 6:41 PM