> What I have is a SQL query, which when executed in Query Analyzer
> takes about 10-12 seconds. From the time I issue the query, I see an
> initial lag of about 1 seconds after which the grid starts getting
> populated, and then runs its course.

> I use the SqlDataReader class off the SqlCommand class to make the
> same query (identical), and yet it takes close to 9 or 10 seconds
> before even the first row is retrieved by the application. I am not
> saying that it takes 9 or 10 second for my DataGrid to start being
> populated; it is simply that the first Read operation of the
> SqlDataReader after I connect (which is instantaneous), that takes 9
> to 10 seconds. Once the data retrieval starts, the grid populates in
> short order.
>
> Are there any pointer by which I can get just a few rows fast enough
> to give the user the impression that the query is executing. In other
> words, what techniques can I use to get the same sort of
> responsiveness as in the SQL query analyzer?

> In case it is relevant, there are some 250,000 rows returned by the
> query.

On Jan 31, 3:09 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (shripathikam...@gmail.com) writes:
> > What I have is a SQL query, which when executed in Query Analyzer
> > takes about 10-12 seconds. From the time I issue the query, I see an
> > initial lag of about 1 seconds after which the grid starts getting
> > populated, and then runs its course.
>
> Eh? If you have results to grid in QA, you will not see anything
> until the batch has completed.
>
> Do you mean Results to text? In this mode, rows are displayed as
> they arraive.
>
> > I use the SqlDataReader class off the SqlCommand class to make the
> > same query (identical), and yet it takes close to 9 or 10 seconds
> > before even the first row is retrieved by the application. I am not
> > saying that it takes 9 or 10 second for my DataGrid to start being
> > populated; it is simply that the first Read operation of the
> > SqlDataReader after I connect (which is instantaneous), that takes 9
> > to 10 seconds. Once the data retrieval starts, the grid populates in
> > short order.
>
> > Are there any pointer by which I can get just a few rows fast enough
> > to give the user the impression that the query is executing. In other
> > words, what techniques can I use to get the same sort of
> > responsiveness as in the SQL query analyzer?
>
> Try adding OPTION (FAST 10) at the end of the query. This may have
> an adverse effect on the total execution time though.
>
> > In case it is relevant, there are some 250,000 rows returned by the
> > query.
>
> But it may also be an issue with ADO .Net when you have so many rows.
> I would expect SqlClient to not buffer all rows before it opens the
> reader, but maybe it does. Maybe they know more in a group like
> microsoft.public.dotnet.framework.adonet.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

On Jan 31, 9:45 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> DataReader by definition returns data as the query executes, one row at a
> time. The only buffering occurs on the client where data is kept in the
> network buffer until requested by the Read method. Based on that you should
> be seeing faster response.
>
> Do you use an ORDER BY clause in your query? That could be the issue, since
> in that case the data needs to be sorted on the SQL Server side before the
> first row is returned.
>

> Is there any reason to display all 250,000 rows in your DataGrid?

> I have
> implemented many similar client applications and the best way to handle huge
> load has been to use paging. Normally 25-100 records per page are easy for
> users to look through. With that page size returning data is instantaneous.
> Then provide direct links to the other pages and allow users to jump quickly
> to a page.
>

> Here is an article with a few useful hints on optimizing ADO.NET
> performance:http://msdn2.microsoft.com/en-us/library/ms998569.aspx
>

>> Do you use an ORDER BY clause in your query? That could be the issue,
>> since
>> in that case the data needs to be sorted on the SQL Server side before
>> the
>> first row is returned.
>>
>
>
> I don't, but I do not see the relevance. The *exact* same query in
> the Query Window of Management Studio starts executing immediately,
> and the grid starts populating within a second, where as the
> DataReader takes about 8-10 seconds before the first row is returned.
>

>
>> Is there any reason to display all 250,000 rows in your DataGrid?
>
> Yes, it is a customer requirement. The idea is to start populating,
> and continue populating the grid in the background, and the user can
> see that more rows are being retrieved, and he can cancel the
> retrieval at any time.
>
> Look at the Query Window in the Management Studio, that is exactly the
> behavior I seek.
>

> I must make a correction: It was not Query Analyzer, it was the Query
> Window in the Management Studio tools

On Feb 2, 3:08 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (shripathikam...@gmail.com) writes:
> > I must make a correction: It was not Query Analyzer, it was the Query
> > Window in the Management Studio tools
>
> Since MgmtStudio uses SqlClient to connect to SQL Server, this means
> that you should be able to do this in your program as well.
>
> If you haven't already, you should probably talk with the people in
> microsoft.public.dotnet.framework.adonet. It probably helps if your
> post your actual code, so that any flaws can be uncovered.
>