Querying towards a responsive UI

[shripathikamath@gmail.com]

I realize that this may not be the correct ng for this question, and
if so, I'd appreciate a pointer to the correct group.

I am not including DDL/DML for this question, because I do not believe
it is relevant.

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.

The UI responsiveness is impressive. Almost immediately, well, within
1 second at most, I see data appear from the server into my query
analyzer grid.

I'd like to get the same responsiveness if possible on my client
application written in C# using ADO.NET.


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.

Thanks for all helpful responses in advance.

[Erland Sommarskog]

Re: Querying towards a responsive UI

(shripathikamat h@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.publi c.dotnet.framew ork.adonet.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[Plamen Ratchev]

Re: Querying towards a responsive UI

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

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[Plamen Ratchev]

Re: Querying towards a responsive UI

Forgot to mention that if you have an ORDER BY clause then having an
appropriate index will help.

Plamen Ratchev

http://www.SQLStudio.com

[shripathikamath@gmail.com]

Re: Querying towards a responsive UI

On Jan 31, 3:09 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

(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.publi c.dotnet.framew ork.adonet.
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/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

[shripathikamath@gmail.com]

Re: Querying towards a responsive UI

On Jan 31, 9:45 pm, "Plamen Ratchev" <Pla...@SQLStud io.comwrote:

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.
>

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 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.
>

Yes, I know of that technique.

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

Thanks.

[Plamen Ratchev]

Re: Querying towards a responsive UI

>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.
>

I pointed this out only because in my experiments with DataReader I have
seen the effect of ORDER BY. As for the comparison with the Query Window I
do not know what method was used to retrieve the data there, so it might not
be possible to achieve the same result with DataReader.

>

>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.
>

Here is another idea. If you are using ADO.NET 2.0 you can explore the new
asynchronous execution. Maybe you can break your big result set to smaller
sets (similar to paging) and return each set asynchronously.

http://msdn2.microsoft.com/en-us/library/ms379553(VS.80).aspx

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[Erland Sommarskog]

Re: Querying towards a responsive UI

(shripathikamat h@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.publi c.dotnet.framew ork.adonet. It probably helps if your
post your actual code, so that any flaws can be uncovered.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[shripathikamath@gmail.com]

Re: Querying towards a responsive UI

On Feb 2, 3:08 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

(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.publi c.dotnet.framew ork.adonet. It probably helps if your
post your actual code, so that any flaws can be uncovered.
>

Thank you Erland, I followed your advice and posted in the appropriate
newsgroup. While the OPTION (FAST 10) did not make a difference, it
was something that I learned, and may come in handy in the future.

Thanks to everyone who responded.