Posted by shripathikamath on 02/02/07 00:38

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

Thanks, I'll try your suggestion.


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

[Back to original message]