Query Analyzer

22 Jan 2007 3:42 PM

Simon

I have a SQL database at a hosted centre which I connect to via Query
Analyzer on my desktop. Running queries from my desktop takes much longer
than if I ran the same query on the hosted server.

Where does the processing of the data take place ? Can I force the
transaction to occur on the SQL Server rather than locally ?

Si

22 Jan 2007 3:58 PM

Robert Klemme

On 22.01.2007 16:42, Simon wrote:

> I have a SQL database at a hosted centre which I connect to via Query
> Analyzer on my desktop. Running queries from my desktop takes much longer
> than if I ran the same query on the hosted server.
>
> Where does the processing of the data take place ? Can I force the
> transaction to occur on the SQL Server rather than locally ?

There is no client side transaction. The effect you observer may be
caused by network communication between your QA and the server. Is this
a slow link or has it a lot of latency? Are you getting large result sets?

robert

22 Jan 2007 4:09 PM

Simon

We are connecting via our internet link which I am aware isn`t the fastest.
However the data set is only 200 rows and about 10 columns. A query that
takes 2 seconds on the server can take pushing 10 mins. Which is a massive
performance hit.

Show quote

"Robert Klemme" wrote:

> On 22.01.2007 16:42, Simon wrote:
> > I have a SQL database at a hosted centre which I connect to via Query
> > Analyzer on my desktop. Running queries from my desktop takes much longer
> > than if I ran the same query on the hosted server.
> >
> > Where does the processing of the data take place ? Can I force the
> > transaction to occur on the SQL Server rather than locally ?
>
> There is no client side transaction. The effect you observer may be
> caused by network communication between your QA and the server. Is this
> a slow link or has it a lot of latency? Are you getting large result sets?
>
> robert
>

22 Jan 2007 10:27 PM

Erland Sommarskog

Simon (Si***@discussions.microsoft.com) writes:

> We are connecting via our internet link which I am aware isn`t the
> fastest. However the data set is only 200 rows and about 10 columns. A
> query that takes 2 seconds on the server can take pushing 10 mins. Which
> is a massive performance hit.

Is that a single query? In such case, does it happen to includ a call to
user-defined function?

In any case, try SET NOCOUNT ON. If there are intermediate results, all
those (1 rows affected) can slow down things quite a bit.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx