Re: Returning records from a Store Procedure

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Roger (Roger_at_discussions.microsoft.com)
Date: 10/13/04 

Date: Wed, 13 Oct 2004 06:11:06 -0700

It is being assigned to a datareader. The statement goes as:

CommandObj.Commandtext = "Stored Procedure Name"
DatareaderObj = CommandObj.ExecuteReader

while DatareaderObj.Read
... loading data into grid
end while

Runs decently when the Stored procedure returns a small data set. But, the
dataset can get as large as 35,863. That takes 10 minutes to finish loading.

If I was just using a simple select statement and use the dataadapter object
to fill the datatable and the associate the datatable to the grid's
datasource property, it runs in like 30 seconds.

reason why I am using a stored procedure is because I am avoiding writing
one big query that will have like 8 to 10 table joins and 3 of them will be
outer joins.

The stored procedure runs quick. It slows down with it gets to the while loop.

We cannot be the only company that has written a stored procedure that
returns thousands of records. Is there any other way to handle this. Thanks.

"Jerry Pisk" wrote:

> Are you using DataReader or DataAdapter? DataReader is the fastest ADO.Net
> object to get data out of a database, but if you're loading it to a grid it
> probably doesn't make much difference as the grid itself will be pretty slow
> (since it has to hold all data in memory).
>
> Jerry
>
> "Roger" <Roger@discussions.microsoft.com> wrote in message
> news:9922CF60-1774-4239-B144-E70B5CFAD037@microsoft.com...
> > Is there any other way to load data returned from a stored procedure other
> > then using a Data Reader.
> > I am loading the data returned into a data grid. It works fine if the
> > amount
> > of records returned is small, but sometimes it will return several
> > thousand
> > records.
> >
> > And to load the data grid using the data reader, slows the program down
> > drastically. Any suggestions.
> >
> > I tried using views and I could still use the data adapter to fill the
> > data.
> > It runs okay for certain situations.
> >
> > Thanks.
> >
> >
>
>
>

Relevant Pages

  • Re: Returning records from a Store Procedure
    ... Using the DataReader Read yourself is not really helping. ... help is loading the rowset a few hundred rows at a time. ... > Runs decently when the Stored procedure returns a small data set. ... >> object to get data out of a database, but if you're loading it to a grid ...
    (microsoft.public.data.ado)
  • Re: DataGrid Colum Names
    ... Should I create a datatable from the stored procedure? ... > You'll reference the DataTable that you bound the grid to. ... > grid is just the presentation of the underlying data obejct. ...
    (microsoft.public.dotnet.languages.vb)
  • Unable to retrieve schema - Divide by zero error encountered
    ... Here's the funny thing - the grid works fine. ... I get to the point where I can test the data source, ... calls a stored procedure with two arguments) are correct. ... even Visual Studio manages to get the data in the data source configuration ...
    (microsoft.public.dotnet.framework.aspnet.datagridcontrol)
  • Re: Stored Procedure Help
    ... if you are expecting the results of the stored procedure to display in the ... refresh the grid. ... Be sure to disable controls before calling the close, ...
    (borland.public.delphi.database.ado)
  • Re: Error sql parameter SqlDbType.Float wont accept null value, A
    ... the field (which is NULL if empty, ... >> I'm using a grid and when a user edits the grid, ... >> The stored procedure allows nulls for this float field. ...
    (microsoft.public.dotnet.framework.adonet)