Executing large result queries

[pnp]

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

[Scott Allen]

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

[pnp]

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:ll********************************@4ax.com...

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 problemis that after I run it 3-4 times the results are read very slowly from theDB. The first times the results are displayed in 1-2 secs but the 4,5 timeit takes more than 2 minutes! What am I doing wrong? Are there any
in-between buffers that need to be emptied?

Thanks,
-peter

[Tibor Karaszi]

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:Oi**************@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

[pnp]

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" <ti***************************@hotmail.nomail.co m> wrote in
message news:%2***************@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:Oi**************@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

[Tibor Karaszi]

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:%2****************@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" <ti***************************@hotmail.nomail.co m> wrote in
message news:%2***************@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:Oi**************@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

[pnp]

I'll try to post it there... Thanks anyway.
"Tibor Karaszi" <ti***************************@hotmail.nomail.co m> wrote in
message news:up*************@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:%2****************@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" <ti***************************@hotmail.nomail.co m> wrote in message news:%2***************@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:Oi**************@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
>
>