Limiting resultset from database

Question

In our C# service I need to provide a quick fix for an application crash scenario.

The service crashes when trying to run a stored proc because of the huge volume of data it returns. We are thinking of limiting the result set at the services side.

OracleCommand oraCommand = GetStoredProcedureCommand("SomeProc");
oraParam = new OracleParameter("param1", OracleDbType.Varchar2,               
                                     ParameterDirection.Output);
oraParam.Size = 32000;
oraCommand.Parameters.Add(oraParam);
oraParam = new OracleParameter("param2", OracleDbType.Varchar2, 
                                  ParameterDirection.Input);
oraParam.Value = strSelectedUser;
oraCommand.Parameters.Add(oraParam);

OracleDataAdapter oraDataAdapter = new OracleDataAdapter(oraCommand);
oraDataset = new DataSet();
oraDataAdapter.Fill(oraDataset);//I want to limit the number of records read to the servcie

The database still execute the proc fully,which is bad, but we don't have the time to fix that now. We need to limit the data which is retrieved to the C# service, hence preventing a crash.

How can we limit the data? Note that if it is possible to use the dataset we have to stick with that as the dataset it passed around in the server. But if no other way, I can refactor the code.

Can you use SELECT TOP xxx in your stored procedure? http://stackoverflow.com/questions/2498035/oracle-select-top-10-records — WraithNath, Feb 21 '14 at 11:37
@Alexander, Memory. The service can't cope with the data returned. — Jimmy, Feb 21 '14 at 11:40

score 3 · Accepted Answer · edited May 23 '17 at 12:20

Option 1:

Can you use SELECT TOP xxx in your stored procedure?

Oracle SELECT TOP 10 records

This will return the first xxxx number of records for the query allowing you to specify a limit.

you may want to think about having a different stored procedure for the service to call with the limited result set so the other queries return all data, or make the stored procedure have the number of records to return as an optional parameter.

Option 2:

Another option would be to using paging to read and process a number of records at a time.

Im not sure of the syntax for oracle, but it would look something like this in SQL:

This would probably be a better option as you are still processing all records from the query, just not all of them at once.

        /// <summary>
        /// Gets a list of Customers by name (with paging)
        /// </summary>
        /// <param name="oSageDatabase">The Sage database to query</param>
        /// <param name="startIndex">The start index</param>
        /// <param name="endIndex">The end index</param>
        /// <param name="filter">The filter</param>
        /// <returns>List of suppliers</returns>
        public static List<SLCustomerAccount> GetCustomersByAccountNumber(SageDatabase oSageDatabase, int startIndex, int endIndex, string filter)
        {
            try
            {
                string query = @"
                SELECT * 
                FROM
                (
                    SELECT [SLCustomerAccount].*, 
                           ROW_NUMBER() OVER (ORDER BY [SLCustomerAccount].[CustomerAccountNumber]) AS RowNumber
                    FROM   [SLCustomerAccount] 
                    LEFT JOIN [SLCustomerLocation]
                    ON [SLCustomerAccount].[SLCustomerAccountID]
                    = [SLCustomerLocation].[SLCustomerAccountID]
                    AND [SLCustomerLocation].[SYSTraderLocationTypeID]=0
                    WHERE  [AccountIsOnHold]=0
                    AND    [CustomerAccountNumber] + ' ' + [CustomerAccountName] + ' ' + [CustomerAccountShortName] + ' ' + ISNULL([SLCustomerLocation].[PostCode], '') LIKE @Filter
                ) as p
                WHERE p.RowNumber BETWEEN @StartIndex AND @EndIndex
                ";

                using (SqlCommand oSqlCommand = new SqlCommand(query))
                {
                    oSqlCommand.Parameters.AddWithValue("@StartIndex", startIndex + 1);
                    oSqlCommand.Parameters.AddWithValue("@EndIndex", endIndex + 1);
                    oSqlCommand.Parameters.AddWithValue("@Filter", String.Format("%{0}%", filter));

                    using (DataTable dtSupplier = DataTier.ExecuteQuery(oSageDatabase.ConnectString, oSqlCommand))
                    {
                        return (from DataRow dr
                                in dtSupplier.Rows
                                select new SLCustomerAccount(dr, oSageDatabase)).ToList();
                    }
                }

            }
            catch (Exception)
            {
                throw;
            }
        }

this will allow you to loop through say 100 records at a time from SQL which will help on memory etc, it depends on what you are doing with the data.

so first run you would pass in to the query 0 for start index, 100 for end index for example, then process the records, and increase your variables to get the next page of results. eg 101 start, 201 end

Do you think it is good idea to limit results of a stored procedure at the database level? What if there is a different service which can get streaming data from the db without any issues? — Jimmy, Feb 21 '14 at 11:43
True, thats just my suggestion, I have added another comment suggesting adding the TOP xxx as an optional parameter or using a different sp to get a subset of results. — WraithNath, Feb 21 '14 at 11:46
Updated a second option using RowNumber to process all records, but not all at once — WraithNath, Feb 21 '14 at 11:55
I quite like the second option and there is no clear advantage in limiting the procedure with "Top" as the full query will be executed anyway(right?). I will use a datareader to limit thee pool of data from the db.Thank you. — Jimmy, Feb 21 '14 at 12:03

score 0 · Answer 2 · answered Feb 21 '14 at 11:38

0

You should limit the data at the database level via modifying your query. This could be done, as WraithNath commented to your post, through using the SELECT TOP statement.

answered Feb 21 '14 at 11:38

Christos

53,228
8
76
108

score 0 · Answer 3 · answered Feb 21 '14 at 11:49

You should limit the data at database side with some appropriate condition or TOP statment

But still as per your request if you want to do it at service use the below code

oraDataset.Tables[0].AsEnumerable().Take(300);

Instead of 300 you can put number as per your requirement

Limiting resultset from database

3 Answers3