Computer: 3.4 Ghz CPU, 1 GB RAM, 2003 Server

database : MS SqlServer 2000 Enterprise. ~ 10 GB database file. Largest
table in the database contains 11,000,000 records.

Framework: .NET 2.0

I try to run a query against the database, selecting aggregated data from
views based on the large table.
When executed from the Query Analizer, it takes 13 seconds.
When executed from a .NET application, it takes 140 seconds.

The database is well tuned (or else the query analizer would go slowly), so
I can't find the reason for this difference.

Any suggestion ?

TIA
Boaz Ben-Porat

Milestone Systems

Just a guess.
It might be the delay in creating and opening the connection.
Why don't you log the current time just before calling the SP and after it
and find the time difference. That can narrow down on what the issue is.

Thanks for a quick answer.

The time I refer to is after the connection is opened.

the relevant code:
DbDataReader dr = null;

try

{

// This method opens a connection, if not allready opened
Connect();

// dbCommand is an input parameter of type DbCommand. It contains the SQL
statement
dbCommand.Connection = _connection;

DateTime t1 = DateTime.Now;

dr = dbCommand.ExecuteReader();

DateTime t2 = DateTime.Now;

TimeSpan ts = t2 - t1;

int milli = (int)ts.TotalMilliseconds; // milli contains the execution time
of dbCommand.ExecuteReader();


Boaz Ben-Porat


"Omnibuzz" <Omnibuzz> wrote in message
news:687e
[..]

Boaz ,shalom

Do you open connection just before calling the view? Can you post a piece of
code how you did it?
Have you looked at execution plan of the query? Does the optimizer available
to use indexes?



"Boaz Ben-Porat" <bbp> wrote in message
news:4980
[..]

Shalom Uri

The code is attached in BadSql.cpp.

1. Yes, I open a connection just before calling the view, but the time to
open is not included in execution time.

2. I run the same query, with the same parameters from the Query Analyzer.
The execution time was 13-15 seconds
and the execution plan used the right indexes. When the query is called
from a .NET application (.NET 2.0 , C#),
it takes 140-140 seconds. It looks like a problem with .NET
communication with the database.

Thanks
Boaz Ben-Porat
Milestone systems
Denmark

Tel.: (+45) 88 300 325
Mobile: (+45) 25606725






"Uri Dimant" <urid> wrote in message
news:4244
[..]

> The database is well tuned (or else the query analizer would go slowly),
> so I can't find the reason for this difference.

Are you running the *exact* same command text in QA as the application code?
Literals vs. parameters can make a big difference in the execution plan with
some types of queries.

Boaz

Hmm, I don't know, try running DBCC FREEPROCCACHE and UPDATE STATISTICS
on the server to see if this helps

BTW, are you in Denmark right now?



"Boaz Ben-Porat" <bbp> wrote in message
news:3588
[..]

Boaz:

I could not view your code in the CPP file.
However, you can try wrapping the query in a stored procedure and
calling the procedure from C#.
Hope this helps.

Thanks

Uri Dimant wrote:
[..]

Yes, I use parameters in both cases. Here is the Query Analyzer call:


declare @startdate datetime
declare @enddate datetime

set @startdate = '2006-05-16 00:00:00'
set @enddate = '2006-06-16 23:59:59'


SELECT
top 2000 dbo.CardTender.CardNumber AS [Card Number], COUNT(*) AS [Card Usage
Count]
FROM dbo.CardTender
WHERE (dbo.CardTender.TranDate BETWEEN @StartDate AND @EndDate)
GROUP BY dbo.CardTender.CardNumber
ORDER BY COUNT(*) DESC

Runs in a reasonable time (13-15 seconds).

Thanks Boaz

"Dan Guzman" <guzmanda> wrote in message
news:2304
[..]

I didn't see anything obvious in the query/code you posted. What's the
underlying type of DbCommand and what provider are you using? Data caching
could be an issue since it looks like the query will require a scan of a lot
of data. Do you get about the same times if you run the query via the
application twice in a row?

This is an OT ovservation but I noticed you are using BETWEEN for the
datetime range. You might consider reformulating the expression to be
exclusive of the end date so that values between 23:59:59 and midnight will
also be included.

set @startdate = '20060516'
set @enddate = '20060617'

SELECT TOP 2000
dbo.CardTender.CardNumber AS [Card Number],
COUNT(*) AS [Card Usage Count]
FROM dbo.CardTender
WHERE dbo.CardTender.TranDate >= @StartDate
AND dbo.CardTender.TranDate < @EndDate
GROUP BY dbo.CardTender.CardNumber
ORDER BY COUNT(*) DESC

Thanks to all those who answerd. Sorry I botherd you. The error is not
linked to Sql Server but to a COM component that is stucked in the way. A
soloution is found.

/Boaz


"Boaz Ben-Porat" <bbp> wrote in message
news:4980
[..]