|
|
Posted by Erland Sommarskog on 01/31/07 23:09
(shripathikamath@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|