Only getting time outs when running a query from .net app

Only getting time outs when running a query from .net app

Post by U2NvdHQgUy » Sat, 30 Aug 2008 02:46:12


I've got a weird problem where when I run a certain query from within any
.net application it will eat a LOT more CPU cycles and eventually time out.
When I run the same query from within SQL Server Management Studio the query
completes in under a second.

Using profiler I see that it uses 28,734 cpu cycles before timing out and
when run from SSMS it uses 125 cycles before completing successfully. This
behavior happens when I run the query from within a stored procedure or run
it on its own. I also tried using various accounts and forms of
authentication and none of that had any effect on it. I tried connecting
with .net 1.1 and 2.0 but got the same results. The fact that profiler shows
different CPU usage depending on whether or not I'm using .net causes me to
think this is a sql server issue.

Does anyone have any ideas what is causing this or what I could check out?
I am using SQL Server 2005 and have not been able to repro on SQL 2000 yet.
 
 
 

Only getting time outs when running a query from .net app

Post by Jason Folk » Sat, 30 Aug 2008 03:11:57

I'm not sure whether or not this contributes to your problem in particular,
but you may have a blocking problem.

Judging by the fact that your CPU spikes really high, that might not be the
case... I'm just throwing that idea out there as a possibility.

 
 
 

Only getting time outs when running a query from .net app

Post by Plamen Rat » Sat, 30 Aug 2008 03:13:29

You probably have different execution plans for the query because of the
client connection settings. Try adding OPTION(RECOMPILE) to the query to
see if that improves performance.

--
Plamen Ratchev
http://www.yqcomputer.com/
 
 
 

Only getting time outs when running a query from .net app

Post by U2NvdHQgUy » Sat, 30 Aug 2008 03:20:02

This is an asp .net application so I thought that was what is happening at
first. I then wrote a client app in .net that only runs that query and had
the same problem. There is nothing else going on in the database so I don't
think there is a blocking issue.
 
 
 

Only getting time outs when running a query from .net app

Post by U2NvdHQgUy » Sat, 30 Aug 2008 03:35:07

Forcing a recompile doesn't change anything.
 
 
 

Only getting time outs when running a query from .net app

Post by Plamen Rat » Sat, 30 Aug 2008 05:31:22

Can you post the query? How do you execute the query in .NET?

--
Plamen Ratchev
http://www.yqcomputer.com/
 
 
 

Only getting time outs when running a query from .net app

Post by Jason Folk » Sat, 30 Aug 2008 05:37:49

whats the text of the sproc?
 
 
 

Only getting time outs when running a query from .net app

Post by U2NvdHQgUy » Sat, 30 Aug 2008 06:02:10

In .net I've tried using the SqlHelper enterprise library stuff to run
ExecuteNonQuery, I've also tried a reader, and have tried the following code:

using(SqlCommand cmd=new SqlCommand(command,connection))
{
using(SqlDataAdapter adapter=new SqlDataAdapter(cmd))
{
dt=new DataTable();
adapter.Fill(dt);
}
}

Here is the query:

update customer
set balance = s.amount
from (
select c.id, coalesce(x.amount, 0) as amount
from
(Select sum(amount)as 'amount',ID from accountTransaction a
inner join transactionLog tl
on a.transactionID = tl.transactionID
where TypeID%10<>5 group by ID) x
right join customer c
on c.id = x.id
) s
where s.ID = customer.ID


and here are the scripts to create the tables:
create table customer
(
[id] bigint,
balance money)

create table transactionLog
(transactionID uniqueIdentifier,
typeID int,
[id] bigint)

create table accountTransaction
(
transactionID uniqueIdentifier,
amount money
)



We got down to the level of inspecting the packets and the packets that are
sent by SSMS are exactly the same as those sent by .net once the connection
is set up.
 
 
 

Only getting time outs when running a query from .net app

Post by Plamen Rat » Sat, 30 Aug 2008 07:15:50

Using ExecuteNonQuery will be good, I use it all the time without
problems. You can try rewriting the query to see if that helps. Here are
two ways that should be equivalent to your query (of course, test first
before trying an update on real data):

WITH CustomerTransactions
AS
(SELECT C.id, C.balance, COALESCE(X.amount, 0) AS amount
FROM Customer AS C
LEFT JOIN (SELECT id, SUM(amount) AS amount
FROM AccountTransaction AS A
JOIN TransactionLog AS L
ON A.transactionid = L.transactionid
WHERE L.typeid % 10 <> 5
GROUP BY id) AS X
ON C.id = X.id)
UPDATE CustomerTransactions
SET balance = amount;

UPDATE Customer
SET balance = COALESCE((SELECT SUM(amount)
FROM AccountTransaction AS A
JOIN TransactionLog AS L
ON A.transactionid = L.transactionid
WHERE L.typeid % 10 <> 5
AND L.id = Customer.id), 0);

--
Plamen Ratchev
http://www.yqcomputer.com/