Re: best way to retrieve thousands of records.

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

From: Sahil Malik (contactmethrumyblog_at_nospam.com)
Date: 12/02/04 

Date: Wed, 1 Dec 2004 22:36:12 -0500

I want to second Frans Bouma's reply - don't retreive that many records in
one go. I understand you should have a chunky not chatty interface, and
retreive as much as you can - but that doesn't mean retreive the lion, the
mouse and their children and their grandmothers with your data. There is
again no hard and fast rule about "how much is how much" - but 90,000 rows
is too much beyond doubt. A 1000 is hitting the limits of too much IMHO -
but anyway.

Have you considered what will it take to DataBind 90,000 rows to the UI?
I just answered another question on this newsgroup recently on which a nice
dude had 100,000 rows in a datatable and to remove half of them it took him
30+ SECONDS (yes not milliseconds).

Also imagine the kind of memory you are using up. (LOTS).

The beauty of ADO.NET is that you can call SqlDataAdapter.Fill multiple
times on the same dataset - why don't you fill the third table on demand -
and fill it with fewer rows? .. i.e code up another stored proc that accepts
an ID from Table2 as a parameter, and returns you rows matching *only* that
id.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

"jaYPee" <hijaypee@yahoo.com> wrote in message
news:7joqq0lvgb49ave7aqit9jhp09onj3922p@4ax.com...
> I have currently using a dataset to access my data from sql server
> 2000. The dataset contains 3 tables that is related to each other.
> parent/child/grandchild relationship. My problem is it's very slow to
> retrieve records from sql server.
>
> I'm using sqldataadapter.fill method to populate dataset.
> Parent table contains more than 3,000 records and child table contains
> more than 10,000 records and grandchild table contains more than
> 90,000 records as of now.
>
> I tried to limit the number of records to return by a select command
> using the criteria but my problem is I dont know how to limit the
> number of records return by a 3rd table (grandchild).
>
> I'm using textboxes in parent table and datagrid in 2nd and 3rd table.
> There is no problem querying the 1st and 2nd table but I don't know
> how to query the 3rd table. 3rd table must know first the primary key
> value of the 2nd table that is stored in a datagrid. but don't know
> how to do this.
>
> Anyone know what is the better way to access this data?
>
> I would love to read some article about techniques on accessing table
> that has a relationship up to 3rd table (grandchild).
>
> thanks in advance.

Relevant Pages

  • Re: best way to retrieve thousands of records.
    ... I understand you should have a chunky not chatty interface, ... i.e code up another stored proc that accepts ... > retrieve records from sql server. ...
    (microsoft.public.dotnet.framework.adonet)
  • handling errors from stored procs
    ... SQL Server 2000 ... comprise the call to a stored proc ... the stored proc gets run...if so, it would be nice if the Access app ... - when stored proc encounters error, it returns that error message ...
    (comp.databases.ms-access)
  • Re: Retrieve error text from extended stored proc
    ... was only on first execution, the following executions, error number was 0. ... However, use OSQL with the -m-1 switch, and you will see that the RAISERROR ... Tibor Karaszi, SQL Server MVP ... > When I call the stored proc from Query Analyzer and force an error, ...
    (microsoft.public.sqlserver.programming)
  • Re: dlookup with stored procs
    ... The lookup criteria is unique based on the primary key value but i'm missing ... Tried amending the Dlookup but as the error message tells me a stored proc ... In a SQL stored procedure you can use a join instead of the DLOOKUP ... David Portas, SQL Server MVP ...
    (microsoft.public.access.adp.sqlserver)
  • Re: localhost cannot browse asp page
    ... There is no such a thing SQL Server 2003, so I assume it is SQL Server 2005 Express Edition. ... "chunky" wrote in message ... website using my local host i am ...
    (microsoft.public.sqlserver.clients)