Executing large result queries

Hi all,
I've developed an app (C#) that connects to SQL Server 2000 without using
thread pooling (using SqlConnection) and I run a query that returns ~4000
tuples each time it runs and displays the results in a listview. The problem
is that after I run it 3-4 times the results are read very slowly from the
DB. The first times the results are displayed in 1-2 secs but the 4,5 time
it takes more than 2 minutes! What am I doing wrong? Are there any
in-between buffers that need to be emptied?
Thanks,
-peter
Hi Peter,
If you could show us some code we might be able to make a better
diagnosis.
This is a windows forms application?
Any chance you are appending the results from the second query to the
listbox without clearning the list box first?
Scott
http://www.OdeToCode.com
On Sun, 8 Aug 2004 13:14:04 +0300, "pnp" <pnp.at.softlab.ece.ntua.gr>
wrote:

>Hi all,
>I've developed an app (C#) that connects to SQL Server 2000 without using
>thread pooling (using SqlConnection) and I run a query that returns ~4000
>tuples each time it runs and displays the results in a listview. The problem
>is that after I run it 3-4 times the results are read very slowly from the
>DB. The first times the results are displayed in 1-2 secs but the 4,5 time
>it takes more than 2 minutes! What am I doing wrong? Are there any
>in-between buffers that need to be emptied?
>Thanks,
>-peter
>
|||The results are displayed in a listview and its' items are always cleared
before putting in the new ones...
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:llbch0dojiom2vgf88iltuhpngpl4ikllh@.4ax.com... [vbcol=seagreen]
> Hi Peter,
> If you could show us some code we might be able to make a better
> diagnosis.
> This is a windows forms application?
> Any chance you are appending the results from the second query to the
> listbox without clearning the list box first?
> --
> Scott
> http://www.OdeToCode.com
> On Sun, 8 Aug 2004 13:14:04 +0300, "pnp" <pnp.at.softlab.ece.ntua.gr>
> wrote:
problem[vbcol=seagreen]
the[vbcol=seagreen]
time
>
|||Start by using Profiler to see if you get the same execution plan between the executions. Depending on whether
you do or not, you can determine whether this is a SQL Server issue or a client app issue.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've developed an app (C#) that connects to SQL Server 2000 without using
> thread pooling (using SqlConnection) and I run a query that returns ~4000
> tuples each time it runs and displays the results in a listview. The problem
> is that after I run it 3-4 times the results are read very slowly from the
> DB. The first times the results are displayed in 1-2 secs but the 4,5 time
> it takes more than 2 minutes! What am I doing wrong? Are there any
> in-between buffers that need to be emptied?
> Thanks,
> -peter
>
|||well i checked it with the profiler and while the SQL:BatchCompleted takes
about the same amount of time for each execution (~2200) the Audit Logout is
different and when the program slows down it takes numbers up to ~584263
while a normal execution takes up to ~20296.
What does this mean?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> Start by using Profiler to see if you get the same execution plan between
the executions. Depending on whether
> you do or not, you can determine whether this is a SQL Server issue or a
client app issue.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
using[vbcol=seagreen]
~4000[vbcol=seagreen]
problem[vbcol=seagreen]
the[vbcol=seagreen]
time
>
|||If you check Books Online, you will find following for the "Audit Logout" event:
Duration The approximate amount of time since the user logged in.
But the SQL:BatchCompleted are the same, so this indicates that the query executes in the same time, the added
time for the logout is the time for the client to process the results before it can logout from the SQL
Server.
I.e., the problem is with the client app (at least as far as I can see). You might want to post this (with
appropriate details) to an ADO.NET group...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message news:%23LQX4gZfEHA.3016@.tk2msftngp13.phx.gbl...
> well i checked it with the profiler and while the SQL:BatchCompleted takes
> about the same amount of time for each execution (~2200) the Audit Logout is
> different and when the program slows down it takes numbers up to ~584263
> while a normal execution takes up to ~20296.
> What does this mean?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> the executions. Depending on whether
> client app issue.
> news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> using
> ~4000
> problem
> the
> time
>
|||I'll try to post it there... Thanks anyway.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:upyMcDjfEHA.708@.TK2MSFTNGP09.phx.gbl...
> If you check Books Online, you will find following for the "Audit Logout"
event:
> Duration The approximate amount of time since the user logged in.
>
> But the SQL:BatchCompleted are the same, so this indicates that the query
executes in the same time, the added
> time for the logout is the time for the client to process the results
before it can logout from the SQL
> Server.
> I.e., the problem is with the client app (at least as far as I can see).
You might want to post this (with
> appropriate details) to an ADO.NET group...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:%23LQX4gZfEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
takes[vbcol=seagreen]
Logout is[vbcol=seagreen]
in[vbcol=seagreen]
between[vbcol=seagreen]
a[vbcol=seagreen]
from[vbcol=seagreen]
4,5
>