sql server odbc: Well that isn't much to go on but here are a couple of possibilities. One is
that the speed in which the query returns the rows can be governed quite a
bit by the client. In this case the client is the server but things like
available memory and how the client is dealing with the rows fetched can
make quite a bit of difference. I have seen queries run from QA on several
different clients with different hardware and seen dramatic differences.
Usually the more rows returned the more the difference. In this case 50 is
not a lot unless you have large text or image columns. But more than likely
you are simply getting different query plans based on how it is being
called. Clear out the procedure cache with DBCC FREEPROCCACHE and run the
queries from the two clients. Look in syscacheobjects to see if the same
plan or a different plan is being used. You can tell by looking at the
UseCounts column and if it increments by one each time you call it you know
that plan is being used. The plan is heavily dependant on what the values
are that are used the very first time it gets called from each client. If
you used different values, the query was formatted differently or you have
certain SET settings that vary you can get two different plans. This article
is for 2005 but a lot of it still holds true for 7.0. I suggest taking a
look.

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Oh and by the way there is usually no need to post to that many groups to
find an answer.

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Hi there!

I have a Sql Server 7 running on a webserver. Now I have a quite complicated
query that I run on 4 ways.

1: Using ODBC-driver directly on the Sql Server. time to execute: 12 seconds
(done this about 10 times)
2. Using OLEDB-driver directly on the Sql Server. time to execute: 12
secondes

3. Using ODBC-driver from my laptop which connects to the sql-server over
the internet: 1 second
4. Using OLEDB-driver from my laptop which connects to the sql-server over
the internet: 1 second.

In each case the query is returning the same 50 records.

As I assumed doing the query connected to the sql-server over the internet
must be slower than running the query on the same server the Sql Server is
running on. But, the opposit seems to be true.

I'm very sure the time-difference has nothing to do with workloads on the
server or something. In all 4 cases the server is running fully idle.

Does someone has any clue why Sql Server performs the query about 12 times
faster is the request comes from a remote machine?

Regards,

Ronald