SQL Server Performance

Help Regarding Migration logic

Discussion in 'SQL Server 2005 Integration Services' started by iamafreak, Apr 16, 2007.

  1. iamafreak New Member

    i have to migrate data from a csv file to a SQL server table. The csv file will contain 2 fields area and number. The destination table will have 3 columns : Area, Range From and Range To. The logic behind the migration is to uplaod the numbers for the sam Area in a Range rather than individual rows in the database.

    For Example if csv file looks like below:

    090,1001
    090,1002
    090,1003
    090,1004
    090,1009
    090,1010
    090,1011
    091,1001
    091,1021



    Then the destination table should look like

    AreaRangeFromRangeTo
    09010011004
    09010091011
    09110011001
    09110211021

    Please not that the ranges are merged.


    Now if next time my csv file contains

    090,1003
    090,1004
    090,1005
    090,1006
    090,1007
    090,1008
    091,1000
    091,1002



    Then for the area 090 and number 1003 and 1004 these rows should be ignored as they already exist in the range 1001-1004 in the destination table. The other rows from 1005 to 1008 will be merged to the existing ranges in the table 1001-1004 and 1009-1011 making it 1001-1011 makin the tbale look like

    AreaRangeFromRangeTo
    09010011011
    09110001002
    09110211021


    The logic that i am using right now is to have a OLE DB command component as the destination which first searches the table to check if the range already exists. If it exists it ignores the row. If it finds that the inpout row can form a new range it first deletes that range from the table and inser the new row with new RangeFrom and RangeTo. This process that i am using is taking a lot of time as there are about millions of rows to be migrated.

    Can anyone suggest some better logic or way to accomplish the same so that upload time could be reduced.




    Thanks
    Freak
    http://techyfreak.blogspot.com
  2. ndinakar Member

    I think one solution is to get all the data in the CSV into a staging table as-is. Then write a WHILE loop to loop through each record, storing the previous values and comparing against each other. Its easier to write queries on tables than against a text file. You can make this stored proc generic enough so it always goes through the staging table and updates your main table. Remember to clean up the stating table once processing is done.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  3. iamafreak New Member

Share This Page