none
Loading large volume of data from SQL Server to list object RRS feed

  • Question

  • We have a requirement to send large volume of data from SQL server to WEB API application. Approximately  20000 records * 100 columns. Currently we are using SQLDataReader to load data into the list object. This is done in a while loop and for the above data it is taking more than a minute (>1). We want to bring this down to single digit secs.

    Code we are using …
    using (SqlConnection sqlCon = new SqlConnection(CommonHelper.GetSqlConnectionString))
                {
                    sqlCon.Open();
                    using (SqlCommand sqlCmd = new SqlCommand("spname", sqlCon))
                    {
                        sqlCmd.CommandTimeout = CommonHelper.GetConnectionTimeout();
                        sqlCmd.CommandType = CommandType.StoredProcedure;
                        using (SqlDataReader objDataReader = sqlCmd.ExecuteReader())
                        {

    Dictionary<string, int> ordinalDict=GetOrdinals(objDataReader);

                            while (objDataReader.Read())
                             {
                                Opportunity opp = ParseData(objDataReader, ordinalDict);
                                opplist.Add(opp);
                            }
                         }
                       }
            }


    protected user ParseData(IDataRecord vobjDataReader,Dictionary<string, int> ordinalDict)
            {
    user objusr = new user ();
                objusr.name = GetDataValue<string>(vobjDataReader, opptyDict["name"]);
                objusr.manager = GetDataValue<string>(vobjDataReader,opptyDict["manager"]);
                objusr.salary = GetDataValue<decimal?>(vobjDataReader, opptyDict["salary"]);
                objusr.dateofbirth = GetDataValue<DateTime>(vobjDataReader, opptyDict["dateofbirth"]);

    }

       protected T GetDataValue<T>(IDataRecord vobjDataReader, int intOrdinal)
            {

                  if (!vobjDataReader.IsDBNull(intOrdinal))
                    return (T)vobjDataReader.GetValue(intOrdinal);
                else
                    return default(T);
    }

    We would like to know..

      1.  What is best approach to load data (from SQLDataReader or other) into a list object (Data reader/dataset/sending data as xml from Stored procedure)?
      2.  What is the best mechanism to transfer such large data from SQL Server to WEB API application?
    Wednesday, April 17, 2013 2:07 AM

Answers

  • This forum is about DataSets. That is a certain collection with around it all kind of adapters and around those again all kind of wizards.

    Your question does fall in now way in that category. 

    Try the more general forum.

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/threads

     Be aware the dataset does in fact what your code does but than more generic. But the dataset is not faster. No data retrieval is faster than the smallest part of a pipeline. Personally I think that looking into code for that is starting at the broadest part of the pipeline so you will only gaining some milliseconds by starting at that part. 


    Success
    Cor


    Wednesday, April 17, 2013 10:56 AM

All replies

  • This forum is about DataSets. That is a certain collection with around it all kind of adapters and around those again all kind of wizards.

    Your question does fall in now way in that category. 

    Try the more general forum.

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/threads

     Be aware the dataset does in fact what your code does but than more generic. But the dataset is not faster. No data retrieval is faster than the smallest part of a pipeline. Personally I think that looking into code for that is starting at the broadest part of the pipeline so you will only gaining some milliseconds by starting at that part. 


    Success
    Cor


    Wednesday, April 17, 2013 10:56 AM
  • XML should also be a good approach. Check with lazy loading also

    Mark Answered, if it solves your question and Vote if you found it helpful.
    Rohit Arora

    Wednesday, April 24, 2013 12:48 PM
  • Hello, you can use T-SQL to reach this goal.

    1. create a stored procedure with two parameters in it: @RowSize and @PageNum

    2. change your SqlCommand, add two parameters for stored procedure

    3. select datas with responding page number

    and here is the T-SQL I used:

    declare @RowSize int
    declare @PageNum int
    
    set @RowSize = 20 -- count of data rows per page
    set @PageNum = 1 -- number of page
    
    SELECT * FROM 
    (
        SELECT rank() OVER ( ORDER BY [The Primary Column of Your Tabel]) AS RankNumber, * FROM
        (
            SELECT *
            FROM [Your Tabel Name]
        )
        AS t1
    ) AS t2
    WHERE RankNumber between @RowSize *(@PageNum - 1) + 1 and @RowSize * @PageNum
    



    Best regards

    Friday, April 26, 2013 10:06 AM