Why is this one sproc so slow when using ADO.NET?

[Guest]

We have a rather stored procedure which works like countless others in our
application, but for some reason, this specific sproc is taking an
extraordinary amount of time to return when called.

We have tried using data adapters to fill a dataset, and we've tried using
a data reader, and in both cases, the call to the sproc takes 25-30 seconds
to execute when the sproc itself only takes 6 seconds. (It's a
multiparameter search of a table with millions of rows.)

I have used SQL Profiler and captured the exact sql used to execute the
sproc and even that only takes 6 seconds to execute.

This is the SQL statement that ADO.NET is using:

declare @P1 int
set @P1=1
declare @P2 int
set @P2=1
exec dbo.nt_Module_SelectBySearchParams @HasAlertFlag = 0,
@SerialNumber = N'0100000259ce%',
@PartDescription = NULL, @PartNumber = NULL,
@OrderByColumn = N'SerialNumber', @OrderByDirection = N'DESC',
@Page = 1, @Size = 10, @PageCount = @P1 output, @RecordCount = @P2 output
select @P1, @P2

I have traced this down using both data adapters and data readers and in
each case the line that takes control of the thread out of our hands and off
to the data layer is what takes 6 times as long to run as the sproc itself.

We have many other queries with similar interfaces and parameters that run
instantaneously. So why would one query be subject to such a slowdown?

I need suggestions on where to look for trouble, Please! This is too slow
for our users and I am at a loss to find any sort of solution.

 

[Guest]

Adding some additional info.

All of our SqlParameters are explicitely defined with Name, Type, and Value,
and for Varchars, size as well.

 

[Robbe Morris - MVP C#]

What happens when you call the procedure directly from
Query Analyzer? I doubt very seriously if this has anything
to do with .NET.

 

[Frans Bouma [C# MVP]]

We have a rather stored procedure which works like countless others in our
application, but for some reason, this specific sproc is taking an
extraordinary amount of time to return when called.

We have tried using data adapters to fill a dataset, and we've tried using
a data reader, and in both cases, the call to the sproc takes 25-30 seconds
to execute when the sproc itself only takes 6 seconds. (It's a
multiparameter search of a table with millions of rows.)

I have used SQL Profiler and captured the exact sql used to execute the
sproc and even that only takes 6 seconds to execute.

This is the SQL statement that ADO.NET is using:

declare @P1 int
set @P1=1
declare @P2 int
set @P2=1
exec dbo.nt_Module_SelectBySearchParams @HasAlertFlag = 0,
@SerialNumber = N'0100000259ce%',
@PartDescription = NULL, @PartNumber = NULL,
@OrderByColumn = N'SerialNumber', @OrderByDirection = N'DESC',
@Page = 1, @Size = 10, @PageCount = @P1 output, @RecordCount = @P2 output
select @P1, @P2

I have traced this down using both data adapters and data readers and in
each case the line that takes control of the thread out of our hands and off
to the data layer is what takes 6 times as long to run as the sproc itself.

We have many other queries with similar interfaces and parameters that run
instantaneously. So why would one query be subject to such a slowdown?

I need suggestions on where to look for trouble, Please! This is too slow
for our users and I am at a loss to find any sort of solution.

Reason 1 why queries are 'slow' (in .NET and not in query analyzer) is
that the resultset returned is really big. Big as in: more than a 1000
rows. Query analyzer for example can display the resultset rather
quickly. But if you want to store a lot (thousands and thousands of
rows) of rows in a datatable, it might take a while.

So, as it is a search, execute a search which results in 2 rows or
something and check if that one is as slow as the query which results in
a lot of items.

Also, if the query simply runs 6 seconds, doesn't mean the complete
action takes 6 seconds, as resultset preparing also takes time but this
isn't part of the query execution time. Also check if the temp catalog
has enough diskspace.

Frans.

--

 

[Guest]

6 seconds. Both when I call it directly passing in the parameters directly
with none of the return parameter querying, and when I run the SQL that I
captured using SQL Profiler.

What happens when you call the procedure directly from
Query Analyzer? I doubt very seriously if this has anything
to do with .NET.

 

[Guest]

Reason 1 why queries are 'slow' (in .NET and not in query analyzer) is

that the resultset returned is really big. Big as in: more than a 1000
rows. Query analyzer for example can display the resultset rather
quickly. But if you want to store a lot (thousands and thousands of
rows) of rows in a datatable, it might take a while.

Not the case here. The result is a single record.

Also, if the query simply runs 6 seconds, doesn't mean the complete
action takes 6 seconds, as resultset preparing also takes time but this
isn't part of the query execution time. Also check if the temp catalog
has enough diskspace.

Can you elaborate on how the temp catalog is involved, and how do I check
whether space is sufficient?