You must Sign In to post a response.
  • Category: .NET

    Comparison of Two Huge List SQL or .Net or Excel

    Hi,

    I have two huge list say
    StagingList containing 130K records and ProdList containing 129K records.

    Could you please let me how to compare and find

    1. records that exist in StagingList not in Prod List
    2. records that does not exist in stagingList but exist in Prod List.

    Regards,
    Mahe
  • #768351
    Hi,

    You can clearly use IEnumerator or IEnumerable in .NET technology to compare huge datas.
    We can easily match/Search data with the list of values.
    Its is kind of row by row comparison but the it will give you effective result. Instead pushing data to Excel format we can push it to CSV format file and we can upload this text file in the Enumerator program

    Kindly check the complete coding about the comparison coding on the below link.


    http://www.splinter.com.au/reconcilingcomparing-huge-data-sets-with-c/

    Thanks,
    Mani

  • #768454
    Hi,

    My suggestion is better to use SQL query because in any application when we had discussion regarding performance, the main thing we should consider is no of call's and data. For example if you get huge data into .net code and then filter it's more than one call and huge data, coming back to sql in single call and minimal data we can filter it.

    You can use Not Exists or use Joins in Sql and try to get those details.

    Hope this helps you..

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #768478
    Hi,

    Refer the below..

    According to my understanding both tables are having same structure..
    1. records that exist in StagingList not in Prod List => Records only in StagingList
    2. records that does not exist in stagingList but exist in Prod List. => Records only in ProdList

    you can use left join for this
    for your first one 1. records that exist in StagingList not in Prod List i.e.
    To retrieve records in staginglist which are not in ProdList:
    SELECT stag.* FROM [StagingList] stag LEFT JOIN [ProdList] prod
    ON prod.ID = stag.ID
    WHERE prod.ID IS NULL

    You have to mention the Primary key columns in On and where conditions. i.e for ex: in your table id and dte is primary column
    ON prod.ID = stag.ID and prod.dte = stag.dte
    where prod.id is null or prod.dte is null

    If there is no Primary column on your table, you can use except
    select count(*) from [StagingList]
    except
    select count(*) from [ProdList]


    Hope this will help you.

    Regards,
    SonyShiva
    Never lose hope..You never know what tomorrow will bring


Sign In to post your comments