Network performance for simple select statement

[Ryan]

I've just inherited a system and have some concerns about the speed of
connections to a remote server (SQL2000). If I do a simple select
statement on the table below, it takes 14 minutes to retrive 6 million
rows across a 2Mb line. Obviously it's a reasonable amount of data to
retrieve, but I would have thought this would be quicker if I'm honest.
Run locally, this is 50 seconds.

My thoughts are that there may be some issues with our connection (we
get general network errors sporadically, which are being looked at),
but wanted some thoughts if the performance is acceptable for what it
is doing with what is available. I don't think there is a SQL issue,
but want to check if this sounds about right.

It's early days, so I'm after a general impression of the speed of
retrieval for the amount of data on the available bandwidth. Assuming a
best performance scenario, what is the minimum time it should take as a
best guess ?

Thanks

Ryan

CREATE TABLE [FIELD_VALUES] (
[DEALER_DATA_ID] [int] NOT NULL ,
[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[FIELD_VALUE] [numeric](15, 5) NULL ,
[CHANGED_TYPE] [int] NULL ,
CONSTRAINT [PK_FIELD_VALUES] PRIMARY KEY CLUSTERED
(
[DEALER_DATA_ID],
[FIELD_CODE]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

[Erland Sommarskog]

Ryan (ry********@hotmail.com) writes:

I've just inherited a system and have some concerns about the speed of
connections to a remote server (SQL2000). If I do a simple select
statement on the table below, it takes 14 minutes to retrive 6 million
rows across a 2Mb line. Obviously it's a reasonable amount of data to
retrieve, but I would have thought this would be quicker if I'm honest.
Run locally, this is 50 seconds.

Hm, your maximum row size is 28 bytes. 28 * 6 million is 168 million.
2 MBit/s, means 250000 Bytes/s. 168 / 0.25 = 672. 672/60 = 11.2 minutes.
So it does not look unreasonable.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp