Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Problem while loading 5.5 million records into fact table?

Answered Problem while loading 5.5 million records into fact table?

  • Sunday, March 17, 2013 9:30 AM
     
     
    Sign In to Vote
    0

    I have increased buffer size to 36700160 and if I increase more it is giving error of allocation memory.

    It is extracting the data but the sort is not happening it got stuck for long time and never going thru.

    Can you please suggest why is this happening?

     

All Replies

  • Sunday, March 17, 2013 9:32 AM
     
     
    Sign In to Vote
    0
    Please check image.
     
  • Sunday, March 17, 2013 11:24 AM
     
     Answered
    Sign In to Vote
    0

    The Sort has to store all of the records in memory. 

    Can you sort the source in your ADO.Net source in the query on that source?  You can then change the IsSorted property on the ADO.Net source to true and the SortKeyPosition on the column that you are joining on.

    http://msdn.microsoft.com/en-us/library/ms137653.aspx

    http://bisherryli.wordpress.com/category/ssis-best-practices/page/2/ has pictures for setting the IsSorted and associated SortKeyPosition property for the column.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed As Answer by DeviantLogic Sunday, March 17, 2013 11:33 AM
    • Marked As Answer by webdev1986 Sunday, March 17, 2013 12:19 PM
    •  
     
  • Sunday, March 17, 2013 12:19 PM
     
     
    Sign In to Vote
    0

    Thanks for the reply Loki.

    That worked fine for one level. After Merge Join again I have to sort its output (with other key) and join it with other result set.

    There is no IsSorted option for Merge Join output transformation.

    Can you suggest how to sort this out?

     
  • Sunday, March 17, 2013 12:40 PM
     
     
    Sign In to Vote
    0

    Here is the screenshot. Again it stopped at Sort transformation I don't get how to sort on another key for merge join transformation.

     
  • Sunday, March 17, 2013 12:52 PM
     
     
    Sign In to Vote
    0

    Some options to look at.  If you are using any kind of join but full outer join, you can replace some or all of these merge joins with Lookups.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

     
  • Sunday, March 17, 2013 1:01 PM
     
     
    Sign In to Vote
    0

    Lookup! I will try that and will let you know. Thanks

     
  • Sunday, March 17, 2013 1:15 PM
     
     
    Sign In to Vote
    0

    Hi Loki,

    I cannot use the LookUp bcoz my connection is ADO.NET and my source tables are in MySQL.Any other suggestions?

    Thanks