SQL Server execution duration difference

  • Thread starter Thread starter alexbf
  • Start date Start date
A

alexbf

Hello,

I have a stored procedure in SQL Server 2000.

When I execute it through my .NET application (OleDbCommand), I can
see (in SQL Profiler) that it takes 423 ms to complete.

If I run the same exact query in Query Analyzer, it takes 216 ms (in
SQL Profiler).

There's no other activity on that server.

Is there an explanation for that time difference?


Thanks,
Alex
 
alexbf said:
Hello,

I have a stored procedure in SQL Server 2000.

When I execute it through my .NET application (OleDbCommand), I can
see (in SQL Profiler) that it takes 423 ms to complete.

If I run the same exact query in Query Analyzer, it takes 216 ms (in
SQL Profiler).

There's no other activity on that server.

Is there an explanation for that time difference?


Thanks,
Alex
Click to expand...

The SQL server caches things like execution plans, query results and
function results, so execution times depends also on what the server has
been doing in the past.

To say anything more specific, I would have to know what the stored
procedure is doing.
 
Hello Goran,

it's quite a simple stored procedure.

- One SELECT INTO that feeds a temp table
- Another select to return the results, fitered with a call to a user
defined function in the WHERE.

I don't think it's the stored proc itself because I execute the exact
same thing through my application or with Query Analyzer.

Thanks,
Alex
 
alexbf said:
Hello,

I have a stored procedure in SQL Server 2000.

When I execute it through my .NET application (OleDbCommand), I can
see (in SQL Profiler) that it takes 423 ms to complete.

If I run the same exact query in Query Analyzer, it takes 216 ms (in
SQL Profiler).

There's no other activity on that server.

Is there an explanation for that time difference?
Click to expand...

In using the Stored Procedure with QA, I believe QA is talking directly to
SQL Server and is a faster means.

When using OleDb and running that SP, OleDb is not directly talking to SQL
Server and is a slower means.

<copied from link>

One of the reasons the SQL .NET Data Provider has gotten so much hype (which
is why all the samples use it), is how it has been optimized. The SQL
Managed Provider talks directly to SQL Server without using OLEDB (the
benefits of using products from the same company). Microsoft claims that the
speed of moving data between SQL Server and your ASP.NET application can
increase as much as 300% or more using the SQL Managed Provider because of
this direct communication.

<end>

http://dotnetjunkies.com/Tutorial/926E56FB-3E8B-4A9B-A872-E5D7C89364A1.dcik

When I was taking my .Net training back in 2004, it was explained that for
speed considerations to avoid OleDb if you were concerned about speed in
accessing SQL Server.

I am sure you can find more articles out on Google about this.
 
alexbf said:
Hello Goran,

it's quite a simple stored procedure.

- One SELECT INTO that feeds a temp table
- Another select to return the results, fitered with a call to a user
defined function in the WHERE.

I don't think it's the stored proc itself because I execute the exact
same thing through my application or with Query Analyzer.

Thanks,
Alex
Click to expand...

So, what is it that makes it take more than a few milliseconds to run?
Is there a lot of data in the table that you have to go through? Does
the final result contain a lot of data? Is it the user defined function
that takes time?

Why is there a need for a temporary table at all? Can't you just filter
the data that are selected from the table? The table can have an index
on the field that you are filtering on, while the temporary table
doesn't. That can make filtering on the table much faster than filtering
on the temporary table.
 
Hello Goran,

yes I could do my stored procedure differently to optimize it.. but I
really want to know why there's such a difference in time between
executing the stored procedure through .NET and through Query Analyzer

The data volume is about 100 rows and a few columns... so I doubt it's
an issue.

Alex
 
alexbf said:
Hello Goran,

yes I could do my stored procedure differently to optimize it.. but I
really want to know why there's such a difference in time between
executing the stored procedure through .NET and through Query Analyzer

The data volume is about 100 rows and a few columns... so I doubt it's
an issue.

Alex
Click to expand...

Have you verified that there is a consistent time difference? Do you
include opening and closing the connection when you check the time in
the code?
 
I am trying to figure out what you two are talking about now that doesn't
make any sense about the speed of something running through SQL Server QA,
which is in direct contact with SQL Server, as opposed to something that's
running from a program that using OleDb

One of the reasons the SQL .NET Data Provider has gotten so much hype
(which
is why all the samples use it), is how it has been optimized. The SQL
Managed Provider talks directly to SQL Server without using OLEDB (the
benefits of using products from the same company). Microsoft claims that
the
speed of moving data between SQL Server and your ASP.NET application can
increase as much as 300% or more using the SQL Managed Provider because of
this direct communication.
Click to expand...

I posted the results of a little test here back in June:

SQL Client (2.0): INSERT 20000 rows: 9,109375 seconds
SQL Client (2.0): 50 SELECT 20000 rows: 1,75 seconds
OLE DB (2000): INSERT 20000 rows: 20 seconds
OLE DB (2000): 50 SELECT 20000 rows: 62,140625 seconds
ODBC (2000): INSERT 20000 rows: 16,65625 seconds
ODBC (2000): 50 SELECT 20000 rows: 47,8125 seconds
OLE DB (2005): INSERT 20000 rows: 17,28125 seconds
OLE DB (2005): 50 SELECT 20000 rows: 61,484375 seconds
ODBC (2005): INSERT 20000 rows: 13,96875 seconds
ODBC (2005): 50 SELECT 20000 rows: 47,734375 seconds

<end>

Anything coming from OLE DB or ODBC is going to be slower because those
solutions are not in direct contact with SQL Server, as opposed to SQL
Client or something running within SQL Server QA with those solutions being
in direct contact with SQL Server.
 
Back
Top